Conditional formatting is one of those features that people set up once and rely on forever. Color-coded status columns, threshold highlights, traffic-light dashboards — when they stop working after a file conversion, it can take hours to diagnose. Most conditional formatting migrates from XLS to XLSX without issue. But "most" is not "all," and the rules that do break tend to be the ones users notice immediately.
Here's what actually happens during XLS to XLSX conversion, which rule types are safe, which need verification, and how to fix the ones that break.
The XLS format (Excel 97–2003) limits each cell to 3 conditional formatting conditions. The XLSX format raises that ceiling to 64 rules per cell. This is one of many structural differences between the two formats — for a broader overview of what changed between XLS and XLSX, see our comparison guide. This matters for conversion because any workarounds you built to stay within the XLS limit — layered rules, helper columns, creative formula gymnastics — are all preserved in XLSX exactly as-is. They'll still work, but you now have room to clean them up if you want.
The more interesting issue runs in the other direction: when the conversion engine has to interpret how your 3 XLS conditions map to the XLSX rule model, it sometimes makes choices that subtly change behavior.
Rules that compare a cell's value to a constant (greater than 100, equal to "Complete", between 0 and 1) migrate without issues. The condition logic is identical between formats, and the fill/font/border formatting is fully preserved.
Rules that use "Use a formula to determine which cells to format" generally migrate intact. The formula text is preserved as-is. The main risk is reference type — see the section below on formula rules specifically.
The standard highlight presets — duplicate values, top/bottom 10%, above/below average — all map directly to XLSX equivalents and migrate cleanly.
This is where most post-conversion conditional formatting problems originate. A formula-based conditional formatting rule is always relative to the top-left cell of the range it's applied to. If your rule applies to A2:A100 and uses the formula =$B2>100, the $B is absolute (column B always) and the 2 is relative (moves with each row).
After conversion, open the Conditional Formatting Manager (Home > Conditional Formatting > Manage Rules) and click Edit Rule on each formula rule. Check that the references are exactly what you intended — particularly mixed references like $A2 or A$1. The conversion itself rarely changes these, but it's worth confirming.
Color scales (the gradient fills showing low-to-high values) can shift slightly after conversion. XLS stored color scale thresholds differently than XLSX, and the conversion has to translate between the two threshold models. The gradient will still appear, but the breakpoints — the cell values at which the color transitions — may move slightly.
To verify: open the original .xls and the converted .xlsx side by side, select the same range, and compare the color distribution on a few representative rows. If the gradient looks the same visually, you're fine.
Same situation as color scales. XLSX supports more icon set types than XLS, so the conversion maps your original icon set to the closest XLSX equivalent. The icons themselves should be the same or very similar, but check threshold percentages in the rule editor after conversion.
In the XLS model, when multiple conditions applied to the same cell all evaluated to true, Excel used the first matching rule. XLSX uses the same first-match logic, but the rule order can occasionally shift during conversion if you had multiple overlapping rules. Check the order in the Manage Rules dialog — rules are listed top-to-bottom in priority order, and you can drag to reorder them.
XLS had a practical limit on how many cells could be included in a conditional formatting range. If you applied a rule to an entire column (e.g., column A from row 1 to 65,536 — the XLS row limit), the conversion to XLSX may split this into multiple rules or truncate the range. XLSX supports up to 1,048,576 rows, but the rule definitions are stored differently. Check that rules applied to entire columns still cover the full intended range.
Conditional formatting formulas that reference another file (e.g., ='[Budget.xls]Sheet1'!$A$1>0) may break if the external file path changed or if the external file also needs to be converted to XLSX. These are rare, but if the original was built with cross-workbook conditional formatting, verify those rules specifically.
LegacyLeaps converts your .xls files to .xlsx while preserving formulas, macros, and formatting — including conditional formatting rules. Free scan shows you exactly what's inside before conversion.
Try the Free Scan$A2, A$1).| Feature | XLS (97–2003) | XLSX (2007+) | Migration risk |
|---|---|---|---|
| Max rules per cell | 3 | 64 | None — XLSX is more flexible |
| Cell value rules | Yes | Yes | Low — migrates cleanly |
| Formula-based rules | Yes | Yes | Low — check reference types |
| Color scales | Not supported | Full support | Medium — thresholds may shift |
| Icon sets | Not supported | More types | Medium — verify icons post-conversion |
| Data bars | Basic | Full customization | Low |
| Rule priority | First match | First match | Low — verify order if overlapping |
Most conditional formatting survives intact. Basic cell value rules migrate cleanly. Formula-based rules migrate but need reference verification. Color scales and icon sets are the most likely to shift because the threshold models differ between formats. A post-conversion audit takes 10–15 minutes and is worth doing for any workbook where conditional formatting is business-critical.
The most common causes are: rule priority order changed, color scale thresholds shifted, or a formula reference type changed. Open Home > Conditional Formatting > Manage Rules and compare each rule against the original .xls to identify what's different.
The most likely culprit is a reference type change. Formula-based rules use the top-left cell of the applied range as the anchor. Check that mixed references like $A2 are still mixed (not changed to $A$2) by opening the rule editor and reviewing the formula.
XLS files are limited to 3 conditional formatting conditions per cell. XLSX files allow up to 64 rules per cell. If you built workarounds in XLS to stay within 3 conditions, those workarounds will still work after conversion — but you now have room to simplify them.
Practical fixes for legacy Excel and Access problems. No spam.