ThisCalculator

Everyday

How to Create a Conditional Formatting Rule

Updated · Standard formula, shown below

Conditional formatting in Excel and Google Sheets automatically changes a cell’s appearance—like its fill color, font, or borders—when the cell’s value meets a condition you set. This guide walks you through creating a rule from scratch, whether you want to highlight duplicates, flag overdue dates, or color-code sales figures. You’ll need a spreadsheet with the data you want to format, and you should know which cells should be affected.

What You’ll Need

  • A spreadsheet application (Microsoft Excel 2010 or later, Google Sheets, or LibreOffice Calc).
  • Data in a range of cells (e.g., a column of numbers, dates, or text).
  • Basic understanding of cell references (like A1 or $B$2) and comparison operators (=, >, <, etc.).

Step-by-Step

  1. Select the data range. Click and drag to highlight the cells you want to format. For example, select A2:A100 if you have values in column A from row 2 to 100. Include the header row only if you want it formatted too.
  2. Open the Conditional Formatting menu.
    • In Excel: Go to the Home tab, click Conditional Formatting in the Styles group.
    • In Google Sheets: Click Format in the top menu, then Conditional formatting.
  3. Choose a rule type.
    • Excel: From the dropdown, select Highlight Cells Rules (for basic comparisons), Top/Bottom Rules, Data Bars, Color Scales, or Icon Sets. For a custom formula, choose New Rule > Use a formula to determine which cells to format.
    • Google Sheets: In the sidebar that appears, under “Format cells if,” pick a preset like “Greater than” or “Text contains,” or choose “Custom formula is” at the bottom.
  4. Set the condition.
    • For presets: Enter the value or choose a cell reference. For example, “greater than 100” or “equal to $B$1”.
    • For a custom formula: Type a formula that returns TRUE for cells you want to highlight. For instance, =A2>100 (relative reference to the first cell in your selection).
  5. Define the formatting style. Click the Format button (Excel) or the paint roller icon (Sheets) to choose a fill color, font color, bold, borders, etc. Preview it in the dialog.
  6. Apply the rule. Click OK (Excel) or Done (Sheets). The formatting instantly applies to cells that meet the condition.
  7. Manage multiple rules. To add more rules, repeat the steps. In Excel, go to Conditional Formatting > Manage Rules to see all rules, change their order, or delete them. In Sheets, the sidebar lists all rules; click one to edit or the trash icon to remove.

Common Mistakes

  • Wrong cell references in custom formulas. If your formula references an absolute cell like $A$1 instead of a relative one like A1, it may format all cells based on that single cell’s value.
  • Selecting the entire column. If you select A:A, the rule applies to empty cells too, which can cause performance issues or unexpected formatting.
  • Forgetting to lock references when needed. When comparing to a fixed cell (like a threshold in $B$1), you must use absolute references ($B$1) so the rule doesn’t shift as it evaluates each cell.
  • Overlapping rules. If two rules apply to the same cell, the one with higher priority (or earlier in the list) wins. Confusing results can occur if you don’t check rule order.

Tips

  • Use the “Stop If True” checkbox (Excel) to prevent lower-priority rules from applying when a condition is met.
  • For text, use “Text contains” or the formula =ISNUMBER(SEARCH("keyword",A2)) for case-insensitive matching.
  • To highlight entire rows based on a cell value, select the whole row range and use a formula with a mixed reference, like =$A2>100.
  • Test your rule on a small range first to ensure the logic is correct before applying to a large dataset.
  • Use color scales for a quick visual gradient—they’re great for spotting trends without writing formulas.

Troubleshooting

  • No formatting appears. Double-check that the condition is actually true for some cells. For example, if you set “greater than 100” but your data is all below 100, nothing highlights. Also confirm the range is correct.
  • Formatting applies to wrong cells. This usually means your range selection was off, or your custom formula’s relative reference points to the wrong cell. Edit the rule and adjust the range or formula.
  • Rule works in one row but not others. Check if you used absolute references where relative were needed. For a column of data, the formula should reference the first cell of the range, e.g., =A2>100, not =$A$2>100.
  • Performance is slow. Too many rules or whole-column ranges can slow down Excel. Limit the range to the actual data and avoid volatile functions like TODAY() if possible.
  • Rule disappears after sorting. Conditional formatting is tied to cells, not values. If you sort, the formatting moves with the cells. To keep formatting with values, you may need to reapply after sorting or use a table (Excel) which handles this automatically.

Frequently Asked Questions

Can I apply conditional formatting to text?

Yes. Use “Text contains” or a custom formula like =ISNUMBER(SEARCH("yes",A2)) to highlight cells with specific text.

How do I highlight duplicates?

In Excel, use “Duplicate Values” under Highlight Cells Rules. In Google Sheets, use the custom formula =COUNTIF(A:A, A1)>1.

Can I use conditional formatting with dates?

Absolutely. For example, to highlight dates older than 30 days, use =A2.

How do I remove a conditional formatting rule?

Go to Conditional Formatting > Manage Rules (Excel) or the Format > Conditional formatting sidebar (Sheets), select the rule, and delete it.

Does conditional formatting work in Google Sheets on mobile?

You can view formatting on mobile, but creating or editing rules is only available in the desktop version.

Can I use formulas that reference other sheets?

Yes, but in Excel you need to use defined names or indirect references, while Google Sheets allows direct cross-sheet references like =Sheet2!A1>10.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.