Conditional Formatting in Excel and Google Sheets Guide

Conditional formatting changes a cell’s appearance automatically when its value meets a rule. In Excel for Microsoft 365, select the cells and choose Home > Conditional Formatting; in Google Sheets, select them and choose Format > Conditional formatting. Both can highlight values, dates, text, duplicates, and formula-defined conditions, though their rule editors and some features differ.

What conditional formatting does

Ordinary formatting is applied directly: you choose a fill color, font, or border, and it stays that way until you change it. Conditional formatting applies a format only while a condition is true. If the underlying value changes, the displayed formatting can change too. This makes it useful for spotting exceptions in a changing table without manually scanning every row.

Common uses include highlighting overdue tasks, marking sales above a target, showing duplicate entries, identifying low inventory, and shading values along a color scale. For example, a project tracker could use a red fill for tasks marked “Late,” while a stock sheet could flag quantities below a reorder threshold.

The rule does not alter the stored value or replace a formula in the cell. It changes how the cell is displayed. A red cell is not necessarily an error, and a green cell is not proof that its data is correct; the color reflects the rule you set. Readers should be able to understand the rule rather than treating color as unexplained meaning.

How rules and ranges work

A conditional-formatting rule has two main parts: the range it applies to and the condition that decides when its format appears. The range might be one column, a block of cells, or a table area. The condition might be a built-in test such as “greater than,” a text or date test, a color scale, or a custom formula.

For a formula-based rule, the formula is evaluated relative to the cells in the selected range. That makes references important. In a rule applied to a whole row, a reference such as $C2 keeps the test in column C while allowing the row number to change. The dollar sign fixes the column; leaving the row number unfixed lets each row use its own status.

Formatting rules can overlap. Excel lets you set rule order and, for supported rules, use Stop If True to prevent lower-priority rules from being evaluated for a cell. In Sheets, rule order in the conditional-formatting panel matters; when more than one rule is true for a cell, the first applicable rule controls the formatting. If a cell’s appearance is surprising, inspect overlapping rules and their order.

Set up a rule in Excel for Microsoft 365

In Excel for Microsoft 365 on Windows, start by selecting the cells the rule should affect, then open Home > Conditional Formatting. The Highlight Cells Rules submenu offers common tests such as Greater Than, Text That Contains, and Duplicate Values. Choose a rule, enter the value or text, select a format, and confirm. For gradients, use Color Scales; for in-cell indicators, use Data Bars or Icon Sets.

To make a formula-based rule, select the target range and choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Enter a formula that evaluates to TRUE or FALSE, choose Format to set the appearance, and confirm. Use Home > Conditional Formatting > Manage Rules to inspect, edit, reorder, or remove rules. The manager can show rules for the current selection or the worksheet, so check its “Show formatting rules for” setting if a rule seems missing.

Excel for Mac has conditional formatting on the Home tab as well, though dialog placement and wording can vary by release. Excel for the web also supports many standard rules, but its editing interface is not identical to the Windows desktop Rule Manager. If exact rule-management options matter, use the desktop application and verify that the workbook’s rules behave as intended after opening it in another version.

Set up a rule in Google Sheets

In Google Sheets in a web browser, select the cells and choose Format > Conditional formatting. A side panel opens. On the Single color tab, confirm the Apply to range field, choose a condition from Format cells if, and set the formatting style. You can also choose Color scale to create a gradient based on the values in the range. Changes are applied through the panel, and additional rules can be added there.

For a custom formula, choose Custom formula is in the condition list, enter a formula, and set the style. To color a complete row according to a status in column C, apply the rule to the row range and use a reference that fixes column C but lets the row adjust. Sheets supports many familiar spreadsheet formula patterns, but not every Excel feature or interface works identically. In particular, Sheets does not present Excel’s full Manage Rules dialog or its Stop If True control.

Sheets saves edits to the spreadsheet as you work, and people with appropriate access can see the resulting formatting. If the sheet is shared, consider whether the color scheme is clear to collaborators and whether they have permission to edit rules. The rules themselves are part of the spreadsheet, not a separate image or report.

Useful formula examples

Custom formulas are useful when a built-in condition cannot describe the pattern you need. These examples use syntax that works in both Excel and Google Sheets. Select the stated range before adding each rule, and choose the desired formatting after entering the formula.

Format an entire row when its status is Late. Assume task data occupies A2:C5, with the status in column C, and apply the rule to A2:C5. Use =$C2="Late". If C3 contains Late, the formula is TRUE for row 3, so A3:C3 receives the chosen format; rows with another status do not match.
Highlight repeated names. Assume names are in A2:A6, with Ana appearing in A2 and A4, and apply the rule to A2:A6. Use =COUNTIF($A$2:$A$6,A2)>1. For either Ana cell, COUNTIF returns 2 and the comparison is TRUE; a name appearing once returns 1 and is not highlighted. Excel also offers a built-in Duplicate Values rule, while this formula gives a similar option in Sheets.
Mark amounts at or above a threshold. Assume values are in B2:B5, with 1,250 in B2, and apply the rule to B2:B5. Use =$B2>=1000. For B2 the test is TRUE because 1,250 is at least 1,000; a value of 900 would not meet the condition.

When adapting examples, make sure the first row number in the formula matches the first row of the applied range. If the selected range starts at row 2 but the formula tests row 1, the rule may look one row out of alignment. Also distinguish a blank from a zero: a cell containing 0 is a numeric value, not an empty cell.

Excel and Sheets: key differences

The basic idea transfers easily between the applications, and simple conditions such as “greater than 100” are available in both. Formula-based rules often translate with little or no change when they use functions supported by both programs. But rule editors, advanced controls, and workbook behavior are not identical, so do not assume a complex setup will look or behave exactly the same after conversion.

Rule management: Excel provides a Conditional Formatting Rules Manager with controls for scope, ordering, and Stop If True. Sheets manages rules in a side panel and uses the listed order to resolve overlapping conditions.
Built-in visual rules: Excel offers Color Scales, Data Bars, and Icon Sets. Sheets offers color scales and single-color rules, but does not provide the same built-in Icon Sets and Data Bars experience.
Duplicate detection: Excel has a built-in Duplicate Values option under Highlight Cells Rules. In Sheets, a custom formula such as COUNTIF is a practical way to identify repeats.
Collaboration and file handling: Sheets is designed for browser-based shared editing. Excel workbooks can also be shared, but desktop, web, and converted-file behavior can vary, especially with more elaborate rules.

For a straightforward shared tracker, either application may be suitable. If a workbook depends on Excel-specific visual rules or precise priority controls, keep and manage it in Excel. If several people need to edit a live browser-based sheet, Sheets’ shared workflow may be more convenient. In either case, test an important rule on sample rows before relying on it across a large dataset.

Good practices for readable rules

Conditional formatting is most useful when it draws attention to a small number of meaningful signals. Applying several bright fills, icons, and gradients at once can make a worksheet harder to read than the unformatted data. Choose colors with enough contrast, use consistent meanings, and add a written status or label when the distinction matters.

Apply rules to the smallest sensible range, such as the data rows rather than an entire column, unless future rows should be included automatically.
Use a clear threshold tied to a real decision, such as inventory below a reorder point, rather than choosing colors arbitrarily.
Keep formulas simple and test boundary cases: a value exactly equal to the threshold, a blank cell, a duplicate, and a row with missing information.
Document unusual rules in a note or nearby instruction so another person can maintain the sheet.
Do not communicate a critical distinction by color alone. Include text, symbols, or another cue, particularly for readers with color-vision differences or when the sheet may be printed in grayscale.

Common problems and how to fix them

If a rule appears to do nothing, first check the Apply to range in Sheets or the rule’s scope in Excel. Then confirm that the test value truly meets the condition and that the formula begins with an equals sign. For formula rules, check relative references, quotation marks around text, and whether the formula’s starting row matches the range.

If the wrong cells are highlighted, inspect whether a reference should be fixed with dollar signs. For example, when every row should check column C, the column should stay fixed as the rule moves across the selected range. Conversely, fixing the row when it should change can make every row test the same record. Test the formula in an ordinary worksheet cell if needed, then remove the temporary test afterward.

If colors seem inconsistent, look for overlapping rules and review their priority. In Excel, check the Rules Manager and whether Stop If True is enabled. In Sheets, inspect the order of rules in the side panel. Also check for pre-existing direct formatting: conditional formatting can override the appearance while its rule is true, which may make manual fill changes seem ineffective. Finally, verify that numbers are stored as numbers and dates as dates; text that merely looks numeric may not satisfy a numeric comparison.

When to use another approach

Conditional formatting is a display aid, not a substitute for organizing or validating data. If you need to prevent invalid entries, use data validation instead. If you need to calculate a status that should appear as text, use a worksheet formula in a status column. If you need a fixed report color that should not change when values change, apply ordinary formatting. For large or complicated analyses, filters, sorting, PivotTables, or a separate summary may communicate the pattern more clearly than many conditional rules.

Frequently Asked Questions

Does conditional formatting change the cell value?
No. It changes the cell’s appearance when a condition is met; it does not rewrite the value or formula stored in that cell.

Can I use a formula in conditional formatting?
Yes. Excel has a formula-based New Rule option, and Sheets includes Custom formula is. A formula rule should evaluate to TRUE or FALSE for the relevant cells.

Can Excel and Google Sheets use the same conditional-formatting formula?
Often, for simple formulas using functions both support. However, advanced rules and some features differ, so review the rule after copying or converting a workbook.

Why is my conditional formatting one row off?
The formula’s row reference may not match the first row of the applied range. Check the selected range and whether row references should change as the rule is applied.

Can I highlight duplicates in Google Sheets?
Yes. Apply a custom formula rule to the range and use COUNTIF to test whether each value appears more than once. Excel also provides a built-in Duplicate Values rule.

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.