Edit Conditional Formatting in Google Sheets

To edit conditional formatting in Google Sheets, select a cell covered by the rule, open Format > Conditional formatting, and click the rule in the panel on the right. Change its range, condition, or formatting style, then click Done. If the rule does not appear, select a cell within its applied range or inspect the ranges shown in the panel.

What editing a rule changes

Conditional formatting changes how cells look when they meet a condition. A rule can apply a fill color, text color, bold or italic styling, or a color scale. For example, a task tracker might shade a row when its status is Overdue, or a sales sheet might highlight values below a target. Editing the rule changes what cells are checked, which values qualify, or how qualifying cells appear.

These rules do not change the underlying cell values. A red cell is still the same date, number, or text it was before the rule applied. This matters when you sort, calculate, or export data: the conditional format is a visual signal, not a replacement for the data itself. If you need to change a value automatically, use a formula in the sheet or another suitable feature rather than relying on formatting.

Open and edit an existing rule

Open the spreadsheet in Google Sheets in a web browser.
Select a cell that you believe is covered by the conditional-formatting rule. Selecting the affected cell makes it easier to find the relevant rule, especially in a sheet with many rules.
Choose Format > Conditional formatting. Sheets opens the conditional-formatting rules panel on the right.
Review the rules in the panel and click the one you want to change. Check its applied range and condition before editing; two rules can look similar while applying to different parts of the sheet.
Update the Apply to range field, the condition, or the formatting style. Click Done to save the change.

You can edit a rule without selecting a cell inside it by opening the panel and locating the rule in the list. However, selecting an affected cell first is a useful shortcut when a large spreadsheet contains many unrelated rules. If you are unsure which rule controls a cell, inspect its range and condition rather than changing the first rule you see.

Change the range a rule applies to

The Apply to range field determines which cells the rule checks and formats. It may contain one range, such as A2:D50, or several ranges separated by commas. To extend a rule to new rows, edit the range—for example, change A2:D50 to A2:D200. To limit it to one column, use a range such as D2:D200.

Be deliberate about the first row. A heading row is often excluded so a rule meant for data does not color the column labels. If the table starts on row 3, a range beginning at row 2 may include a blank row or a different type of content. Also check whether a rule uses a fixed endpoint. A rule that stops at row 50 will not automatically format row 51 just because you add data there.

When a rule is intended for several separate areas, you can include multiple ranges in the range field. Check that each area has the same layout relative to the formula, if the rule uses a custom formula. Applying a rule to a broad range can be convenient, but it can also make unrelated cells change appearance unexpectedly.

Change the condition or criteria

After selecting a rule, use the condition controls to change what triggers it. Available choices depend on the type of rule. A single-color rule can test for conditions such as text containing a phrase, a number greater than a threshold, a date, or a cell that is empty. Color-scale rules instead map values across a selected range to colors; they are useful for showing relative highs and lows rather than marking one fixed threshold.

For example, suppose a budget sheet uses a rule to highlight expenses above $500, but the threshold should now be $750. Click the rule, keep the number-based condition, replace the threshold with 750, and click Done. The rule changes what it considers high; it does not alter the expense amounts. For a text rule, changing the phrase from Pending to Approved changes which matching cells receive the style.

Check the condition type as well as the value. A number rule, a date rule, and a text rule interpret data differently. A date stored as text may not behave like a genuine date value, even if it looks similar on screen. If the rule seems to miss qualifying data, verify the cell contents and format before assuming the condition itself is wrong.

Edit a custom formula rule

A custom formula lets a rule test a more specific relationship, including one cell determining the formatting of another. In a task table with task names in column A and status in column B, suppose the data begins in row 2 and the rule should shade each task row when its status is Overdue. Set the range to A2:B100 and use this custom formula:

=$B2="Overdue"

The dollar sign fixes the status column at B, while the row number remains relative so each row checks its own status. If the rule is applied to A2:B100, the first row checks B2, the next checks B3, and so on. The formula tests the status cell; the selected formatting is applied to the cells in the rule’s range.

For a different example, assume scores are in C2:C40 and you want to highlight scores below 60. Set the range to C2:C40 and use:

=C2<60

Because this range begins in row 2, the relative reference checks the score in each corresponding row. When changing a custom formula, make sure its references match the top-left cell of the applied range. A formula written for a range beginning in row 2 may give unexpected results if the range is changed to begin in row 3 without adjusting the reference.

Change colors and other formatting

To change the visual result, select the rule and use the formatting options in its panel. For a single-color rule, you can choose a fill color, text color, and available text styling. The preview shows the style that will appear when the condition is met. Pick a combination that remains readable: pale fills with dark text are often easier to scan than saturated fills with low-contrast lettering.

For a color scale, review the minimum, midpoint, and maximum settings. A scale is best when the goal is to compare values across a range, such as low-to-high response times. It is not the same as a fixed rule that marks every value over a threshold. If readers need to know that a value crossed a specific limit, a single-color condition may communicate that more clearly.

Conditional formatting can make a busy sheet easier to interpret, but color should not be the only way important information is conveyed. A reader may print the sheet in grayscale or have difficulty distinguishing certain colors. Pair the visual cue with clear labels or meaningful cell contents when the distinction matters.

Understand overlapping rules

A cell may fall within the ranges of multiple conditional-formatting rules. For instance, one rule might highlight overdue items in red while another highlights all high-priority items in yellow. When rules overlap, the order shown in the conditional-formatting panel can affect the result, particularly when rules set competing styles. Review the list and use the panel’s controls to reorder rules when the displayed order is producing an unintended appearance.

Do not assume that deleting one rule is the only way to resolve a conflict. You may be able to narrow one rule’s range, make its condition more specific, or adjust its style so the rules communicate different things. After making a change, check a cell that meets both conditions and another that meets only one. That small comparison helps show whether the rules behave as intended.

Common problems and how to fix them

The expected rule is missing. Select a cell that should be affected, reopen the panel, and inspect the listed rules. A rule may apply to a neighboring range or another sheet area.
New rows are not formatted. Check whether the applied range ends before the new rows. Expand it, and verify that any custom formula still lines up with the range’s first row.
The wrong row is highlighted. In a custom formula, look for references that are fixed or relative in the wrong way. For row-by-row checks, the column may need a dollar sign while the row number remains relative, as in =$B2="Overdue".
A rule appears to do nothing. Confirm the condition type, threshold, and actual cell contents. Text that contains extra spaces, numbers stored as text, or dates stored as plain text can prevent an expected match.
The format looks different from the preview. Check for another rule applying to the same cells and review the rules’ order. Also make sure you are examining a cell that actually meets the condition.
Changing one rule affects other cells. The rule may cover a wider range than expected. Recheck Apply to range before saving further edits.

Delete, replace, or reuse a rule

If a rule is no longer useful, select it in the conditional-formatting panel and use the trash icon to remove it. Deleting a rule removes its conditional formatting behavior; it does not delete the cell contents. If you only want to stop formatting one part of the sheet, consider narrowing the applied range instead of removing a rule that is still useful elsewhere.

To create a replacement, select the intended range and choose Format > Conditional formatting, then use Add another rule. This is often safer than repurposing a rule that affects several unrelated sections. You can also copy a suitable formatted cell and use Edit > Paste special > Conditional formatting only where that option is available in the Sheets menu, then inspect the resulting range and rule before relying on it. Reusing formatting does not remove the need to check relative references.

Google Sheets compared with Excel

The basic idea is shared by Google Sheets and Excel: a condition controls how selected cells appear. The menus differ. In Google Sheets, use Format > Conditional formatting and edit rules in the side panel. In Excel for Microsoft 365 on Windows, the comparable controls are under Home > Conditional Formatting, and rules are managed through Excel’s rule-management interface. Instructions for one program should not be followed as though its menu names and dialogs were identical in the other.

Custom formulas may look similar in both programs, but spreadsheet features and behavior can differ, especially when a workbook is converted or shared. If a file moves between Sheets and Excel, review the conditional-formatting rules in the destination application. Check the applied ranges, formulas, and appearance rather than assuming every rule will transfer unchanged.

Frequently Asked Questions

How do I find all conditional formatting in a Google Sheet?
Open Format > Conditional formatting to view the rules in the side panel. Select a cell in a particular area first to help identify rules relevant to it, then check each rule’s applied range.

Can I edit conditional formatting on my phone?
The Google Sheets mobile app has its own interface and may not show the same controls as the browser. For the browser steps described here, open the spreadsheet on a computer and use Format > Conditional formatting.

How do I apply a rule to an entire column?
Edit Apply to range and enter the intended column or a suitably open-ended range, while excluding a header if needed. For custom formulas, make sure the reference is aligned with the first row of the data range.

Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a condition is met. To change the contents themselves, edit the values or use a formula or other data-processing method.

Why does my custom formula highlight the wrong cells?
Check whether the formula’s row and column references match the applied range. Relative references move across the range, while dollar signs lock a column or row. Also verify that the formula starts by testing the range’s top-left cell in the way you intend.

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.