Conditional formatting is when you automatically format the styling of cells in your spreadsheet based on the data in each cell. Google Sheets lets you use conditional formatting to apply different fonts, fill colors, and other styles, making your spreadsheets instantly easier to read.
All you need to do to use conditional formatting is create some rules for your spreadsheet to follow. Below, we’ll show you how to create those conditional formatting rules with Google Sheets.
When to Use Conditional Formatting
You can use conditional formatting for pretty much any reason that you might typically format a spreadsheet. The main advantage is to save yourself time and ensure consistency, but conditional formatting also encourages you to think about the setup and styling of your spreadsheet in advance.
You could use conditional formatting to differentiate between types of data in a set, grouping together employees by their department, for example. Or you could also use it to draw attention to problematic values, such as a negative profit. On occasion, it may also be useful to downplay obsolete records, such as activities with a date in the past.
How to Create Conditional Formatting in Google Sheets
Perhaps the best way to demonstrate conditional formatting is with a simple example. Start with a new spreadsheet, and follow the steps below:
-
Enter some example values in a few rows.
- Highlight the range of cells that you want to be eligible for conditional formatting.
-
From the Format menu, choose Conditional formatting.
- Change the Format cells if… dropdown to Is equal to.
- Enter one of your example values in the Value or formula box.
-
Choose the formatting you want to add, such as a fill color.
- Click Add another rule.
- Replace the previous value with a different one from your spreadsheet and select a different format or color for this value.
- Repeat the previous two steps for more values, using different formatting each time.
-
Once finished, click the Done button to see a list of the rules you’ve created.
Types of Condition That Google Sheets Supports
Google Sheets supports a wide range of conditions that you can base your formatting on. These are built around three core data types: text, dates, and numbers.
Text
The simplest condition for text, empty, depends on whether the cell contains any value at all.
To check for the presence of a specific piece of text, use contains. This condition can check for a fixed piece of text or it can also use pattern matching whereby each ? stands in for any character and * stands in for zero or more of any character.
Finally, for more structured text matches, starts with, ends with, or exactly will narrow down the possible matches.
Dates
Dates are straightforward, although they support some useful predefined values in addition to user-defined ones. Use conditional formatting to look for dates before, after, or equal to a selection of relative dates (e.g. tomorrow, in the past year). You can also compare them to a specific date of your choice.
Numbers
Finally, you can use conditional formatting to look for numbers equal to, greater than, less than, or between a range of other numbers.
Custom Conditional Formatting Formulas
Custom formulas make conditional formatting more powerful since they can highlight entire ranges and even reference cells outside of the range being formatted. To demonstrate custom conditional formatting, let’s take another very simple example.
This time, create a table in Google Sheets with two columns. We chose to record stock levels for different fruits.
To highlight all rows in the data with more than four items in stock:
- Select the data range (A2:B5).
- Open the Conditional format rules window.
- Choose Custom formula is as the condition.
- As a Value, enter =$B2>4.
Pay particular attention to the details of the formula we used.
As with a normal formula in a cell, it must begin with an equals (=) sign. The dollar ($) before the column (B) makes it an absolute reference, so the comparison always refers to data from that specific column. The row (2) is relative to the first row of the data range, so for each row, the comparison takes place with the stock value in the same row.
Types of Formatting Supported in Google Sheets
You can choose from a limited set of preset styles in Google Sheets by clicking the Default text, just under Formatting style.
Beyond that, formatting options are fairly limited, but should suffice for most practical purposes.
First up, you can configure any combination of the standard font styles: bold, italic, underline, and strike-through. The latter, in particular, can be useful to indicate invalid or obsolete data.
The other type of styling that you can apply relates to colors: both text (foreground) and fill (background). The standard color pickers are available, including custom colors to give a choice of essentially any color you desire.
Conditional Formatting With a Color Scale
A convenient method for visualizing numerical values is the color scale. This assigns colors from a palette according to each cell’s relative value. Colors are applied to the background, otherwise known as Fill color.
This technique is often used in charts and maps where, for example, a darker color can indicate a lower value.
Google Sheets’ implementation is limited, but very easy to use. Select a range of numbers, as before, but this time, change the tab near the top of the Conditional format rules window from Single color to Color scale.
Under Format rules you can define the nature of your color scale. Clicking the Preview button reveals a limited set of predefined palettes to choose from. You can customize the scale by selecting different values or percentages to represent the minimum color point, the maximum color point, and the midpoint. The color pickers alongside each point allow for more control over the color palette.
Other Aspects to Consider
If you make extensive use of this feature in a spreadsheet, it can be easy to lose track of what conditional formatting rules you put in place. The side panel gives no way of viewing all the rules in play; it simply shows which rules are set up for the currently selected cells.
To view all your rules, select all cells using either the keyboard shortcut (Ctrl/Cmd + A) or by clicking the blank rectangle in the very top-left corner before row 1 and column A.
Note that if you copy and paste a cell containing conditional formatting, you’re also copying that conditional formatting for that cell.
Finally, if you create multiple contradicting rules for a cell or range, only the first rule applies. You can change the order by dragging a rule using the vertical dots icon on its left side.
Save Time and Ensure Consistency With Conditional Formatting
Conditional formatting is the kind of Google Sheets feature that can save a lot of time, although you could easily go without ever knowing it exists. Even at the simplest level, it allows you to present spreadsheet data with added meaning, without additional on-going effort.