7 Ways To Clean up Data in Excel and Google Sheets
This article was written by Shirin Bakhtiari, an Employee Success specialist and Senior Accountant at Unito.
In Unito’s BizOps department, we might have different tastes and opinions, but we are all united in the love of Excel and Google Sheets! So many of our processes go through a spreadsheet eventually, so it’s a good thing we love them. However, we all know how spreadsheets can suck up precious time, even for the smallest tasks. For example, racking our brains trying to figure out why a simple sum doesn’t work. That’s why if you’re going to use spreadsheets, you need to know some simple tricks to clean up data in Excel and Sheets.
Here are seven simple tips for avoiding those frustrating moments and keeping your data clean. These tips are relevant for more than 90% of situations where you need a spreadsheet. Let’s get started!
Clean up data in Excel and Sheets by not hiding lines
What if we stopped using the “hide” function when we don’t need a line or a column anymore? This “hide” option is tricky because we forget there was data in those hidden sections: later, we come back to our spreadsheets and potentially mess up sums and other formulas by including hidden data by mistake. So here’s a simple rule: delete data you don’t need. It keeps things cleaner and helps you avoid potentially frustrating mistakes.
Alternatively, move the data you would hide below your table. If you need to keep it keep where it is, change the format. I usually color these cells light gray and use strikethrough formatting.
Inputs and formulas are like oil and water (they don’t mix)
While building a spreadsheet, one can be tempted to write hard-coded numbers within formulas in order to gain a few seconds or to avoid using too many cells. This is a bad habit because:
- This can create typos and other mistakes.
- Data becomes hard to update.
- You have to click through each cell to verify the numbers.
As a rule of thumb, the only time you should use a number inside is with this formula: “divided/multiplied by 12 months”. For all other numbers, write them in distinctive cells and use formulas to refer to them. Everything will be cleaner!
A final professional tip to differentiate “input” and “formula” cells? Use a different color code! Usually, cells containing formulas should have the normal white and black format and those with inputs should be in blue. Keep that in mind when you need to clean up data in Excel and Sheets.
Clean up data in Excel by highlighting cells with exceptions
When we exceptionally modify a formula within a given cell, we should modify the color of that cell. Here, I’ll take the example of price table:
Column Total Price = Column Quantity x Column Unit price.
Let’s say there is a problem with one of the orders; the product was damaged so you offered an exceptional discount. If we modify the formula of this specific line without changing the color, chances are that in a few months, your colleagues (or you) will come in the spreadsheet, miss the exception, and update formulas in the whole table. This action will override the exceptional calculation. Result: numbers won’t add up and no one will understand why.
So if you don’t want to crawl through all that data manually, don’t forget to highlight exceptions!
Let the sum line breathe
For this one, let’s take the example of a salary table with a total sum formula at the bottom. Whenever you add a new employee, you have to add a line. In this situation, we can reduce errors with the habit of:
- Permanently leaving an empty line above the sum formula, and
- Including that empty line within our formula, and
- Changing the color of that line so that we remember it is always meant to remain empty.
Why? Because you’ll be able to add new data above this line and be sure the formula will still work. Otherwise, sometimes your spreadsheet will automatically adjust the formula — but not always. That’s when mistakes happen.
Boycott the merging option
I can’t remember the last time I used the “merge” option, and didn’t regret it afterwards. Now I only use this option for presentation purposes if I need to show a beautifully-displayed document to an external partner.
Merges can be very annoying because they:
- Prevent you from moving lines/columns
- Stop you from copying formats
Besides, there are easy alternatives to merging columns or rows. For example, if you want to highlight the fact that some columns have to be considered together, just use common borders!
Clean up data in Excel by embracing the power of F2
We all know about the power of F4 — if you don’t, I seriously advise you to stop everything and Google it! But if you only use spreadsheets punctually, you might not know about the power of F2. So here we go:
- To edit a cell without having to double-click it, use F2
- To add a note without right-clicking, use Shift+F2
If you are a Mac user and use spreadsheets a lot, I advise you to change the parameters of your computer so that your keyboard is automatically set to the function buttons. You’ll be able to access F2 and F4 very easily. Unfortunately, this will mean that you will need to hit the “Fn + key” to activate the lighting and sound control.
Clean up data in Excel by standardizing dates
Let’s end this list with a personal rant. Mankind has to overcome many challenges that can only be solved with hand-in-hand collaboration. Unfortunately, when I see our inability to agree on a common date format, I get pessimistic.
Here is an example of the problem:
How do you read this date: 07/01/06? Is it:
- July 1st, 2006 ? (US style)
- January 7th, 2006 ? (French style)
- January 6th, 2007 ? (some Canadian styles)
And here again, another interesting example: I recently found this expiry date on a product as being 22-MA-23. What does it mean? Is it 2022 or 2023? Is it March or May?
Well, no one knows — except the people who printed it on the product.
So why don’t we all use this magical date format: DD-MMM-YYYY? In other words, always communicate dates as “01-jul-2006”. Then, no confusion is possible. However, for month names, we must use the 3-letter abbreviation in English. For example, for June and July, it gives “JUN” and “JUL”, but if we had to use french abbreviations, that would be “JUI” and “JUI”, so… you know… we can’t really guess!
Need more tips?
We have a ton of content that'll help you get the most out of Excel, Sheets, and any other spreadsheet tool.