3 Hacks to Keep Data Organized in Google Sheets

For those who live and die by Excel spreadsheets (there are LOTS of you out there), take note: Google Sheets is rapidly gaining ground on Microsoft Excel as an enterprise-grade statistical and financial analysis tool. Yup, Google Sheets even has pivot tables!

Below are three helpful hacks for Google Sheets users trying to keep their data as organized as possible.

1. Easy Detection with Google Sheets Conditional Formatting

In the old days of two-column ledgers, credits were black, debits were red and the final  tally of profits and loss was easy to discern. Spreadsheets can make it comparatively simple  to enter, maintain, and share data, but some days you really wish that all the debits were red, and all the profits were black.

Google Sheets Conditional Formatting is here to help. Just select a range of cells and configure the conditions – the absence or presence of certain text, or a figure that falls within or without a specific numerical range – that trigger a change in the text color and fill color of a cell. Need all positive fiscal figures in green, or all items assigned to Janice highlighted in yellow? No problem. Conditional formatting makes large spreadsheets easier to parse with a simple glance. Remember: it’s not OCD; it’s professionalism.

2. Build Lists Quickly and Easily with Google Sheets’ Autofill

More often than not, spreadsheets require repeating patterns of lists: days of the week, months of the year, or just ascending numbers. Filling out long columns or rows of these patterns can become tedious, even with copy-and-paste functions. Luckily, Google Sheets Autofill can do the work for you. Start a pattern in a range of cells, highlight those cells, then grab the little blue corner box of the highlighted range and pull. The pattern will repeat to whatever range of cells you extend the highlighting over. No muss, no fuss!

3. Prevent Collaborators from Corrupting your Spreadsheet with Google Sheets Data Validation

When you ask colleagues to work with you on a spreadsheet, you run the risk of someone putting dollar figures in a date field, ZIP codes in phone number slots, or negative numbers in ranges where it’s impossible to have values less than zero (“there’s no such things as negative rainfall”).

Here’s a tip! You can simply use Google Sheets Data Validation to warn or prevent users from entering inappropriate data in particular fields. You can constrain numerical figures to certain types (dates, dollar figures, whole numbers) or even force collaborators to choose options from a list (we use two-letter state abbreviations, not three-letter, you savages!).