If you use spreadsheets, you’ve almost certainly spent time at some point manually tidying up messy data. It could be duplicate information, weird extra spaces, or a mix of differently formatted company names that should all be the same.
Sheets has a couple of built-in features to help with this, and Gemini can step in where those features fall short.
How to clean up data in Google Sheets
Let’s take a look at three different tasks - removing duplicates, trimming whitespace and standardising inconsistent data. Want to try this out with dummy data? Watch the video and use the template sheet.
Trim whitespace
If your data has weird imperfections like extra spaces before or after names, this is what you need. For example, the second row in the screenshot says ‘ Sarah ‘ - which will be fixed to ‘Sarah’
Click on the top left of the rows and columns to select them all
Click ‘Data’
Choose ‘Data cleanup’
Click ‘Trim whitespace’
Remove duplicates
Does what it says on the tin. If you have duplicate rows, this will remove them. For example, rows 10 and 11 have the same information, so one will be deleted.
Repeat steps 1-3 above
Click ‘Remove duplicates’
Check the ‘Data has header row’ box (unless it doesn’t)
Click ‘Remove duplicates’
Standardise data
In this example, we have a column for phone numbers, but they’re all in random formats. This is the kind of thing Gemini can help with.
Click the Gemini icon on the top right to open the side panel
Submit a relevant prompt
For example: “Create a new column with the phone numbers consistently formatted in this style: (555) 000-0000”
Watch me walk through this example
Upgrade to a paid subscription to watch the video, access the template I used and get other perks like monthly live calls where I share updates and ideas, and answer your questions.
Gemini in Google Sheets can do lots more! Here’s a previous tip where I explain how to ask for formulas instead of writing them.
I’ll be back in your inbox next week.
Dean
