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

Are you getting the most out of Google Spreadsheets? Click hear to read about 2 features that will take your team from crazy, to crazy organized.

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

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?

Data validation

Data validation

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.

Data validation

Color Coding (Conditional Formatting)

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.

Conditional Formatting

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!

Select and drag

Author Image

Cameron Chapman

Cameron is a staff writer here at Skillcrush, and spends most of her time writing and editing blog posts and Ultimate Guides. She's been a freelance writer, editor, and author for going on a decade, writing for some of the world's leading web design and tech blogs. When she's not writing about design, she spends her time writing screenplays and making films (and music videos for rock and metal bands!) in Vermont's Northeast Kingdom.