No matter which department you work for at your organization, chances are that Excel spreadsheets have touched your life in one way or another.
If you’re like me, then you’re eager to dive into that world of cells and formulas. Or, maybe you dread it each time you see an email request with a .csv or .xlsx attachment. That dread gets reinforced once you open the file, see the glaring errors, and realize you’ll have to make corrections manually. If it’s a long list, this can take hours.
Here’s the good news. There are faster ways to do this, and you don’t even need to be an Excel expert. We can resolve these issues in the same amount of time it takes to watch a GIF. (And if your org uses Google Sheets, these tips work there as well!)
Here are 5 common excel list nightmares that can upend any nonprofit employee’s day…and the easy hacks to fix them.
1) 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
☑ Also works on Google Sheets
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 that appear in a cell – either before the first character, after the last, or in between words. So if someone typed their information too quickly and added unnecessary spaces, this formula will correct it.
In a blank cell, enter “=TRIM(location of the cell you’re trying to clean up)”. Then, you can copy & paste down the column.
2) 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
☑ Also works on Google Sheets
PROPER adjusts the punctuation in your cell to treat each word as a proper noun. A cell with the text “sally seashells” would then become “Sally Seashells” instead. This trick is great if you need to format a bunch of mis-entered names, titles, or companies as well.
In a blank cell, enter “=PROPER(location of the cell you’re trying to clean up)”. Then, you can copy & paste down the column.
Tip: The UPPER formula, which works in the same way, will change all text in the cell to uppercase. LOWER will change all text to lowercase.
3) Your director needs an updated list of funders, but the list is full of duplicatesduplicates.
The Solution? Remove Duplicates
☑ Can be done on Google Sheets, but requires an add-on. I use Mail Merge with Attachments.
Duplicates are the bane of any data person’s existence, but they can be fixed. First, make sure that all of your information is correctly entered into the spreadsheet – no additional spaces, columns are in their proper format, etc…
The Remove Duplicates function will look at your table and delete any rows where there is a duplicate value present, based on the column/field that you define. In this case, we’ll delete all rows where the email address is repeated.
Highlight the table that you want to clean up, and find the “Remove Duplicates” icon in the Data Tools tab. Then, select the column that you want Excel to check for duplicates against, and click ‘Remove’. Note in the example below, we chose to delete based on email. Had we chosen the organization column instead, Excel would’ve deleted 4 rows instead of 3.
4) You have text in a single cell that now needs to be separated out into multiple columns.
The Solution? Text to Columns
☑ Also works on Google Sheets (called “Split text to columns”)
Text to columns is THE saving grace for staff who run into this problem. This function takes the text in a cell and separates it out into multiple columns, based on whatever rules you decide. It’s so simple to do that the only hard thing about this hack is identifying all of the opportunities when you can use it.
Here’s a real example from a past project. Say you have a list of contacts with emails, but you need to create a column for their affiliations. You’ve determined that the best way to do this is to separate out the part of their email that indicates their organization (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.
- In Excel, the first prompt will ask you to 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.
- 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.
- See the preview, click on Finish, and watch the magic happen.
Another great, common use case for this? Separating full names into first & last name columns, using the spacebar key as your delimiter.
5) 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
☑ Also works on Google Sheets
Find & Replace isn’t specific to Excel, but it’s saved myself and my colleagues tons of time. It’s exactly the way it sounds – Excel will search your sheet for every instance where a specific string of text appears, and replace it with whatever you want. If that sounds too easy, well, that’s because it is.
Continuing off of the last example, we now have everyone’s email suffix. But we still need to make tweaks so that 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 don’t forget to…
• 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 any mishaps, copy the column that has the formula, right-click and do a “paste as values” into those cells. This overwrites the formula, keeping the outputs instead. (To see what I mean, check out how the fx box at the top changes after I’ve pasted.)
To wrap things up…
These tips are only a fraction of the power that spreadsheets hold. The best learning I ever did for my career was to take an Excel class, and I can’t encourage it enough if you’re serious about finding ways to save your time and frustration on these list mishaps!
If you have any additional tips, please share in the comments below!