Updated 2020 – If you’re reading this blog, there’s a good chance that you work with spreadsheets from time to time.

Maybe you’re like me: eager to dive into that world of cells and formulas. Or maybe you dread each time you see an email with a .csv or .xlsx file.

Because once you open that file, you know all bets are off. Who knows what kind of mistakes are waiting for you.

But the good news is that you can quickly clean an Excel file, without being an Excel expert. In fact, I’m betting you can resolve those issues in the time it takes you to watch a GIF. (Spoiler alert: I have GIFs.)

Here are 5 common Excel list nightmares that can upend any nonprofit employee’s day…and the easy ways to fix them.

---

Scenario 1

The Problem: You’re merging names into an email for volunteers, but your cells have extra spaces in them. A h h h.

The Solution: TRIM Formula

One day, we will get to a place where web forms are smart enough to remove the spaces for us (right? Can someone put this on the docket already??) Until then, there’s TRIM.

TRIM removes extra spaces in cells: before the first character, after the last, or in between words. So if someone typed too quickly and added unnecessary spaces, this will fix it.

In a blank cell, enter “=TRIM(location of the cell you’re trying to clean up)”. Then, copy & paste down the column.

Scenario 2

The Problem: You need to print mailing labels to send thank-you letters, but the addresses ARen’T iN tHe cORrect CaSE. UGh.

The Solution: PROPER Formula

PROPER adjusts the punctuation in your cell by treating each word as a proper noun. This trick is great if you need to format a bunch of mis-entered names, titles, or companies.

In a blank cell, enter “=PROPER(location of the cell you’re trying to clean up)”. Then, you can copy & paste down the column.

p.s. UPPER will change all text in the cell to uppercase. LOWER will change all text to lowercase.

Scenario 3

The Problem: Your director needs an updated list of funders, but the list is full of duplicatesduplicates.

The Solution: Remove Duplicates

Duplicates are the bane of data existence. That’s why we’ve got Remove Duplicates: a function that deletes any rows where there is a duplicate value, based on the column that you define.

In this example, we’ll delete all rows where email address is repeated.

Highlight the table you want to clean, and find the “Remove Duplicates” icon in the Data Tools tab. Then, select the column that you want Excel to check for dupes and click ‘Remove’. Step-by-step video here for my visual learners.

Scenario 4

The Problem: You have text in a single cell that now needs to be separated out into multiple columns..

The Solution: Text to Columns

Text to columns is THE best Excel thing no one talks about. This function takes the text in a cell and separates it out into multiple columns, based on whatever rules you decide.

The hardest thing about this hack? Figuring out all the possible ways to use it.

Here’s an example from a past project. Say you have a list of contacts with emails, but need a column for their company affiliations. You’ve determined the best way to do this is to separate out the part of their email that indicates their org (everything after the @ symbol).

Highlight the column of cells that you want to split. Then, locate the Text to Columns icon in the Data section and walk through the prompts.

1. First, choose between a Delimited or Fixed situation. Delimited separates cells wherever a certain character appears in a string of text. (9 times out of 10, this is what you’ll want.) Fixed separates cells after a set number of characters. 
2. After clicking Delimited, enter the character that you want to use as your separator. For this case, we’ll enter the “@” symbol. Check the “Other” box, enter the symbol and click Next.
3. See the preview, click on Finish, and watch the magic happen.

Another great use case? Separating full names into first & last name columns, using the spacebar key as your delimiter.

Scenario 5

The Problem: You need to replace a ton of text in the document, because something wasn’t entered correctly and now it’s EVERYWHERE.

The Solution: Find & Replace

Find & Replace isn’t specific to Excel, but it’s another good one. Excel will search your sheet for every instance where a specific string appears, and replace it with whatever you want.

If that sounds too easy, that’s because it is.

Continuing off the last example, we now have everyone’s email suffix. But we still need to make sure the affiliations are properly listed. If we know that every “usc.edu” suffix stands for “University of California”, then we can do a ‘find & replace’ to make the correction.

On a Mac, hit the command+f buttons (ctrl+f for PC) to open the ‘find’ dialogue box. Enter the term you want Excel to find, the term you want Excel to replace its findings with, and hit “Replace All.” Voila.

Be sure to add these tips to your list-editing arsenal! And remember…

• Make copies of your sheets/tabs/columns before using functions or formulas. In case something goes wrong, you always want to have the original version available.
• • Remember that formulas are references in your sheet. This means that if you change any columns/rows/cells that are referenced in a formula, the output for that formula will change too.

Tip: To prevent mishaps, copy and paste your formula calculations “as values”. This overwrites the formula and keeps the outputs instead.

To conclude…

Spreadsheets can be fun, I promise. You’ve just got to know what’s available to you, and how best to work with them.