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.

1. 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

2. Color Coding

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.

Color Coding

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

Share on Tumbl


About Skillcrush

Skillcrush is an interactive online learning platform teaching the most in-demand job skills to get you hired. Our unique (and fun!) approach includes one-on-one video chats with instructors, a class of fellow learners, and portfolio-worthy projects (not just "exercises") to teach digital skills like user experience design and JavaScript. Start with our free 10-day bootcamp today! Visit www.skillcrush.com for more info.
This entry was posted in The Main Agenda and tagged , , . Bookmark the permalink.

Comments are closed.

#HerAgendaLive: Watch Video Recaps From Our Live Event Series