Quite often when we receive data, it can be in various formats and styles. Though our data maybe correct and valid it can look ugly when it isn’t in a easy to read format. We have gathered together some spreadsheet tricks to improve your data.
Some of the worst culprits are
- User form submissions
- Collecting data from various websites EG: finding local businesses to sell to
To improve our data we have gathered here together to celebrate the union of you our reader and these spreadsheet formulas in blissful existence. I now pronounce you a spreadsheet guru.
Google Sheets Baked In Cleanup
Google has baked in a Data Cleanup function. To access just press Data > Data cleanup > Cleanup suggestions.
=Proper
If we have data in cells that’s all capitals, no capitals or a mismatch of both then proper can help.
Proper will adjust our cell data so every word’s 1st character is a capital.
=Upper
Need to shout in every cell? Maybe you just want every letter to be a capital? Well Upper has got your back.
EG: Mushy Pea gravy = MUSHY PEA GRAVY
=Lower
Remember =upper? =Lower is its evil twin. Ok not evil but definitely opposite. Lower will make every letter in a cell lowercase
EG: Pizza on TOAST = pizza on toast
=Trim
Trim will remove unnecessary spaces from a cell
EG Chef Sue ate extra cake = Chef Sue ate extra cake
Google Sheets: use the Trim Whitespace Function built into Google Sheets without having to write a formula.
Select the columns or rows you wish to change. Then Click Data > Trim whitespace.
Clear Formatting
Formatting is made up of text size, font style, bold, italic, underlined, text colour, cell background colour, cell borders.
We can remove formatting in both Excel and Sheets by selecting the cells, columns / rows we wish to ‘reset to default’ and pressing
Microsoft Excel Clear Formats
Editing > Clear > Clear Formats
Google Sheets Clear Formatting
Format > Clear Formatting
The Finial Spreadsheet Tricks To Improve Your Data Is Spell Checking
It sounds like a no brainer but you might be surprised how many times we have been guilty of not Spell Checking a spreadsheet.
Struggling with the above recipe? Hire a chef to do it for you