2 Life-Saving Google Spreadsheets Tips to Help You (and Your Team) Stay Organized

By Randle Browning

In the last several years, Google Drive has made working with teams on a set of documents or spreadsheets so much easier. Still, when it comes to more complex projects, things can start to swerve out of control.

Who added that column? You’re supposed to type a numeral, not the word “four.” Wait, Bob! 11 wasn’t an option! This is a 1-10 operation we’re talking about.

Whether you’re using Google Spreadsheets to track sales, manage an editorial schedule, or work with form data, sometimes it would be easier if there were some ground rules in the data set, if you know what I mean.

That’s where data validation and color coding come in – two less obvious and underappreciated features of Google Spreadsheets. In my virtual office, the combination of these handy selectors and color codes has transformed our data management system from a circus to a menagerie.

Data validation sets rules about what values a cell will accept. For example, in a “Status” column, you might allow for four values that appear in a dropdown: Not started, In progress, Submitted for review, and Done. Or you might require the user to input a valid date that is in the future. This means no rogue answers. The feature also forces users to know exactly what they’re inputting — if it’s not one of the options, why are you trying to enter it there?

There are two ways to access data validation: right-click a selected cell (or column or row) or click the “Tools” drop-down in the toolbar. Once there, you can select a cell range, a type of criteria (like List of Items, Number, or Text, for example), and protocol for invalid inputs.

Color coding changes the color of the text and/or background of a cell, row, or column based on its value. In combination with data validation, color coding almost feel magic. Imagine that all dates in the past turn red, and all dates in the future are green. Or maybe clients with over a certain dollar amount in purchases turn gold. With a little work up front, color codes can make it easier to understand your data at a glance.

To use color coding, right-click the selection and hit “Conditional Formatting” or choose Format > Conditional Formatting in the toolbar. Once there, set criteria and associated colors.

Protip: To use Data Validation and Color Coding in conjunction, set data validation first, then set color coding. Select the cell, then drag and drop down the column or across the row, just like you would with functions. Happy organizing!