G Suite Pro Tip: how to create a dropdown list in Google Sheets (and pointers on conditional formatting)

Happy Spreadsheet Day! Today marks the release of the first-ever electronic spreadsheet from a company called Visicalc. Nearly 40 years later, spreadsheets are still an essential tool to help businesses crunch—and share—their data.

To commemorate, we’re releasing another G Suite Pro Tip to help you master the art of spreadsheet organization using two key features in Google Sheets: Data Validation and Conditional Formatting. With this tip, you’ll be able to make your spreadsheet look top notch by adding dropdown lists to cells and color-coding tasks.

Breaking it down

In this G Suite Pro Tip, we break down how to use two features in Sheets.

First, Data Validation…

Data Validation is a feature that’s most commonly used to add dropdown lists into cells within Sheets. Let’s say you want a column within your spreadsheet to track the status of a project. You might use data validation to create dropdown options within a cell that say “Not yet started,” “In progress” or “Ready.”

Go to Data > Data Validation and select from a number of options under “Criteria.” For this specific example, you can select “list of items” and insert the dropdown options you’d like to have, separated by a comma.

What some folks don’t realize is that data validation can be used for so much more than creating dropdown lists. For example, you can:

• Track upcoming work. You can use the “dates” option in data validation, and combine it with conditional formatting (see instructions below), to set it up so that anything with a certain date—or past a certain date—gets automatically grayed out in your Sheet. This is great if you need to review upcoming work at a glance. Go to Data Validation >
• Add custom meanings to checkboxes. Instead of simply adding a checkbox, you can use data validation to automatically mark the checkbox with a value. For example, if you check the box, it could mean “yes” or if you uncheck a box it could mean “no.” Go to Data > Data Validation > and select Checkbox under “criteria.” Choose “Use custom cell values” under the Criteria option and type in the meaning you’d like.
• Protect structured data or formulas. You can set the option in data validation to ‘Reject input’ for invalid entries. This is helpful if you’re trying to protect structured data, like formulas or Apps Scripts you’re running, from a multitude of editors who may be in your Sheet. Data validation can help you lock down cells so that your custom tools run as expected.

Second, Conditional Formatting…

Conditional formatting is a tool that can help you color-code your spreadsheets—like if you want to auto-assign a color to a cell based on specific criteria.

Building on the project tracker example, let’s say you want to remind team members to take action by making anything that’s categorized as “In progress” appear in yellow within your Sheet.

You can select the cell or column you want to assign colors to and go to Format > Conditional Formatting. It will open up a side panel with options.

Under “Format cells if…” select from a number of options (we recommend “text is exactly”). Type in the text that you’d like to auto-change color for. In this instance, anytime someone selects “In progress” from the dropdown options, you can choose to make the color appear yellow in the cell.

Whether it’smastering macrosorcentralizing your data, we want to help you become a G Suite Pro. Learn more tips on ourYouTube channel.