Last week, I stayed at work late while I was finishing an update of 17,000 records in our Salesforce database (we wanted to change all of our matching grants from “Paid” to “Approved”), and I used that time to clean off my desk that had become cluttered with mugs and old papers. At home, I faced another deep clean project; namely, the fridge. I’ll spare you the details, but suffice it to say, it was a smelly and unpleasant ordeal.
As I began researching for this post, I did what any good organizer would do –> find out who has tackled this question before! And to my great joy, I found a phenomenal post, complete with screenshots, that covers some very useful and helpful data cleaning tips. Check it out here! The post covers…
- using the “Find” and “Replace” feature (especially useful if you need to change CA to California!)
- “Snipping” (NOT sniping!) functions (in case you need to snip just the area code or just the zip code from a cell that has other mumbo jumbo in it)
- text combo functions (in case you need to take First Name and Last Name columns and turn them into a Full Name column) (sometimes this is called Concatenate)
- Upper/Lower (and I would add Proper!) functions to turn UPPER CASE into lower case and vis versa!
- Duplicates! or you could refer to TDAA’s own posts on de-duping … (1) Data cleaning is a “contact” sport, (2) Dupes / dupes / dupes (Excel), (3) google google sheets sheets
That’s a pretty good list, right?! I think it’s even better than the official Excel guide to data cleaning (which you might also want to check out).
Here are a couple functions that I would add (useful to have in your back pocket! and after the revolution, more womens’ / femme clothes will HAVE POCKETS, thank you very much!)
Outer Space
Sometimes you’ll find data with extra “spaces” whether that is at the end of a name (for example, “Samantha ” or weirdly spaced-out text that was downloaded from a database. You can use the handy-dandy trim function to remove those spaces for you. Never delete another invisible space again!!
Snipped Zips
When it comes to zip codes (sad trombone…) you will almost certainly need to fix zip codes when you are cleaning data, especially if you have NJ people on your list or other states with pesky “leading zeros” like 08505. Here’s a good tutorial for that.
One hit number
It’s really useful to be able to format numbers exactly the way you want them! (Phone number? Dollar amount? Zip code? You name it!). Check out this post for standard AND custom number formatting tips (probably more than any of us ever need to know!)
If you’re missing, raise your hand
We covered this in much ado about nothing, but often cleaning data isn’t only fixing what’s there, but it’s ALSO adding what’s missing! You can use Conditional Formatting to “reveal” (or at least, highlight) cells that are blank. Then, get to work adding data (or delegating!) so that you can go on your merry way!
Rubber, meet road
I recently put a lot of these tools to use when I was helping a community group deliver a petition to the School District of Philadelphia. They had gathered around 500 signatures, but the data needed to be standardized and cleaned up so that it could be easier to read, easier to sort, easier to map, and ultimately more hard-hitting. I updated zips and states, standardized capitalization, changed the spreadsheet style by “formatting as table.” Then, I took things one step further and mapped all of the signatories in a free Google Map (we’ll save that for another post). A little bit of cleaning goes a long way – and with these spreadsheet tips, you’ll only need a little bit of time to do a LOT of cleanup.