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



Aug. 27 2014, Published 1:15 a.m. ET

Share to XShare to FacebookShare via EmailShare to LinkedIn

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?

Article continues below advertisement
Data validation
Data validation
Article continues below advertisement

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
Article continues below advertisement

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
Article continues below advertisement

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
Article continues below advertisement

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

Ambition Delivered.

Our weekly email newsletter is packed with stories that inspire, empower, and inform, all written by women for women. Sign up today and start your week off right with the insights and inspiration you need to succeed.


Latest The Main Agenda News and Updates

    Link to InstagramLink to FacebookLink to XLinkedIn IconContact us by Email

    Opt-out of personalized ads

    Black OwnedFemale Founder