When you’re parsing through large amounts of data in a Google Sheets spreadsheet, conditional formatting can help you find those bits of information that you want. Conditional formatting helps to visualize data, by coloring in a cell and/or text, as well as to understand and be able to interpret the data available.

Whether it be by finding those pieces of data in a range or by formula, conditional formatting can be an efficient way to organize data. We’re going to show you exactly how to use this valuable tool, so keep reading.

Related: How to create an in-cell dropdown list on Google Sheets

What is conditional formatting?

When it comes to spreadsheets, conditional formatting refers to the appearance of certain cells (e.g., bolding, highlighting, etc.) being dictated by a set of predetermined conditions. In other words, once conditional formatting is established, the cells in a spreadsheet are automatically formatted in to appear in certain ways depending on the data contained within.

How to create conditional formatting in Google Sheets

The easiest way to set conditional formatting on Google Sheets is by using the Format option in the Google Sheets toolbar.

In this example, we will examine weather forecasts and data over a 30-day period, looking at how many days were predicted to be sunny, how many were predicted to be cloudy, how many times an umbrella was used when it actually rained, and a combination of those variables.

The spreadsheet version of the following tutorial can be downloaded here. Make a copy of the worksheet by selecting “Make a copy” from the drop-down in the File menu.

Conditional formatting in range

Rows, columns, and a range of cells can be formatted to background color or changed in text based on certain conditions. Type sheets.new in the URL with your Google account, and a spreadsheet on Google Sheets will be created. You will have a range of data, be it numbers or text, or a combination of both, and you want to highlight that range. In the menu bar, click on Format, which then drops down to a list. Click on Conditional formatting, which then leads to a toolbar titled “Conditional format rules” that opens to the right of the screen. A range of options are available.

In the example below for the worksheet titled “conditional formatting in range”, a range of days lists whether the day by number is either “sunny” or “cloudy.” Click on a range from cell B2 to B31. Go to Conditional formatting under Format in the menu bar. The conditional format rules toolbar then appears at the right of the screen. “Apply to range” will show B2:B31 in the selection. Under “Format rules,” for “Format cells if…” select “Text contains” and a new window for “Value or formula” appears. In that window, type Sunny, and below that in “Formatting style” select the tipped paint container icon to select the color, which in this case will be blue. To further customize the selected cells, click on the letter A icon to pick the color yellow, which will highlight the text in yellow. Click “Done” and the selected range of cells for “Sunny” will be highlighted.

For “Cloudy” days, repeat the same procedure for “Sunny” but select gray for the cell color.

Formatting the cells isn’t limited to text. Google Sheets offers a range of options that include dates and numbers, which follow a “greater than” or “less than” type of parameters.

Conditional formatting with formula

The simple version of conditional formatting with formula is the true-false statement. This is best represented in an IF function, which returns one value if a logical expression is “true” and another if it is “false.”

Syntax

=IF(logical_expression, value_if_true, value_if_false)

In the example below for the worksheet titled “conditional formatting with formula,” “Sunny” and “Cloudy” are listed as types of weather under the heading “Today’s Weather” in Column A. In Column B, the response to either would be “Yes” or “No.” To create the response in Column B, a formula using the IF function will be created. Using Column A as reference, the formula — following the syntax above — would be: “=If(B2=”Sunny”, “No”,”Yes”).” If the field in the reference cell is “Sunny,” the response would return “No” as a true response. If the cell were “Cloudy,” the response would be false, or “Yes.” Conversely, the IF function could be written with Cloudy, with a response of “Yes” if true and “No” if false.

There are websites that provide more complex formulas in conditional formatting. Some formulas outlined in Microsoft Excel, particularly with AND/OR conditions, for example, can be applied to Google Sheets.

Conditional formatting in range with formula

Combining conditional formatting by range and with formula is also possible. In the example below, the “range and formula” worksheet follows the format for the “conditional formatting in range” worksheet but adds a row for “Bring Umbrella?” For every day listed, the IF function follows the same as in the “conditional formatting with formula” worksheet but with the addition of color formatting for the “Yes” or “No” response.

For additional formatting, you can count how many days were sunny and cloudy over that 30-day period by using the COUNTIF formula.

Conditional formatting with two or more variables

Conditional formatting can be used on two or more variables by formatting a cell using a customized formula. Using the example on weather, and setting the outcome of a day on whether there was rain or no precipitation, we can set conditional formatting to determine whether the forecast for the day was either sunny or cloudy but ended up with rain or no precipitation. Conditional formatting by color can help visualize those days when it rained on sunny or cloudy forecasts. This can help to determine when it helped to bring an umbrella based on the outcome of days when the weather called for sunshine but actually ended with rain.

As an extension of COUNTIF, the COUNTIFS function sets criteria for a range of data based on matching two or more items, or variables, in the dataset.

Syntax

=COUNTIF(range of search, item in search)

=COUNTIFS(range of search, item in search, range of search, item in search)

In this case, pick a cell for the responses. Type =COUNTIF, and in parentheses, highlight the range, followed by a comma and in double quotation marks type in “Sunny.” Do the same for “Cloudy” search.

To find out how many days the forecast was sunny, but ended up with rain and no umbrella, you can use a combination of those variables in conditional formatting to highlight the text and COUNTIFS to help determine the number of days.

Still, there are limitations on using conditional formatting to apply to different criteria so COUNTIFS helps to discern the visual data.

There are many applicable formulas in conditional formatting, so find out which one suits your needs in visualizing and understanding data.

Again, the spreadsheet version of this tutorial can be downloaded here. Make a copy of the worksheet by selecting “Make a copy” from the drop-down in the File menu.

This is just one of many articles about easy to use tools in Google Sheets. Let us know what you’d like to see next. Email us here: tools@thearenagroup.net

Related: Doug Kass, who correctly predicted yields would drop, reveals top stocks for 2024