How to Use Conditional Formatting in Google Sheets

What Conditional Formatting Does

Conditional formatting is a Google Sheets feature that automatically changes the appearance of cells based on the values they contain. Instead of manually highlighting cells or scrolling through rows to spot patterns, you set a rule once and Sheets applies the formatting instantly to hundreds or thousands of cells. A cell background might turn red if its value exceeds a threshold, or a gradient color scale might shade a range from blue to red based on numeric values. The result is a visual scan that lets you spot trends, outliers, and patterns in seconds.

Think about managing a project timeline. You want overdue tasks in red and upcoming deadlines in yellow at a glance. Without conditional formatting, you would click each cell and manually color it. With conditional formatting, you write one rule, and every overdue task colors itself. This transforms data from static numbers into a living dashboard that updates as values change.

Beyond visual appeal, conditional formatting enforces data quality. You can highlight duplicate entries, flag missing information, or color-code status columns so managers spot bottlenecks instantly. Sales teams use it to surface high-value deals; finance teams use it to flag budget overages; educators use it to visualize grade distributions. The feature bridges the gap between raw data and actionable insight.

Opening the Conditional Formatting Panel

Start by selecting the range of cells you want to format. Click anywhere in your data range, then select the exact cells or drag to highlight a block. If you want to format an entire column, click the column header. The selection can be a single cell, a range like A1:C50, or even multiple non-adjacent ranges if you use Ctrl+Click.

Next, open the Format menu at the top of the Sheets window. Scroll down and click “Conditional formatting”. A panel slides open on the right side of your screen. This panel is your command center for all conditional formatting rules on this sheet. At the top, you’ll see the range you selected. Below that are dropdown menus and input fields where you define your formatting logic.

Single Color Rules: The Foundation

The simplest conditional formatting rule applies a single color when a condition is met. In the formatting panel, the first dropdown says “Format rules”. Click it and select “Single color” to start. This option lets you pick from several preset conditions: “Text contains”, “Text is exactly”, “Greater than”, “Less than”, “Between”, “Is empty”, “Is not empty”, and a few others.

Let’s walk through a practical example. You have a project task list in column A with due dates in column B and status in column C. You want to highlight any task where the due date has passed. Select cells B:B, open conditional formatting, choose “Single color”, then select “Less than” from the first dropdown. In the value field, type =TODAY(). This tells Sheets to color any cell with a date earlier than today. Choose a red background in the color picker below, and click Done. Every overdue task turns red instantly.

The beauty of single color rules is their simplicity. You can stack multiple rules on the same range. Add another rule that highlights tasks due within the next 7 days with a yellow background using “Between” and TODAY() and =TODAY()+7. Now red is overdue, yellow is urgent, and uncolored cells are on schedule. Each rule operates independently, though order matters if rules overlap.

You can also use “Text contains” to highlight cells with specific keywords. For a status column, highlight all cells containing “Blocked” in orange or all cells containing “Complete” in green. Sheets is case-insensitive by default, so “complete”, “Complete”, and “COMPLETE” all match. If you need exact case matching, use a custom formula instead.

Color Scale Formatting: Heatmaps and Gradients

Color scales apply a gradient of colors across your data, with darker or more saturated colors representing higher or lower values depending on your choice. This is perfect for visualizing the distribution of numeric data at a glance. Open the Format menu, select Conditional formatting, then change the dropdown from “Single color” to “Color scale”.

A three-color gradient appears with three sliders: minimum, midpoint, and maximum. By default, Sheets assigns a light green to your minimum values, yellow to the midpoint, and a dark green to the maximum. You can customize each color by clicking the color box next to each slider. A common pattern is red for low values, white for middle, and green for high values, creating a heat map effect.

Imagine a spreadsheet with regional sales data. You have regions in column A and sales figures in column B. Select B2:B50, open conditional formatting, choose “Color scale”, and Sheets automatically calculates the minimum, midpoint, and maximum values in your range. Regions with the lowest sales appear red, medium sales appear yellow, and highest sales appear dark green. This visual gradient lets you instantly identify your strongest and weakest markets without reading numbers.

Color scales are also valuable for displaying survey responses, temperature ranges, or any metric with a clear low-to-high spectrum. You can lock the scale to specific numbers instead of auto-calculating. In the minimum and maximum dropdowns, select “Custom” and enter hardcoded values. This is useful if you want the scale to represent a standard range, like 0 to 100 for scores, regardless of what your actual data contains.

Custom Formulas: The Power Tool

Custom formulas unlock the real power of conditional formatting. Instead of choosing from preset conditions, you write a formula that evaluates to TRUE or FALSE for each cell. When TRUE, the formatting applies. This opens endless possibilities. Open the Format menu, select Conditional formatting, then change the dropdown to “Custom formula is”.

Here’s a practical example. You want to highlight every other row with a light gray background to improve readability. In the custom formula field, type =MOD(ROW(),2)=1. The MOD function returns the remainder of dividing the row number by 2. Odd rows return 1, even rows return 0. This formula returns TRUE for odd rows, so they color themselves gray. Select the entire data range including headers, apply the formula, and you have alternating row colors without manually selecting every other row.

Another example: highlight any cell in column A that appears more than once, indicating a duplicate entry. Select A:A, open custom formula, and type =COUNTIF($A$1:$A$100,A1)>1. This counts how many times the current cell’s value appears in the range A1:A100. If the count is greater than 1, the cell is a duplicate and gets highlighted in red. The dollar signs lock the range so the formula always counts from A1 to A100, while A1 shifts to A2, A3, and so on for each row.

You can reference other cells in your formula. Select the data range where you want formatting applied, then write a formula that references a helper column. For instance, if column D contains a status and you want to highlight all rows where column D says “Review Required”, type =D1=”Review Required”. Sheets applies this formula to each row, checking the corresponding cell in column D for that row.

Combining functions expands possibilities further. Use ISBLANK() to highlight empty cells, AND() to combine multiple conditions, OR() to highlight cells matching any of several criteria, SEARCH() to check if a cell contains a substring, and REGEX() to match patterns. A formula like =AND(A1>100,ISBLANK(B1)) highlights cells in A that exceed 100 AND have a blank cell in the same row’s column B.

Conditional Formatting Based on Another Cell

Often you want to highlight cells in one column based on a value in another column. Select the range you want to format, say A2:A100, then open conditional formatting and choose “Custom formula is”. Write a formula that references a different column but uses a relative reference for the row.

Example: you have a budget column in A and a spend column in B. You want to highlight cells in column A where the actual spend in column B exceeds the budget. Select A2:A100, type =B2>A2 as the custom formula, and choose a red background. Sheets evaluates this formula for each row. For row 2, it checks if B2 > A2. For row 3, it checks if B3 > A3. The formula automatically adjusts the row reference while keeping the column logic intact.

The key is using relative references for the row (A2, not A$2) and absolute references (dollar signs) for ranges you don’t want to shift. If your rule needs to compare against a static value in cell G1, type =$G$1 to lock that cell while the comparison cell shifts with each row. This technique lets you create sophisticated conditional logic without writing a formula for every single cell.

Highlighting Duplicates

Duplicates in your data can cause errors, double-counting, and confusion. Conditional formatting makes spotting them instant. Select the range where you expect potential duplicates, open conditional formatting, choose “Custom formula is”, and use =COUNTIF($A$1:$A$100,A1)>1. This counts each value and highlights it if it appears more than once in the range.

Every duplicate gets the same formatting, so a value appearing three times gets highlighted three times. This tells you exactly which entries repeat. You can then decide whether to delete duplicates, consolidate them, or investigate why they exist. If you want to highlight only the second and subsequent occurrences of a duplicate (leaving the first one unhighlighted), use =COUNTIF($A$1:A1,A1)>1 instead. This counts from the top of the range only up to the current row, so the first occurrence counts as 1 (not highlighted), and the second occurrence counts as 2 (highlighted).

Highlighting Entire Rows Based on a Condition

Sometimes a single cell’s value matters to the entire row. If column C contains a status and you want to highlight the entire row whenever status is “Pending”, select all columns in that row range, like A2:Z2 or A:Z, then apply a custom formula that references column C but uses an absolute reference for the column.

Select your data range, say A2:Z100, open conditional formatting, choose “Custom formula is”, and type =$C2=”Pending”. The dollar sign before C locks that column, so every cell in the formula checks its row’s column C value. If C2 says “Pending”, the entire row 2 gets highlighted. If C100 says “Pending”, the entire row 100 gets highlighted. All cells in a row share the same formatting because they all check the same cell in their row’s column C.

This is invaluable for status tracking, priority flags, and approval workflows. If a row’s status is “Urgent”, highlight the whole row in red so managers see it immediately. If status is “Completed”, apply a light gray to show it’s done but not yet archived. One rule, applied across all columns and rows, creates a consistent visual system that guides attention to what matters most.

Managing Multiple Rules

A single range can have multiple conditional formatting rules. Each rule operates independently. If a cell meets multiple rules’ criteria, Sheets applies the formatting from the first rule in the list. You can see all rules for your range by selecting it and opening conditional formatting again. The panel lists every active rule.

To edit a rule, click the pencil icon next to it. To delete a rule, click the trash icon. To reorder rules and change their priority, drag the handle next to each rule up or down. If you want a specific rule to take precedence, move it higher in the list. This matters when rules conflict. If rule 1 colors cells red and rule 2 colors cells blue, rule 1’s red wins because it appears first.

You can also set a rule to stop processing if it matches. In the rule editor, toggle “Done” at the bottom. If this option is on and the rule’s condition is true, Sheets ignores all rules below it in the list. This prevents lower rules from overriding your formatting.

Conditional Formatting with Date Formulas

Dates are common in spreadsheets, and conditional formatting handles them well. Use TODAY() to reference the current date, EDATE() to add or subtract months, and comparison operators to flag dates that are past, upcoming, or within a specific window.

Select your date range, open conditional formatting, choose “Custom formula is”, and write formulas like =A1TODAY() to highlight future dates, or =AND(A1<=TODAY()+7,A1>=TODAY()) to highlight dates within the next 7 days. Combine these into multiple rules: red for overdue, orange for this week, yellow for next week, and no color for further out.

You can also highlight dates that match a specific month or year using MONTH(), YEAR(), and TODAY(). For birthdays, use =MONTH(A1)=MONTH(TODAY()) to highlight today’s birthday month. For anniversary tracking, use =MONTH(A1)=MONTH(TODAY()) AND DATEDIF(A1,TODAY(),”Y”)>0 to highlight anniversaries of past events.

Copying Conditional Formatting to Other Cells

Once you’ve set up a rule, you can copy it to other cells without rewriting it. Select a cell with conditional formatting, then use the Format Painter tool or Paste Special. The Format Painter is the brush icon in the toolbar. Click it, then click and drag over the cells where you want the formatting copied. The rules transfer with the correct relative references.

Alternatively, select the cells with formatting, copy them with Ctrl+C, select the destination range, then use Paste Special (Ctrl+Shift+V) and choose “Paste conditional formatting only” at the bottom. This copies rules without affecting the underlying data. When pasting, row and column references adjust automatically, just as they would if you had written the formula in each cell.

Practical Scenarios

Here are four real-world examples where conditional formatting transforms data into actionable information.

Project Status Tracker: Columns for Task, Owner, Due Date, and Status. Use color scale on the Due Date column to show which tasks are closest to their deadlines. Add a single color rule to highlight Status cells containing “Blocked” in red and “At Risk” in orange. Use alternating row colors with =MOD(ROW(),2)=1 for readability. Managers can see at a glance which tasks are blocked, which are at risk, and which are on track.

Inventory Low-Stock Alerts: Columns for Item, Current Stock, Reorder Level, and Last Updated. Use a single color rule to highlight Current Stock cells where the value is less than the Reorder Level column. Add a custom formula like =C2

Student Grade Highlighting: Columns for Name, Assignment 1, Assignment 2, Midterm, Final, and Grade. Use a color scale on the Grade column to show the distribution of performance across students. Red for low grades, green for high grades. Add a single color rule to highlight any cell below 70 in orange to flag grades needing improvement. Teachers can identify struggling students and top performers instantly.

Budget Variance Reporting: Columns for Department, Budget, Actual, and Variance. Use a custom formula =D2>(C2*0.1) to highlight Variance cells that exceed 10 percent of budget. Highlight the entire row to flag departments with major overages. Color-code by positive or negative variance. Finance teams spot budget issues without manually reviewing every line item.

FAQ Schema

Conditional formatting in Google Sheets transforms static data into dynamic, visual dashboards. Whether you’re tracking projects, managing inventory, evaluating student performance, or monitoring budgets, conditional formatting highlights patterns and exceptions that would otherwise require manual inspection. Start with single color rules for basic conditions, graduate to color scales for visualizing distributions, and master custom formulas to unlock sophisticated conditional logic. Your spreadsheets become faster to read, easier to act on, and more professional in appearance. The investment in learning these tools pays dividends every time you open a sheet.

For related functionality, explore how to wrap text in Google Sheets to improve cell readability, or learn about COUNTIF in Google Sheets for counting cells that meet specific criteria. You might also benefit from sorting by date in Google Sheets to organize time-based data before applying conditional formatting. Understanding merge cells in Google Sheets helps when creating headers or summary rows that stand apart from your data. For more advanced conditional logic, check out the IF formula in Google Sheets to build formulas that power your rules. Lastly, once your data is formatted and ready, make a graph in Google Sheets to create visual charts from your highlighted data.