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 that you’ll have to take time to make the corrections manually. If it’s a long list, this can take hours.
Here’s the good news. We can usually spare ourselves that headache if we can identify the right tools to solve our problems. This is especially true when we’re just formatting lists – most situations I’ve encountered have had a formula or function that helped save the day.
The best part? You don’t have to be an excel expert to use these tips. You can literally resolve your issue in the same amount of time that it takes to watch a GIF. These tools also work on Google Sheets, if that’s more your style!
Here are 5 common excel list nightmares that can upend any nonprofit employee’s day…and the easy hacks to fix them.
5 Excel List Nightmares Any Nonprofit Employee Can Fix
*Note: The GIFs below were created using a Mac, but the instructions are generally the same across Macs and PC’s.
Scenario 1: You’re merging names into an email for volunteers, but your cells have extra spaces in them. A h h h.
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.
Scenario 2: You need to print mailing labels to send thank-you letters, but the addresses ARen’T iN tHe cORrect CaSE. UGh.
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.
Scenario 3: Your director needs an updated list of funders, but the list is full of duplicatesduplicates.
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.
Scenario 4: You have text in a single cell that now needs to be separated out into multiple columns. If only someone had told you that from the beginning.
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 an actual example from a previous project of mine. 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.
Scenario 5: You need to replace a ton of text in the document, because something wasn’t entered correctly and now it’s EVERYWHERE.
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:
- 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 circumvent this, 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.)
- This is only a snippet of what spreadsheets can do for your organization. The best learning I ever did for my career was to take an Excel class. If you’re serious about identifying more ways that Excel can save you time and effort, I really recommend signing up for a session.
What are some other excel list issues that you’ve encountered, and how did you fix them?