If you are managing a large spreadsheet filled with sales figures, student grades, or inventory counts, scanning columns of black-and-white numbers to identify trends or pinpoint problems is a massive strain on the eyes. You might spend ten minutes just trying to find all the accounts that are overdue.
Conditional Formatting in Google Sheets solves this by forcing the spreadsheet to do the visual work for you. It allows you to create rules that automatically change the background color, text color, or formatting of a cell based on the data inside it. For example, you can tell Sheets to automatically paint a cell red if a sales number drops below $1,000, or turn a cell green if a task is marked “Complete.”
How to Apply Basic Conditional Formatting
Setting up a basic rule takes only a few seconds.
- Open your Google Sheet.
- Use your mouse to click and drag, highlighting the specific range of cells you want to monitor. (You can highlight a single column, a specific block of data, or click the square in the top-left corner to highlight the entire sheet).
- Click on Format in the main menu bar at the top of the screen.
- Select Conditional formatting from the dropdown menu.
A new panel labeled “Conditional format rules” will slide out on the right side of your screen. This is your command center.
Configuring the Rule
In the right-hand panel, you need to define two things: the trigger condition and the resulting visual style.
1. Set the Condition
Under the “Format rules” section, click the dropdown menu that currently says “Is not empty”. This opens a long list of logical triggers.
- For Text: You can choose “Text contains” or “Text is exactly”. (e.g., Rule: If text is exactly “Failed”, turn the cell red).
- For Numbers: You can choose “Greater than,” “Less than,” or “Is between”. (e.g., Rule: If number is less than 50, highlight it).
- For Dates: You can choose “Date is before” or “Date is after”. (e.g., Rule: If the date is before today, highlight it yellow to indicate it is overdue).
Once you select a condition, a blank text box will appear below it. Type your target value (e.g., the word “Failed” or the number “50”) into that box.
2. Set the Formatting Style
Under the “Formatting style” section, you design what happens when the cell triggers the rule.
- Click the Paint Bucket icon to choose a background fill color.
- Click the ‘A’ icon with an underline to change the text color.
- You can also click the Bold, Italic, or Strikethrough icons.
As you adjust these settings, the highlighted cells in your spreadsheet will preview the changes in real-time. Once it looks correct, click the green Done button at the bottom of the panel.
Using Color Scales for Data Trends
If you want to visualize a trend across a large range of numbers—like student test scores from 0 to 100—creating individual rules for every single number is impossible. Instead, you should use a Color Scale.
A Color Scale creates a heatmap. It automatically finds the highest and lowest numbers in your selection and applies a gradient of color across all the cells in between.
- Highlight the column of numbers (e.g., Test Scores).
- Go to Format > Conditional formatting.
- In the right-hand panel, look at the very top and click the Color scale tab (next to “Single color”).
- Click the color preview box under “Format rules” to choose a gradient template. A common choice is the Red-Yellow-Green gradient.
Instantly, Google Sheets will color the lowest scores deep red, the middle scores yellow, and the highest scores bright green, giving you an immediate visual understanding of the class’s performance without reading a single number.
Managing and Prioritizing Multiple Rules
You can apply dozens of different formatting rules to the exact same column of data. However, if rules conflict (e.g., one rule says “If number > 50 turn green” and another says “If number = 60 turn blue”), Google Sheets needs to know which rule wins.
In the Conditional Formatting panel, your rules are listed in order of priority from top to bottom. The rule at the very top overrules everything below it. If you need a rule to take precedence, hover your mouse over it in the list, click the four-dot icon on its left edge, and drag it to the top of the list.