Google Sheets Conditional Formatting for Data Tables

Yes. In Google Sheets, you can apply conditional formatting to cells in a table by selecting its range and choosing Format > Conditional formatting. You can highlight individual cells or entire rows when values meet a condition; the key is setting the rule’s range and formula references correctly.

What “table” means in Google Sheets

People use “table” to mean two related things in Sheets. It can mean an ordinary rectangular range of cells organized into columns and rows, or the newer Google Sheets table feature created from a range with Insert > Table. Conditional formatting applies to cells in either case: you choose the cells that should respond to a rule, then define the condition and appearance.

A table’s built-in styling and conditional formatting are separate. Table formatting can provide a consistent base appearance, while a conditional-formatting rule changes a cell’s appearance when its condition is true. For instance, a table might have alternating row shading, and a rule can still turn overdue dates red. A conditional rule does not replace the table’s data, change its sort order, or validate an entry; it only changes how qualifying cells look.

This distinction matters when the table grows. A rule applies to the range shown in its “Apply to range” field. If you add rows beyond that range, inspect the rule to confirm that those rows are covered. Don’t assume that a formatted table and a conditional-formatting range will always expand together.

Apply a rule to a table range

In Google Sheets in a web browser, use the following steps. The same basic process works for an ordinary data range and for cells in a Sheets table.

Select the cells you want the rule to affect. You can select one column, a block of columns, or the whole data area. Include the header only if you want the header formatted by the rule.
Choose Format > Conditional formatting. The conditional-formatting rules panel opens.
Check the Apply to range field. Edit it if necessary so it contains the intended cells, such as A2:D500.
Under Format rules, choose a condition, such as “Text contains,” “Greater than,” or “Is empty.” For a formula-based condition, choose Custom formula is.
Choose the formatting style, such as a fill color, text color, or bold text, and select Done.

For a quick visual rule, you can select a numeric column, choose “Greater than,” enter a threshold, and choose a color. For example, a sales column can highlight values above 1,000 without needing a formula. If you want a rule to depend on another column or format a whole row, a custom formula is more flexible.

Choose the right kind of formatting rule

Google Sheets offers several ways to define a condition. The best choice depends on whether you are comparing a cell to a fixed value, checking its contents, or evaluating a relationship across columns.

Single-color rules: Use these to apply one style when a condition is met. They suit simple checks such as dates before today, text containing a word, or numbers above a target.
Color scale: Use this to show a gradient across a numeric or date range. A scale is useful for comparing relative values, such as low, middle, and high scores, but it does not communicate a single pass-or-fail threshold as clearly as a single-color rule.
Custom formula: Use this when the condition depends on another cell, combines tests, or should format an entire row based on one column.

Think about what the color is meant to tell the reader. A gradient can reveal a pattern in values, while a distinct fill is easier to interpret for an exception such as a late task. Avoid using several nearly identical colors for conditions readers need to distinguish quickly.

Use custom formulas for row-based highlighting

A common request is to color every cell in a row when a status column contains a particular label. Suppose your headers are in row 1, task names are in column A, due dates are in B, owners are in C, and statuses are in D. Set the apply range to A2:D500, choose Custom formula is, and use:

With statuses in D2:D500 and the data rows in A2:D500, use =$D2="Overdue". This highlights the row wherever the status in column D is “Overdue.”

The dollar sign before D fixes the status column as Sheets evaluates the rule across the selected range. The row number is not fixed, so the reference moves from D2 to D3, D4, and so on. If you used =$D$2="Overdue", every row would test only D2, which is usually not what you want.

You can also flag duplicate IDs. Set the apply range to A2:A500 and use this custom formula:

With IDs in A2:A500, use =COUNTIF($A$2:$A$500,$A2)>1. It highlights each ID that occurs more than once in that bounded range. If blank cells should not be highlighted, use a condition that excludes blanks:

With IDs in A2:A500, use =AND($A2<>"",COUNTIF($A$2:$A$500,$A2)>1). This applies the duplicate test only when the current ID cell is not empty.

For a date-based example, suppose due dates are in column B and you want past-due dates highlighted only when the task is not marked complete. Apply the rule to A2:D500 and use:

With due dates in B2:B500 and statuses in D2:D500, use =AND($B2<TODAY(),$B2<>"",$D2<>"Complete"). The formula checks that the due date is earlier than today, is not blank, and is not paired with a Complete status. Adjust the status text to match the labels in your sheet.

Set the range so new rows are included

The “Apply to range” setting is one of the most important parts of a rule. A rule set to A2:D30 does not necessarily cover data entered in row 31. If your list is expected to grow, choose a range with room for more rows, such as A2:D500, or use an open-ended column range when that is appropriate. A bounded range can be easier to inspect and can avoid applying formatting to a very large unused area.

When a rule uses a custom formula, its references must line up with the first row of the apply range. For example, if the range starts at row 2, a row-based formula normally refers to row 2. If you change the apply range to start at row 3 but leave the formula referencing row 2, the conditions may no longer match the rows you expect.

After inserting, deleting, or moving rows or columns, revisit the conditional-formatting panel and check both the range and the formula. This is especially useful for shared sheets where people add records in different ways. If a rule seems to stop at an old boundary, expand the range rather than recreating the entire rule.

Understand multiple rules and visual conflicts

A cell can be covered by more than one conditional-formatting rule. For example, one rule might color all overdue tasks red, while another highlights high-priority tasks yellow. When rules overlap, the resulting appearance can be confusing, particularly if both rules set the same style property, such as fill color. Review the rules in the conditional-formatting panel and simplify overlapping conditions if the result is unclear.

Make each rule communicate a distinct fact. If “Overdue” should always be red and “High priority” should be yellow, decide what should happen to a task that is both. You might use different formatting properties, or create conditions that prevent the rules from competing. Test the rule with a row that meets the relevant conditions rather than judging it only from rows where the rules do not overlap.

Conditional formatting is a display aid, not a change to the cell’s underlying value. A date that appears red is still the same date, and a value highlighted as a duplicate is not automatically removed. Users can still sort, filter, or edit the data according to the sheet’s other settings.

Common problems and how to fix them

Nothing changes: Check that the condition is true for at least one cell in the apply range. Confirm that the range includes the data rows, and check whether the rule is looking at the correct column.
Every row gets the same result: Review the dollar signs in the custom formula. Lock a column when the condition should always refer to that column, but usually leave the row number relative so each row evaluates its own value.
The header is colored unexpectedly: Start the apply range at the first data row, often row 2, instead of including the header row.
Dates behave like text: Check that the cells contain actual date values, not text that merely looks like a date. Inconsistent entry formats or imported data can cause comparisons to behave unexpectedly.
New entries are not highlighted: Inspect the “Apply to range” field. Extend the range to include the added rows.
The wrong color appears: Look for another conditional-formatting rule covering the same cells. Simplify overlapping rules or adjust their conditions and styles.

When diagnosing a custom formula, test it on a small sample range first. Confirm the exact spelling of text labels, including spaces, and verify that the formula’s starting row matches the range’s first row. A rule can be syntactically valid and still fail to express the intended logic.

Google Sheets compared with Excel

The core idea is similar in Google Sheets and Microsoft Excel: select a range, create a conditional-formatting rule, and choose a format. The menu paths and some interface details differ. In Sheets, use Format > Conditional formatting; in Excel for Microsoft 365 on Windows, conditional formatting is on the Home tab. Instructions for one program should not be assumed to match the other exactly.

Google Sheets formulas use familiar spreadsheet references and functions, but a workbook’s layout and rule ranges still need attention when moving between products. If you share a sheet with Excel users or export it, inspect the resulting rules in the destination program, especially when the rules use custom formulas or overlap. A color appearing in both programs does not guarantee that every rule was transferred with identical behavior.

Frequently Asked Questions

Can I conditionally format an entire row based on one cell?
Yes. Apply the rule to the whole row range and use a custom formula that locks the condition’s column while leaving its row relative. For example, a status in column D can control formatting across columns A through D.

Does conditional formatting work with the Google Sheets Table feature?
Yes. You can create a rule for cells in a Sheets table by selecting its cell range and using Format > Conditional formatting. Check the rule’s apply range when the table changes or gains rows.

Can I highlight duplicate values in a table?
Yes. Use a custom formula with COUNTIF to check how many times each value appears in the relevant column. Add a nonblank check if empty cells should not be treated as duplicates.

Why does my conditional formatting stop at a certain row?
The rule’s apply range probably ends at that row. Open the conditional-formatting panel, select the rule, and extend the range to cover the additional records.

Can I use a color scale and a text-based rule together?
Yes. You can create more than one rule for a range, but overlapping formats may be hard to interpret. Review how the rules interact and choose distinct visual cues where possible.

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.