Google Sheets 2D Conditional Formatting: A Practical Guide

Google Sheets can apply conditional formatting across a two-dimensional range: select the cells, open Format > Conditional formatting, and use a color scale or a custom formula that evaluates each cell against its row, column, or both. For example, a custom formula such as =B2>B$1 can compare every value in a data grid with the threshold at the top of its column.

What 2D conditional formatting means

“2D conditional formatting” is not a separate Google Sheets command. People usually use the phrase to describe formatting a rectangular grid in which the condition depends on where a cell sits: its value might be compared with a value in the same row, a column header, or both. A monthly report, for instance, might contain employees down the rows and months across the columns. Conditional formatting can flag sales that exceed each employee’s target or fall below a monthly minimum.

There are two common interpretations. A color scale shades cells according to their numeric values across a selected range, creating a heat map. A custom formula applies a format only when a logical test is true. The second approach is more flexible because it can make a cell’s result depend on related cells outside the selected grid.

In either case, the key is to choose the correct range and understand how the rule treats references as it is evaluated at different positions. A formula that works for the upper-left cell may produce an unintended result across the rest of the grid if its row and column references are not anchored correctly.

How conditional formatting works in Sheets

In Google Sheets in a web browser, select the range and choose Format > Conditional formatting. The conditional formatting panel lets you set an apply-to range, choose a condition or color scale, and specify the formatting style. For a formula-based rule, choose Custom formula is under the format rules. The formula must evaluate to TRUE for a cell to receive that rule’s formatting.

For custom formulas, Sheets evaluates the formula relative to the first cell in the apply-to range, then adjusts relative references as it considers other cells. If the apply-to range starts at B2, a formula written for B2 is the starting point. The rule does not need to be separately written for every cell; Sheets adjusts references according to standard spreadsheet reference behavior.

A relative reference such as B2 can move by row and column as the rule is applied.
A dollar sign before a column, as in $B2, keeps the column fixed while allowing the row to change.
A dollar sign before a row, as in B$1, keeps the row fixed while allowing the column to change.
A fully fixed reference such as $B$1 stays on the same cell everywhere in the range.

These reference rules are the foundation of most two-dimensional custom formatting. Use relative references for values that should move with each formatted cell, and anchors for headers or lookup values that should remain in the same row or column.

Example: compare every cell with its column threshold

Suppose B1:F1 contains a different target for each month and B2:F6 contains the values to check. The target in B1 applies to values in column B, the target in C1 applies to column C, and so on. Set the apply-to range to B2:F6. With the range starting at B2, enter this custom formula:

With monthly thresholds in B1:F1 and values in B2:F6, use =B2>B$1.

The B2 part is relative, so it changes for each cell being evaluated. The dollar sign in B$1 fixes the comparison to row 1 while allowing the column to shift. Thus, when Sheets evaluates C2, it effectively compares C2 with C1; when it evaluates B3, it compares B3 with B1. If B1 contains 70, B2 contains 82, and B3 contains 64, the rule is true for B2 and false for B3.

To set it up, select B2:F6, open Format > Conditional formatting, choose Custom formula is, enter the formula, and choose a fill or text style. Check that the apply-to range still reads B2:F6 before clicking Done. If the selected range instead starts at C2, the formula’s relative starting reference needs to match that top-left cell, or the comparison will be offset.

Use both a row target and a column threshold

A rule can test two conditions at once. Imagine employee-specific targets in A2:A6, monthly minimums in B1:F1, and results in B2:F6. You may want a result highlighted only when it meets both the employee’s target and the threshold for that month. Apply the rule to B2:F6, whose top-left cell is B2.

With employee targets in A2:A6, month thresholds in B1:F1, and results in B2:F6, use =AND(B2>=$A2,B2>=B$1).

Here, $A2 holds the target column steady while the row changes for each employee. B$1 holds the threshold row steady while the month column changes. B2 moves in both directions. The AND function returns TRUE only when the result is at least as large as both relevant comparisons. For example, if A2 is 80, B1 is 75, and B2 is 82, both tests are true and B2 is formatted. If B2 were 78, it would meet the monthly minimum but not the employee target, so the rule would return FALSE.

Make sure the data, row targets, and column thresholds actually align with this layout. If targets are in another column or the selected range begins on a different row, adjust the references rather than copying the example unchanged.

Build a heat map with a color scale

If the goal is to see high and low values at a glance, a color scale is usually simpler than writing a custom formula. Select the numeric grid, open Format > Conditional formatting, and use the Color scale tab in the panel. Choose colors for the minimum, midpoint if needed, and maximum. Sheets assigns shades based on the values in the selected range.

A color scale answers a different question from a formula rule. A heat map shows relative magnitude: it helps readers see clusters, peaks, or unusually low areas. A custom formula can enforce a specific business condition, such as “at least 100” or “greater than the target in this row.” With a color scale, the shade assigned to a value may depend on other values in the chosen range, so a particular color is not necessarily a fixed pass-or-fail signal.

Choose the scale range carefully. Including totals, blank sections, or a column with a very different measurement can affect the visual contrast. If a grid contains values with different units or meanings, separate scales may be clearer than treating all cells as one comparable population. For a fixed threshold, use a rule with a specific condition or a custom formula rather than relying on a color gradient.

Highlight an entire row based on one cell

Sometimes the desired effect is not to color one result cell, but to highlight a whole record when a status column meets a condition. For example, if statuses are in F2:F20 and the table spans A2:F20, apply the rule to A2:F20 so that the formatting covers each row.

With statuses in F2:F20 and the table in A2:F20, use =$F2="Late".

The fixed $F keeps the status check in column F as the rule moves across the row. The row number is relative, so each formatted row checks its own status cell: row 2 checks F2, row 3 checks F3, and so on. The word Late must match the cell’s text. If the sheet uses a different label, such as Overdue, replace the text in the formula accordingly. Applying this rule to F2:F20 alone would format only the status cells, not the entire records.

Common problems and how to fix them

The wrong cells are highlighted: Check the apply-to range and make the formula’s starting reference match its top-left cell. A formula designed for B2 can shift incorrectly if the rule is applied starting at C2.
Every cell uses the same comparison: A fully anchored reference such as $B$1 never moves. That is useful for a single global threshold, but wrong if each column has its own header value. Use B$1 when the row should stay fixed and the column should move.
Formatting stops at a particular row or column: Expand the apply-to range to include the full grid. A correct formula cannot format cells that are outside the rule’s range.
A text check does not match: Confirm that the formula’s text matches the cell content, including spelling and any extra spaces. If the value is generated by a formula, check the displayed result rather than assuming the underlying cell is a manually entered label.
Blank cells get unexpected formatting: Add a test that excludes blanks when needed. For example, with values in B2:F6, a formula such as =AND(B2<>"",B2>B$1) requires the current cell to be nonblank as well as above its column threshold.
Two styles seem to compete: Review the rules listed for overlapping ranges. More than one rule may apply to the same cell, so simplify overlapping rules or revise their ranges and conditions.

When a rule behaves strangely, test the logic in a spare cell before changing the formatting setup. For instance, compare one grid value with the header it should use and verify whether the test ought to be true. Then inspect the first cell in the apply-to range and the dollar signs in the rule. This isolates formula logic from visual styling.

Google Sheets and Excel: what carries over

The general ideas of conditional formatting, custom formulas, and mixed references are familiar to Excel users too. The menu names and dialog layout differ, however. In Google Sheets, the browser menu is Format > Conditional formatting; Excel for Microsoft 365 on Windows uses Home > Conditional Formatting on the ribbon. Do not follow Excel ribbon instructions when working in Sheets.

Both programs use dollar signs to anchor row and column references, and formulas such as a comparison against a column header can often be adapted between them. Still, verify the apply-to range, the first-cell reference, and the rule options in the program being used. Spreadsheet applications can differ in interface details and in how particular formatting options are presented. This guidance describes Google Sheets in a web browser, not Excel’s menu workflow.

When to use another approach

Conditional formatting is designed to make patterns visible, not to change values or create a permanent classification. If readers need explicit labels such as Pass and Review, a separate results column with a formula may be more accessible and easier to filter. If they need a summary of which categories occur most often, a pivot table or chart may answer the question better than coloring every cell.

For a compact numeric matrix, a color scale is quick to set up. For a rule tied to targets, dates, statuses, or other cells, a custom formula offers more control. For complex logic involving many exceptions, consider whether helper columns would make the conditions easier for other people to understand and maintain. A clear layout is often more useful than a single elaborate rule that only its creator can interpret.

Frequently Asked Questions

Can Google Sheets apply conditional formatting to a whole 2D range?
Yes. Set the apply-to range to the rectangular grid. Use a color scale for a value-based heat map or a custom formula when each cell must be checked against related row or column values.

How do I compare each cell with the value at the top of its column?
For a grid starting at B2 and thresholds in row 1, apply a custom formula such as =B2>B$1 to the grid. The relative column changes across the range while the threshold row stays fixed.

Why does the first row work but the rest do not?
Check whether the formula uses an absolute row or column where it should use a relative reference. Also confirm that the formula is written relative to the top-left cell of the apply-to range.

Can I highlight a full row when one cell contains a status?
Yes. Apply the rule to the whole table range and use a formula that locks the status column but leaves the row relative, such as =$F2="Late" when column F contains statuses and the selected range starts on row 2.

Is a color scale the same as a conditional formula?
No. A color scale maps values to shades across a range, while a custom formula tests a condition and applies a chosen format when that test is true. Use the latter for a fixed target or a comparison with headers.

Does “2D conditional formatting” require an add-on?
No. The built-in conditional formatting panel supports color scales and custom-formula rules for selected ranges. An add-on is not necessary for the examples described here.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.