× Terms of Use Privacy Policy

Take me back!! I want my CSS!! Click me to get back :)

×

READY FOR A NEW CAREER?

But not sure where to start? Find out if a tech career is right for you.

TAKE THE 3-MINUTE QUIZ

2 Life-Saving Google Spreadsheets Tips to Get You (and Your Team) 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.

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

Your email address will not be published.

2 comments

  1. Jeppe Replied

    A little more explanation on how to actually do it, instead of just showing that it’s there – and how to access it. I still have no clue how to create what you created, after reading it.

  2. Pingback: 2 Life-Saving Google Spreadsheets Tips to Help You (and Your Team) Stay Organized | HER AGENDA

Want more articles like this?

Sign up to get the most recent tech news, tips and career advice.