Conditional formatting is one of those Excel features that, once you discover it, you use every day. It makes cells change color, font or show icons automatically based on their content. It is not just for making a report look nicer: used well, it is an alert system that warns you about errors, duplicates, suspicious values and changes between versions before they become a problem. In this article you will see its most useful applications for data quality control.
Where it is and how it works
Select a range and go to Home > Conditional Formatting. You will find predefined rules (highlight cells, top/bottom rules, data bars, color scales, icon sets) and the New Rule option, which lets you use formulas for custom cases.
To review or delete rules, use Conditional Formatting > Manage Rules.
1. Highlight duplicates
Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
It is ideal for columns that should be unique: product codes, invoice numbers, customer emails. If any color appears, review those records before they cause trouble. To remove duplicates in bulk afterwards, use our online duplicate remover.
2. Spot required cells left blank
If a column should never be empty (for example, the price or tax ID), create a formula rule:
- Select the range, for example C2:C500.
- New Rule > Use a formula to determine which cells to format.
- Type
=ISBLANK(C2)and choose a light red fill.
Any gap will stand out.
3. Out-of-range values
To catch data entry errors, flag impossible or unlikely values:
- Negative prices: Highlight Cells Rules > Less Than 0.
- Discounts above 50%: Greater Than 0.5.
- Future dates in an invoice date column: formula
=C2>TODAY().
Adjust the thresholds to your business. A 60% discount may be correct in a clearance sale, but someone should check it.
4. Compare two columns
To see which rows differ between two columns (for example, old price and new price):
- Select the range in the new column, D2:D500.
- New formula rule:
=D2<>C2. - Choose a color.
The cells that changed will be highlighted.
5. Find values that are in one list but not another
If you have August’s customer list on the “August” sheet and September’s on “September”, you can highlight September’s new customers:
=COUNTIF(August!$A:$A,A2)=0
Apply this rule to the code range on the September sheet. Colored cells are customers who were not there in August. Swap the sheets and you will see the ones that disappeared too.
This method works well for one column. If you need to compare whole tables with several columns and sheets, the Excel comparison tool on our Excel tools page will give you a complete report without creating any rules.
6. Extra spaces
Invisible spaces are a constant source of errors. This rule exposes them:
=LEN(A2)<>LEN(TRIM(A2))
Highlighted cells contain extra spaces at the start, at the end or between words.
7. Numbers stored as text
To find amounts that Excel treats as text:
=ISTEXT(C2)
Applied to an amount column, it points out exactly the cells that will cause trouble in sums and PivotTables.
8. Color scales to see trends
Color scales (green to red) applied to a column of price changes show at a glance which products rose the most. Data bars are useful for comparing magnitudes, such as sales by salesperson.
Good practices
- Do not overdo the colors. If everything is highlighted, nothing stands out. Use one or two colors with a clear meaning.
- Document the rules. Add a small legend explaining what each color means.
- Apply rules to the right range. Applying them to entire columns slows the file down and increases its size.
- Check for duplicated rules. When you copy and paste cells, rules multiply. Clean them up in Manage Rules.
- Use the right references. In formulas, the reference to the first cell of the range should be relative (no
$) so it adjusts for each row.
Frequently asked questions
Does conditional formatting work in LibreOffice and Google Sheets?
Yes. Both programs have conditional formatting with predefined and formula-based rules. Some functions may have different names.
Is conditional formatting kept when converting to PDF?
Yes. When you convert with our Excel to PDF tool, the colors applied by the rules appear in the document, which is useful for sharing reports with visible alerts.
How many conditional formatting rules can I have?
Excel has no low practical limit, but hundreds of rules slow the file down. If you notice lag, open Manage Rules and delete duplicated rules or rules applied to unnecessarily large ranges.
Can I use conditional formatting to compare two whole sheets?
Yes, with a rule like =A1<>Sheet2!A1 applied to the whole range. It works if both sheets have exactly the same structure and order. If rows were inserted or deleted, a comparison tool will give a much more reliable result.
How do I remove all conditional formatting from a sheet?
Go to Home > Conditional Formatting > Clear Rules > Clear Rules from Entire Sheet. The data is not changed; only the automatic colors disappear.
Conclusion
Conditional formatting turns your spreadsheets into a control system that flags duplicates, gaps, impossible values, hidden spaces and changes between lists. Set up a few rules in your most important files and you will catch errors before they reach a report. For full comparisons between files, complement these rules with the free tools on our site.
Try our free tools
Compare, convert, merge and clean your spreadsheets right in your browser, with nothing to install.
