View All Conditional Formatting Rules in Google Sheets

To view conditional formatting rules in Google Sheets, open the sheet in a web browser and choose Format > Conditional formatting. The rules panel opens on the right and lets you inspect the rules on the active sheet, including the ranges each rule applies to; repeat this on other tabs to check the rest of the spreadsheet.

How to open the conditional formatting panel

Open the Google Sheets file, select the worksheet tab you want to inspect, and use the menu path Format > Conditional formatting. You do not need to create a new rule to see existing ones. The panel on the right is headed “Conditional format rules” and displays rules associated with that sheet. Click a listed rule to inspect its settings.

You can start with a cell or range selected, but selecting cells is not required just to open the panel. If you are investigating a particular cell, select it first: this makes it easier to compare the cell with each rule’s “Apply to range” field. If the sheet has many rules, scroll through the panel; a rule list can extend beyond the visible area.

For a quick check of whether a cell is covered, click the cell and inspect the listed rules’ ranges. A cell may be included in a range even if it currently looks unformatted, because the rule’s condition might not be true for its value. Conversely, a visible color might come from a manually applied fill rather than conditional formatting.

What “all rules” means in Google Sheets

The conditional-formatting panel is a sheet-level view, not a single control center for every worksheet in the file. A spreadsheet with several tabs can have different rules on each tab. To review the whole file, open the panel on each worksheet in turn. Hidden tabs may also contain rules, so unhide a tab if you need to audit every worksheet.

Rules are attached to cell ranges. Their “Apply to range” entry might be a single cell, a rectangular range such as A2:D100, or multiple ranges. This is why the rule list is more useful than looking only at colored cells: a rule may cover blank rows, future entries, or cells whose current values do not meet its condition.

There is not necessarily a rule for every colored cell. Someone may have applied a fill color manually, or a cell may use a color scale or another formatting feature. Conditional formatting is only one source of appearance. When tracking down a color, inspect the rule panel and the ordinary formatting controls before concluding that a rule is missing.

How to read each rule

Click a rule to see its main parts. The first is the range to which it applies. The second is the condition, shown under “Format cells if.” The third is the formatting style that Sheets applies when the condition is met, such as a fill color, text color, bold text, or a color scale. The condition and style together explain what the rule is meant to do.

For example, a rule might apply to D2:D50 and highlight cells that are less than zero. That rule will not color a positive number in D12, even though D12 is inside the range. Another rule might apply to A2:C50 but use a custom formula to make the whole row stand out based on a status in column B.

When you inspect a custom-formula rule, check both the formula and its reference style. For example, suppose rows 2 through 100 contain a task status in column B and a due date in column C, and the rule applies to A2:C100. The formula =AND($B2="Overdue",$C2<TODAY()) highlights a row when its status is Overdue and its date is earlier than today. The dollar sign locks the column reference while the row number changes as the rule is applied down the range. Check the top row of the apply-to range when interpreting relative references.

Inspecting overlapping or complicated rules

Several rules can apply to the same cell. For example, a cell might be covered by a rule for overdue tasks and another rule for high-priority tasks. In that situation, inspect every rule whose range includes the cell, not just the first rule whose name or condition seems relevant. The list order can matter when rules overlap, so note the order shown in the panel as well as each rule’s condition and style.

To understand an unexpected result, write down the cell, its value, the rules that cover it, and whether each condition should evaluate as true. Also check whether a rule uses a custom formula with relative references: a formula that works for the first row can behave differently farther down if its references were not set as intended. If the range contains multiple separate areas, verify whether the formula’s reference pattern makes sense for each area.

A color scale is different from a simple “greater than” or text-matching rule. It assigns colors across a range according to values, often using minimum, midpoint, and maximum points. Click the rule to see whether it is a color scale or a single-color condition; otherwise, it can be easy to mistake a gradient for several separate rules.

Edit, reorder, or remove a rule

Once you have located a rule, click it in the panel to review or change its range, condition, or formatting style. Be cautious when editing a range: changing A2:A50 to A2:A500 can affect many more cells than intended. If you only want to change the rule’s appearance, adjust its formatting options without altering the range or condition.

To remove a rule, open it in the conditional-formatting panel and use the trash icon for that rule. Deleting a conditional-formatting rule does not delete the cell values, but it removes the automatic formatting that rule supplied. If the cells still have a color afterward, that appearance may be coming from another rule or from manual formatting.

When rules overlap, the panel lets you change their order by dragging rules. Reordering can help resolve competing styles, but do not assume that changing the order fixes an incorrect range or formula. First establish which rules actually cover the cell and what each condition tests; then adjust the order only if priority is the issue.

Common reasons a rule seems missing

You are on the wrong tab. Rules belong to individual worksheets. Open the panel on the tab containing the cells you are checking.
The cell is outside the apply-to range. A rule that covers A2:A20 does not automatically cover A21 or another column. Inspect the complete range field.
The condition is currently false. A rule can be present and correctly applied without producing a visible style for the cell’s current value.
The appearance is manual formatting. A fill or text color applied directly to a cell does not have to appear as a conditional-formatting rule.
A formula reference is off. Relative references move across the apply-to range. A missing or misplaced dollar sign can make a custom formula test a different cell than intended.
Another rule also affects the cell. Review all rules covering that address and consider their order, conditions, and styles.

If a cell looks wrong, test the condition against the cell’s actual contents rather than its displayed appearance alone. A date can be stored as text, for example, and a text value that looks like a date may not behave like a true date in comparisons. Similarly, leading or trailing spaces can prevent a text condition from matching what appears to be the same word. These are data issues, not proof that the rule is absent.

Finding rules in a large spreadsheet

For a large workbook, make a simple audit pass one worksheet at a time. Open the panel, inspect each rule’s apply-to range, and note its purpose, condition, and style in a separate checklist if the rules are difficult to follow. You can compare the ranges with the sheet’s headers and data layout to spot rules that stop too early or extend into unrelated columns.

Start with the cells that matter most, such as summary indicators, deadlines, or exception flags. Select one of those cells and identify every rule whose range includes it. Then inspect the surrounding rows and columns for rules with similar ranges. This targeted approach is often faster than trying to infer a rule’s purpose from the color pattern across an entire sheet.

Google Sheets does not provide a built-in, single workbook-wide conditional-formatting report in the sidebar. If you need documentation, record each tab’s rules yourself or use an approved add-on or script only after considering the access it requests. For ordinary troubleshooting, the built-in panel is usually the clearest way to review and maintain rules without changing spreadsheet data.

Google Sheets compared with Excel

The basic idea is similar in Google Sheets and Microsoft Excel: conditional formatting changes how cells look when specified conditions are met. The menus and rule-management interfaces are different, however. In Google Sheets in a browser, use Format > Conditional formatting. Excel for Microsoft 365 on Windows uses Home > Conditional Formatting > Manage Rules to manage rules, with controls that can show rules for a selected range or the worksheet.

Do not follow Excel menu instructions when you are working in Google Sheets. Excel’s rule manager and Google Sheets’ right-side panel are not the same tool, and details such as how a rule is edited or how overlapping formats appear can differ. If a file is opened in both programs, check the rules in the application where you intend to maintain them; do not assume the interface or behavior is identical.

Frequently Asked Questions

Can I view all conditional formatting rules for a Google Sheets file at once?
No. The built-in panel shows rules for the active worksheet, so open it separately on each tab to inspect the whole file.

Why does a rule not show a color on the cell I selected?
The cell may be outside the rule’s apply-to range, or the condition may not be true for its current value. A manual fill or another rule can also affect what you see.

Can I see who created a conditional formatting rule?
The conditional-formatting panel shows the rule’s settings, not a creator field. Google Sheets version history may help you review past file changes, but it may not identify the author of an individual rule setting.

Does deleting a rule delete the cell data?
No. Deleting the rule removes that rule’s automatic formatting; it does not erase the values in the cells. Check for other rules or manual formatting if the cells still look colored.

Can I check rules on my phone?
The Google Sheets mobile apps have different interfaces from Sheets in a web browser, and rule-management options can be more limited. For a complete rule audit, use the browser interface and open the conditional-formatting panel.

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.