Conditional Formatting in Excel: Highlight, Rules, Examples

Last updated July 2026. To use conditional formatting in Excel, select the cells you want to check, go to Home, click Conditional Formatting, and pick a rule. The fastest options live under Highlight Cells Rules (greater than, less than, equal to, text that contains, duplicate values) and Top/Bottom Rules. Excel colors any cell that meets the rule and updates the color automatically the moment the value changes. For anything the built-in rules do not cover, choose New Rule and write a formula.

On a freshly converted PDF, conditional formatting is the fastest way to see what needs attention: negative amounts, duplicate rows from merged files, blank cells a table lost, or numbers that came in as text. You are not editing the data, only coloring it, so it is a safe first pass before you start fixing anything.

How do I use conditional formatting in Excel?

Select your range, open Home, click Conditional Formatting, and choose a rule type. For a quick highlight, hover Highlight Cells Rules and pick a condition such as Greater Than; type the threshold, choose a fill color, and click OK. Every qualifying cell colors instantly, and because the rule is live, editing a value or pasting new rows re-applies the color with no extra work.

The menu is organized by job. Highlight Cells Rules compares each cell against a value or text. Top/Bottom Rules find the highest or lowest items and above or below average. Data Bars, Color Scales, and Icon Sets shade cells by size so a column reads like a tiny chart. New Rule opens the full builder, including the formula option, and Clear Rules and Manage Rules handle cleanup and ordering.

How do I highlight duplicates with conditional formatting?

Select the column, go to Conditional Formatting, Highlight Cells Rules, Duplicate Values, and click OK. Excel colors every value that appears more than once. This is the fastest duplicate check there is, and it is the first thing to run after you combine several converted PDFs into one sheet, because merged statements and invoices are where repeat rows sneak in.

Two things to know. The rule flags every copy, not just the extras, so three identical rows all turn the same color rather than leaving one uncolored. And the match is case insensitive, so "ACME" and "acme" count as duplicates. Conditional formatting only shows you the duplicates; to actually delete them, use Remove Duplicates once you can see what you are dealing with.

How do I use conditional formatting with a formula?

Choose New Rule, then "Use a formula to determine which cells to format." Write a formula that returns TRUE for the cells you want colored, using a relative reference to the first cell of your selection. For example, to flag every row where the amount is negative, select the range starting at B2 and enter =B2<0. Excel adjusts the reference down the range, so each cell is tested against its own value.

The formula approach unlocks the rules the menu does not offer: color a cell that is text when it should be a number with =ISTEXT(B2), flag anything over a variable limit with =B2>$E$1, or highlight rows that fall on a weekend. Lock the parts of the reference that must not move with dollar signs, and leave the parts that should follow the range relative. That single rule choice, absolute versus relative, is what most formula rules come down to.

How do I highlight a whole row based on one cell?

Select the entire data range, not just one column, then create a formula rule that locks the column of the test cell with a dollar sign. To shade every row where column D says "Overdue", select A2 across to the last column and down to the last row, choose New Rule, use a formula, and enter =$D2="Overdue". The dollar sign in front of D pins the test to column D while the row number stays relative, so Excel checks each row's own D cell and colors the whole row.

The mistake that trips people up is locking the row too, like =$D$2, which tests only one cell and colors either everything or nothing. You want the column absolute and the row relative: =$D2. Get that one reference right and whole-row highlighting works every time, which is how a converted register turns into a sheet where problem rows jump out at a glance.

How do data bars, color scales, and icon sets work?

These three tools shade cells by their value so a column reads like a small chart. Data Bars draw a horizontal bar in each cell whose length reflects the number, which is useful for scanning amounts in a converted ledger without building a chart. Color Scales grade cells across a two or three color gradient, so low and high values sit at opposite ends and outliers stand out. Icon Sets drop an arrow, flag, or traffic light into each cell based on where its value falls.

Apply one by selecting the range, opening Conditional Formatting, and choosing Data Bars, Color Scales, or Icon Sets, then a style. To control the thresholds instead of letting Excel guess, open Manage Rules, edit the rule, and set the minimum, midpoint, and maximum by number or percent. One caution on converted data: if some values are text, they are treated as the lowest value and can throw the whole scale off, so convert text to numbers before you trust the shading.

Why is my conditional formatting not working?

The most common cause is a data type mismatch: your rule compares numbers but the converted cells are really text, so =B2>100 never fires because "150" as text is not greater than the number 100. Fix the data first by converting text to numbers, and the rule starts working. The second cause is the wrong reference in a formula rule, usually a dollar sign locking a cell that should move down the range.

Two more to check. Rule order matters: if several rules cover the same cells, they apply top down, and a rule higher in the list with Stop If True checked can block the ones below it. Open Conditional Formatting, Manage Rules, and use the arrows to reorder or clear the Stop If True box. And make sure "Applies to" actually covers your data; a rule built on a small selection will not color rows you added later unless you extend its range.

How do I remove conditional formatting?

Select the cells, go to Conditional Formatting, Clear Rules, and choose Clear Rules from Selected Cells or Clear Rules from Entire Sheet. That strips the coloring without touching the underlying values. To remove one rule and keep the rest, open Manage Rules, select the rule, and click Delete Rule. If you want to keep the current colors as permanent fill rather than a live rule, copy the range and paste it back as values into a plain area, but note that conditional colors do not survive a values-only paste, so most people simply reapply a static fill instead.

Conditional formatting is one step in tidying a converted file. It shows you the problems; the fixes come next. Once you have colored the duplicates, negatives, and text numbers, work through the full cleanup sequence for data after a PDF conversion, and start every sheet with a clean PDF to Excel conversion so there is less to flag in the first place. When those highlighted rows turn out to be repeat bills, catching them before they are paid is the job of an accounts payable process built to spot duplicates automatically.