OmniDocExcel Conditional Formatting Not Working: Causes and Fixes
Conditional formatting not applying in Excel? Learn the common causes, including range, rule order, formula references and text numbers, and how to fix them.
Excel conditional formatting is a set of rules that color cells when their values meet a condition. When it stops working, the rule often looks correct in the rules manager, which makes the problem hard to spot. Most readers searching for this see one of two things: some cells highlight while others don't, or a rule that appears to do nothing at all.
This guide skips how to create rules. Instead, it focuses on why existing rules stop applying. The causes fall into a few groups: the rule covers the wrong cells, rules conflict with each other, formulas point the wrong way, or numbers are stored as text. You'll get a table to match your symptom to a cause, then fixes you can work through in order.
Why is Excel conditional formatting not working? The short answer
Conditional formatting usually fails for one of four reasons: the range is too small, another rule wins, the formula references shift, or the data is text instead of numbers. Check them in that order.
| Your situation | What to do | Why |
|---|---|---|
| Only some rows get highlighted | Extend the Applies to range in the rules manager | A rule colors only the cells inside its range |
| The rule is correct but the color never appears | Check rule order and Stop If True | A higher rule that sets the same format property wins |
| A formula rule works in one row but not others | Anchor columns with dollar signs | Relative references shift with each row |
| "Greater than" rules ignore some numbers | Convert text-stored numbers to real numbers | Text is compared differently than numbers |
| Rules disappeared after copying or converting | Compare with the original and reapply | Pastes and file conversions can change or drop rules |
Figure out which problem you have
Use this table to match what you see with a likely cause before you change anything.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| Highlighting stops partway down a column | The Applies to range is too short | Open the rules manager and read the range each rule covers |
| The rule looks right, but no color shows | An earlier rule sets the same fill or font color | List the rules in order and see which one sets that color |
| A formula rule is correct in the first row only | Relative references are drifting | Select a cell in the second row and check whether each reference moved as you intended |
| Numbers look right, but comparisons skip them | The numbers are stored as text | Select the range and check the status bar; if no sum appears, the values are probably text |
| Colors update late or not at all | Calculation is set to manual | Check the workbook's calculation setting, then recalculate and see if the colors change |
| Rules are missing after a conversion or translation | The file round trip changed formatting | Open the original workbook and compare the same range side by side |
Fix it step by step
- Check the Applies to range. Open the rules manager for the sheet and read the range each rule covers. Extend it to every row that should be colored. Watch for ranges that stop at a blank row or that cover only the first block of a larger table.
- Check rule order and Stop If True. Excel evaluates rules from top to bottom. If an earlier rule sets the same fill or font color, it wins. Move the rule you need higher in the list. If a rule has Stop If True turned on and it matches, rules below it do not run. Turn that option off if a lower rule should still apply.
- Anchor formula references. A formula rule is written for the top-left cell of its Applies to range, and every other cell shifts from there. For a rule on B2:B200 that compares column C in the same row, write
=$C2>100. The dollar sign locks the column, and the row number moves down. Also make sure the formula returns TRUE or FALSE. An error such as #N/A makes the rule skip the cell silently, so wrap the test in IFERROR with FALSE as the fallback. - Convert text-stored numbers. Numbers that are stored as text often sit left-aligned and may show a small warning marker in the corner. Select the column and convert the values to numbers using Excel's text-to-number tools. If the data is messy, add a helper column with a formula such as
=VALUE(A2), check the results, and then paste them back as values. - Set calculation to automatic. If the workbook uses manual calculation, rules may not refresh when data changes. Switch the calculation setting to automatic, then recalculate the sheet and check the colors again.
- Check where the file has been. If you pasted cells into the range, use a paste option that carries only formats, or the pasted cells may duplicate or override rules. If the file was converted or translated, open the original and compare the same cells. OmniDoc's document translation keeps layout, tables, formulas and pictures, but don't assume every workbook setting carries over. Check the output against the original before you rely on it.
Prevent it next time
- Keep a short rule list. Note each rule, its Applies to range and its color meaning in a notes tab. This makes conflicts visible before they cause confusion.
- Use Excel tables for growing data. When a range is part of a table, new rows usually join the table, and the formatting follows them.
- Use one rule per meaning. Stacking several rules that set the same color makes the winner hard to predict.
- Anchor formulas from the start. Adding
$to the column in formula rules takes seconds and prevents most reference drift later. - Check imported numbers. After pasting data from another system, confirm that numbers align to the right. Left-aligned numbers are a sign that they are stored as text.
- Keep an untouched copy. Save the original before you convert or translate a workbook, so you can compare results.
❓ FAQ
1. Why does conditional formatting work on one sheet but not another?
Each sheet has its own rules and ranges, so a rule copied from one sheet may point at the wrong cells. Check the range and the data type on the sheet that fails, then compare its rules with the working sheet.
2. Can a conditional formatting formula reference another sheet?
Newer versions of Excel accept references to other sheets in rule formulas. In older versions, define a named range and use that name in the rule instead.
3. Will translating my spreadsheet keep its conditional formatting?
OmniDoc's document translation keeps layout, tables, formulas and pictures, but this article does not confirm how every rule carries over. Open the output, compare a few highlighted cells with the original, and fix any gaps in the source file.
4. Why does a rule color blank cells?
Blank cells count as zero in numeric comparisons, so "less than" rules often catch them. Add a check for content, such as =AND($C2<>"",$C2<100), so the rule applies only to cells with values.
📝 Summary
Excel conditional formatting not working usually comes down to the range, rule order, formula references or text-stored numbers. Check those four areas in that order, and you will usually find the cause. When a file has passed through a conversion or translation, compare it with the original before you rely on the colors.
