Excel Conditional Formatting Rules: How conditionalFormatting in xl/worksheets/sheet*.xml Exposes Business Thresholds, KPI Targets, Performance Bands, and Reveals What Authors Considered Good or Bad

Where Conditional Formatting Lives Inside an XLSX

Open any XLSX as a ZIP archive and look at xl/worksheets/sheet1.xml. After the cell rows and the data-validation block, before any drawings or extension lists, you will usually find one or more blocks that look like this:

Three things are happening here. The sqref attribute names the cell range the rules apply to. The type attribute selects the rule family — cellIs here, but the schema also supports expression, colorScale, dataBar, iconSet, top10, aboveAverage, duplicateValues, timePeriod, and several others. The dxfId attribute is an index into a differential-format table stored in xl/styles.xml, and the priority attribute decides which rule wins when several apply to the same cell.

The two numbers in the example above are the entire story. Somebody at the organisation decided that a value in column F below 0.18 is bad enough to paint red and a value at or above 0.32 is good enough to paint green. Whether column F currently holds the values 0.21, 0.27, and 0.30 is almost irrelevant: the bands themselves are what the rule encodes. The cells can be deleted, the rows can be sorted, the data can be replaced wholesale; the two thresholds remain in worksheet XML as the author’s judgment about where the line is.

The dxf Table: Where the Colours Actually Live

The dxfId attribute on every cfRule indexes into a table of differential formats stored at xl/styles.xml inside <dxfs>. A differential format is a delta against whatever the cell’s base style was: a font colour, a fill, a border, a number format, or a combination. The table looks like this:

The table is shared. Two rules that both paint a cell green typically share a single dxf entry by index. The colour choices encode their own kind of information: the canonical Excel red FFFFC7CE and green FFC6EFCE are the defaults offered by the conditional-formatting wizard, and seeing those exact hex values tells a reader the workbook was authored interactively in Excel itself rather than written by a third-party generator. Custom hex values tend to come from corporate brand palettes pasted out of marketing decks, so a workbook whose dxf entries use the company’s house red is, in passing, a small fingerprint of where it was made.

The table is also leaky. Like the entries in the shared-strings table, dxf entries are not garbage collected when the rules that referenced them are deleted. A workbook whose visible cells carry no conditional formatting at all can still have a populated <dxfs> block in xl/styles.xml — preserving the entire colour palette of a previous version of the file. The orphan entries are durable evidence that conditional formatting once existed in cells that no longer have it.

Icon Sets: KPI Targets Encoded as Cut Points

The iconSet rule type paints a small symbol next to each cell value — a green check, a yellow exclamation, a red cross, a set of traffic lights, a row of arrows. The cut points between the icons are the policy. The XML stores them like this:

The three cfvo entries (conditional formatting value objects) define the bands. A reader has just learned that values below 0.92 earn a red light, between 0.92 and 1.05 a yellow light, and at or above 1.05 a green light. In context — a column called “Attainment” or “Performance Index” on a sales spreadsheet — the numbers are exactly the bands the organisation uses to decide who is on plan, who is at risk, and who is overperforming. The XML carries the bands explicitly; the visible cell shows only the resulting icon.

Five-icon sets are even more revealing. The five cut points typically decode into a complete performance scale — strong fail, fail, on track, exceed, strong exceed — and the numbers between them are the exact percentiles or thresholds the organisation uses internally. A workbook with a five-arrow icon set on its revenue forecast column is shipping the entire forecast-band methodology to the recipient as four numbers in plain text.

The cfvo type attribute is also informative. num values are absolute numeric thresholds; percent values are positions within the data range; percentile values are statistical positions; formula values reference other cells. Each one tells a different story about how the author thinks about the band: numeric cuts are policy, percentile cuts are statistical fairness, formula cuts indicate a band whose location depends on something else in the workbook.

Colour Scales and Data Bars: The Gradient Layer

colorScale rules paint cells along a colour gradient interpolated between two or three control points, and dataBar rules paint a horizontal bar inside each cell whose length is proportional to the value. Both store their endpoints as cfvo entries, and both leak the author’s sense of what counts as a meaningful range:

The three numbers tell a reader that the column being formatted is expected to range somewhere between a loss of 25,000 and a gain of 75,000, with break-even as the midpoint. The asymmetry is itself information: a colour scale anchored at -25000 and 75000 tells the reader the author considers a 75k upside more typical than a 25k downside, and that break-even matters enough to be the midpoint of the gradient. Replace the column with any other data and the gradient anchors stay put; they describe what the author expected to see, not what the recipient is seeing.

Data bars carry the same kind of metadata in a slightly different form. The two cfvo entries define the minimum and maximum bar lengths; type="num" values fix absolute endpoints, while type="min" and type="max" auto-scale to the data. An auto-scaled bar is the “just visualise it” case and carries little information; a bar with hard-coded endpoints is the case where the author insisted on a specific reference scale and is therefore the more revealing one.

Custom-Formula Rules: Entire Approval Logics in One Element

The most expressive rule type is expression, whose <formula> child accepts any Excel formula that resolves to TRUE or FALSE. Custom-formula rules are the place where authors encode the entire business logic that they wanted to be able to see at a glance:

The reader has just learned that there is a sheet called DiscountFloors in the workbook, that it carries a two-column table of tiers and their corresponding floor discounts, that column G of the active sheet is the actual discount given on a deal, that column D is the customer tier, and that any deal whose discount exceeds the floor for its tier should be painted with whatever the dxfId="5" style is — usually red. The formula is structurally a policy: it is the rule by which the organisation defines an out-of-policy discount. Anyone who reads the XML reads the rule.

Custom-formula rules also frequently encode entire seniority hierarchies. A row painted gold for $E2="Director", silver for $E2="Manager", and bronze for $E2="IC" tells a reader the organisation’s exact role taxonomy without ever showing the actual data. A rule that paints any row whose $P2 contains the substring “Legal Hold” tells a reader the workbook tracks litigation status — even if the column itself has been emptied.

top10, aboveAverage, and timePeriod: Hidden Statistical Cuts

Several rule types encode statistical or temporal cuts whose parameters are themselves revealing. top10 rules flag the top or bottom N values (or percentile); aboveAverage rules flag values one or more standard deviations away from the mean; timePeriod rules flag cells whose date falls in a specific calendar window such as “last 7 days” or “next month”. The XML records the exact parameter:

The top10 rule tells a reader that the author cares about the worst-performing 5% of opportunities specifically — not the bottom decile, not the bottom quartile, exactly the bottom 5%. The aboveAverage rule with stdDev="2" tells a reader the organisation’s anomaly threshold is two sigmas, not three sigmas or one. The timePeriod rule tells a reader the active monitoring window is a rolling week. Each parameter is a small operational choice that the recipient now knows about. None of those choices need to travel with a file going out the door.

The x14 Extension: Cross-Sheet References and Modern Icon Sets

The original OOXML conditional-formatting schema does not let a rule reference cells on another sheet directly. Excel 2010 introduced an extension under the x14 namespace that adds cross-sheet support, custom icon sets, and a handful of other features. When a rule needs the extension, Excel writes a stub <cfRule> in the legacy block and the full rule in an extLst entry at the end of the worksheet:

The extension carries cross-sheet citations that are not available in the legacy block at all. The example above tells a reader there are two sheets called Budget and Actuals, that column D on each carries the line items the rule compares, and that the organisation considers an actual coming in below 90% of budget worth flagging. The two sheet names themselves are organisational vocabulary; the 0.9 is the variance threshold; the column letters fix the data layout. A workbook that has had its Budget sheet deleted but still carries the extension rule is, again, leaving an orphan citation that proves the deleted sheet existed.

The x14 extension is also exactly the kind of element that older or third-party tooling does not always understand. Mismatches between legacy and extension blocks — a stub in the legacy block with no corresponding extension entry, or vice versa — are reliable fingerprints of conversion pipelines, macro-based exporters, and minimal XLSX writers, in the same way we saw with the shared-strings table and the data-validation layer.

Why Document Inspector Never Touches the Layer

Microsoft’s Document Inspector removes core-properties author names, comment threads, hidden sheets, ink annotations, and several other layers we have covered in previous posts. It does not remove conditional formatting. The justification, which is the same as for data validation, is that conditional formatting is functional — the colour-coding is part of how the workbook is meant to look, not part of its history. The user might want the recipient to see the same red/yellow/green bands they see.

That reasoning collapses the moment the workbook is shared externally. A client receiving a project tracker does not need the consultancy’s internal variance threshold; a regulator receiving a return does not need the firm’s anomaly cut-off; a competitor who buys a sample workbook off an unlucky ex-employee does not need the entire discount-floor table encoded as VLOOKUP-driven rules. The colours are useful to the original team and are pure leakage to anyone else. Document Inspector simply does not draw that distinction, so an “inspected” workbook generally still carries every conditional-formatting rule it was authored with, intact, in worksheet XML.

Reading the Layer Without Excel

The whole conditional-formatting layer can be enumerated with a few lines of Python. The simplest approach unzips the XLSX, parses each worksheet XML, and walks the rule elements:

Run on a typical workbook the script prints one line per rule: the sheet, the cell range, the rule type, the operator, and the formulas that define the threshold. Anyone with five minutes and a Python install can extract every cut point and every business-rule formula from a shared workbook without ever opening Excel. The openpyxl library exposes the same information through worksheet.conditional_formatting and resolves dxf indices into the colour and font deltas they describe, which is the right starting point for automated audits.

For ad-hoc inspection the simplest approach is to rename the XLSX to .zip, expand it, and grep for cfRule across the unzipped tree. Every rule in every worksheet, plus every x14-extension entry, is right there in plain UTF-8 XML. The same workflow is what an opportunistic investigator runs the moment a workbook lands in their inbox.

What Investigators Reconstruct From the Layer

In a single sweep through the conditional-formatting layer of a moderately-sized workbook, a reader typically extracts:

• The full set of numeric thresholds an organisation uses to separate good performance from bad, encoded as cellIs rules with lessThan, greaterThan, and between operators.
• The KPI bands behind every icon set — the cut points the author uses to decide who gets the red light versus the green one.
• The asymmetric anchor points of every colour scale, which describe the range of values the author expected to see and where they considered the midpoint to be.
• Hard-coded data-bar endpoints, which encode the reference scales the author insisted on rather than letting Excel pick them automatically.
• Custom-formula approval logics — the discount-floor checks, the budget-vs-actuals comparisons, the litigation-status flags, the seniority-tier colour codes that document the organisation’s actual decision rules.
• Statistical cut parameters from top10, aboveAverage, and timePeriod rules — the bottom-5% review window, the two-sigma anomaly threshold, the rolling-week monitoring scope.
• Cross-sheet citations in x14 extension rules that name budget sheets, target sheets, master-data sheets, and discount-floor tables, including any whose underlying sheets have since been deleted.
• Orphan dxf entries in xl/styles.xml that survive rule deletion and preserve the colour palette of a previous version of the file.
• Tooling fingerprints from mismatches between legacy and x14-extension blocks, which often indicate the workbook passed through a third-party converter or an automated build pipeline.

The first six categories are policy. The last three are organisational fingerprints. Together they typically tell a recipient more about how the originating organisation measures, evaluates, and escalates than the workbook’s visible content does — because the rules describe what the author cares about, which is usually a sharper signal than what they happened to type into the cells.

How to Actually Strip the Layer

Excel exposes a Home > Conditional Formatting > Clear Rules > Entire Sheet command that removes rules from the active sheet, but it does not touch other sheets, does not remove orphan dxf entries from xl/styles.xml, and does not touch the x14 extension block. The reliable path is to operate at the XML level. The minimum cleanup pass for a workbook bound for external release is:

The script removes every <conditionalFormatting> block, every x14 extension entry that contains conditional formatting, and the entire <dxfs> table from the styles part. Excel opens the resulting file without complaint and shows no coloured bands, icons, or data bars. There is no functional behaviour that has to be preserved for a workbook being shipped externally; the value of red/yellow/green colour-coding in someone else’s copy is essentially zero, and the cost of leaving the rules in place is the entire leak surface this post has described.

A more conservative variant keeps rules whose type is something like duplicateValues — the “highlight duplicates” case carries little policy — and strips everything that encodes a numeric threshold, an icon set cut, a colour-scale anchor, or a custom formula. The split is a one-line filter on rule.get("type"). For most external shares the aggressive variant is correct: colour-coding is an authoring convenience that recipients do not need.

Conditional Formatting as Part of a Coordinated Cleanup

Stripping conditional formatting in isolation is necessary but not sufficient. The layer interlocks with several others that have to be touched in the same pass. The defined-names symbol table frequently carries references that were used only by cf rules; those names should be deleted at the same time, both because they are still readable on their own and because orphan names raise no warnings inside Excel itself. Data-validation rules often share cfvo-style threshold values with conditional-formatting rules, and the two layers should be stripped together to avoid telling the recipient through validation what the cleaned cf rules used to say.

The same audit pass should look at the calculation chain, the shared-strings table, the external-links table, the pivot caches, and the Power Query connections, all of which can carry references that the conditional-formatting cleanup just orphaned. The right mental model is that conditional formatting is one of perhaps a dozen metadata layers that share references and identifiers with each other; stripping any one of them without coordinating the others leaves a workbook that has visibly been edited rather than a workbook that is genuinely clean.

The Bands Are the Judgment

Conditional formatting looks like aesthetics and ends up acting like documentation of how the organisation thinks. Every threshold captures a number that somebody considered worth defending. Every icon-set cut captures the line between adequate and inadequate performance. Every colour-scale anchor describes the range of values the author expected to see. Every custom formula encodes an approval rule, a comparison, or a flag that the team uses to decide whether a row deserves attention. The XML stores all of that verbatim, side by side with the cell data, and treats none of it as metadata to be redacted on the way out.

For a recipient of the file the layer is not noise. It is a compact, machine-readable description of the originating organisation’s standards, thresholds, escalation rules, and analytical conventions — written by the people best placed to know all of those things, and shipped without modification. The single most effective change a team can make to its pre-share workflow is to treat conditional formatting as content rather than chrome: review every rule the way they review every cell, and strip the rules that the recipient does not need to see.

For workbooks staying inside an organisation, conditional formatting is exactly what it was designed to be: a small, helpful visual layer that helps a reader find the cells that matter. For workbooks crossing the perimeter, it is one of the most concentrated pieces of organisational judgment the file is ever going to carry. The only durable defence is to rewrite the layer as part of a coordinated cleanup pass that touches the cf rules, the dxf entries they reference, the defined names they relied on, and the master-data sheets they pointed at — leaving recipients with the values the author intended to disclose and none of the bands, cut points, or approval rules they thought no one would ever see.