How to how conditional formatting bloats excel file size — and how to reduce it
- Step 1Run the Weight Analyzer — Upload the
.xlsx. The analyzer measures everyxl/worksheets/sheet*.xmland reports each in the Top 10 and collectively underworksheet_data. - Step 2Spot the disproportionate sheet XML — In the Top 10, compare sheet XML sizes. One sheet at 30 MB while others are 200 KB — especially if it holds modest data — is the smoking gun for formatting bloat.
- Step 3Open Manage Rules for that sheet — In Excel: Home -> Conditional Formatting -> Manage Rules -> set 'Show formatting rules for' to 'This Worksheet'. Look for rules whose 'Applies to' is a whole column ($A:$A) or many fragmented sub-ranges.
- Step 4Tighten or delete the rules — Delete rules applied to entire columns and re-create them against the real data range (e.g.
$A$2:$A$10000). Remove duplicates left by copy-paste. Consolidate fragmented ranges into one. - Step 5If unsure, clear all formats on the sheet — Select all cells -> Home -> Clear -> Clear Formats removes every conditional rule and direct format on the sheet. Reapply only the rules you actually need, scoped to the data range.
- Step 6Save and re-run to confirm — Excel rewrites the sheet XML on save. Re-upload to the analyzer — the bloated sheet XML and the
worksheet_datatotal should drop sharply.
What inflates a worksheet XML — and which analyzer category catches it
Conditional formatting is one of several things stored in the sheet XML. The analyzer can't list rules, but it sizes the file that holds them. Related formatting weight may also appear in styles.
| Cause | Stored in | Analyzer category |
|---|---|---|
Conditional formatting applied to A:A | xl/worksheets/sheet*.xml (rule + huge sqref) | worksheet_data |
| Duplicate rules from copy-paste | Same sheet XML, repeated <conditionalFormatting> blocks | worksheet_data |
| Fragmented rule ranges | Sheet XML with hundreds of tiny sqref ranges | worksheet_data |
| Formatting on millions of empty cells | Sheet XML cell/row metadata far past last data row | worksheet_data |
| Thousands of distinct cell formats | xl/styles.xml (cellXfs explosion) | styles |
| Real cell data (values, formulas) | Sheet XML cell elements | worksheet_data (legitimate) |
| Defined/named ranges | xl/workbook.xml | other |
Sheet XML size vs. data: reading the disproportion
Rough rule of thumb for interpreting the analyzer's per-sheet XML sizes. The disproportion, not the absolute size, is the tell.
| Symptom in the report | Likely cause | Action |
|---|---|---|
| One sheet XML >> all others, modest data | Whole-column or duplicated conditional formatting | Manage Rules -> tighten/delete rules on that sheet |
| All sheet XMLs large, lots of real rows | Genuine data volume | Normal — split sheets or archive if too big |
styles also very large | cellXfs explosion (thousands of unique formats) | Standardise formatting; clear unused styles |
| Sheet XML large, few used cells, last cell far out | Formatting/ghost data past the used range | Ctrl+End to find it; delete those rows/cols; save |
| worksheet_data small overall | Formatting isn't your bloat | Follow the actual top category instead |
Tier limits for the Excel family (real numbers)
The analyzer runs entirely in your browser, but the upload size and row caps are enforced by tier. Weight Analyzer itself requires at least Pro.
| Tier | Max file size | Row cap | Files at once |
|---|---|---|---|
| Free | 5 MB | 10,000 rows | 1 (and Weight Analyzer is gated to Pro+) |
| Pro | 50 MB | 100,000 rows | 5 |
| Pro-media | 200 MB | 500,000 rows | 20 |
| Developer | 500 MB | unlimited rows | unlimited |
Cookbook
Weight Analyzer output focused on per-sheet XML sizes under worksheet_data. Sizes are decompressed KB. The analyzer doesn't list rules — it sizes the sheet file; the fix happens in Excel's Manage Rules.
Whole-column rule turns a small sheet into 30 MB of XML
A tracker with 3,000 data rows had a colour-scale rule applied to entire columns. The analyzer shows one sheet XML hugely out of proportion to its data.
Total workbook size: 4096.0 KB (compressed), 33792.0 KB (decompressed) By category: worksheet_data 32768.4 KB (97%) shared_strings 512.1 KB (2%) styles 511.5 KB (1%) Top 10 largest files: xl/worksheets/sheet1.xml 32640.0 KB xl/worksheets/sheet2.xml 128.0 KB Reading: sheet1 is 250x sheet2 with similar data -> whole-column conditional formatting. Manage Rules -> re-scope $A:$A to $A$2:$A$3001.
Copy-paste duplicated one rule 90 times
Copying a formatted block down repeatedly created 90 near-identical rules. The analyzer can't count them, but the bloated sheet XML points you to Manage Rules where they're visible.
BEFORE worksheet_data 18432.0 KB (94%) xl/worksheets/sheet1.xml 18200.0 KB In Excel: Manage Rules -> This Worksheet -> 90 duplicate 'Cell Value > 0' rules listed. AFTER (delete duplicates, keep one rule on the range) worksheet_data 1536.0 KB (61%) xl/worksheets/sheet1.xml 1320.0 KB
Ghost formatting past the used range
Someone formatted to row 1,048,576. Ctrl+End jumped far below the data. The sheet XML carried metadata for empty rows.
Total workbook size: 2048.0 KB (compressed), 14336.0 KB (decompressed) By category: worksheet_data 13312.0 KB (93%) styles 700.0 KB (5%) shared_strings 324.0 KB (2%) Fix in Excel: Ctrl+End reveals last cell at A1048576. Select all rows below the data -> Delete Rows; same for columns. Save. Re-run -> worksheet_data collapses.
It's styles, not conditional formatting
The sheet XMLs were reasonable, but styles was enormous — thousands of unique cell formats (a cellXfs explosion), a different bloat than conditional formatting. The analyzer separates the two.
Total workbook size: 6144.0 KB (compressed), 21504.0 KB (decompressed) By category: styles 15360.0 KB (71%) worksheet_data 5120.0 KB (24%) shared_strings 1024.0 KB (5%) Top 10 largest files: xl/styles.xml 15360.0 KB Reading: bloat is styles.xml (too many distinct formats), NOT conditional formatting. Standardise formats; copy data to a fresh workbook to reset the style table.
Confirming the fix worked
After clearing excess rules and re-saving, the analyzer's header line and worksheet_data percent are the proof.
BEFORE Total workbook size: 5120.0 KB (compressed), 36864.0 KB (decompressed) worksheet_data 35000.0 KB (95%) AFTER (Manage Rules cleanup + Clear Formats on stray cols) Total workbook size: 768.0 KB (compressed), 2560.0 KB (decompressed) worksheet_data 1843.0 KB (72%) shared_strings 512.0 KB (20%) Net: 36 MB -> 2.5 MB decompressed; 5 MB -> 0.75 MB on disk.
Edge cases and what actually happens
Analyzer doesn't list the actual rules
By designThe Weight Analyzer sizes files, it does not parse conditional-formatting rules. It tells you WHICH sheet XML is bloated (xl/worksheets/sheet3.xml), and you open that sheet's Manage Rules in Excel to see and fix the individual rules.
Whole-column rule ($A:$A)
AvoidableA rule applied to an entire column forces formatting metadata across 1,048,576 rows, swelling the sheet XML even with little data. Re-scope to the real range ($A$2:$A$10000). The disproportionate sheet XML is the analyzer's flag for this.
Copy-paste created duplicate rules
AvoidablePaste copies conditional formatting, so repeated pasting multiplies identical rules into the sheet XML. The analyzer shows the bloated sheet; Manage Rules shows the duplicates. Delete extras and keep one rule per range.
Formatting extends past the used range
AvoidableFormatting empty cells out to row 1,048,576 inflates the sheet XML. Ctrl+End reveals the false 'last cell'; delete the empty formatted rows/columns and save. The analyzer's oversized-XML-vs-data signal catches this too.
Big sheet XML is actually real data
ExpectedA genuinely large dataset legitimately produces a large sheet XML — that's not bloat. Compare against row count: if the size matches the data volume, there's nothing to remove. The analyzer reports size; you judge proportion.
Bloat is in styles.xml, not the sheet
Different causeThousands of distinct cell formats explode xl/styles.xml (the cellXfs table), which the analyzer reports separately as styles. That's related to formatting but distinct from conditional rules. Standardise formats or copy data to a fresh workbook to reset styles.
Clearing formats removes wanted formatting too
CautionHome -> Clear -> Clear Formats wipes ALL formatting (conditional and direct) on the selection — handy as a reset, but you must reapply the rules you want. Scope the clear to stray columns when possible rather than the whole sheet.
Analyzer reports but can't fix
By design (read-only)The tool never modifies the workbook (outputType: report). It diagnoses the bloated sheet; the actual rule cleanup is manual in Excel's Manage Rules. Re-run afterward to confirm the size dropped.
Encrypted workbook
Cannot read (encrypted)Encrypted .xlsx files are OLE/CFB containers, not plain ZIPs — JSZip can't open them, so no per-sheet breakdown appears. Remove the open-password in Excel first, then re-upload.
.xlsb binary workbook
Limited / unsupportedIn .xlsb, sheets are stored as .bin, so the worksheet_data (which matches worksheets/) won't reflect them and most weight lands in other. The per-sheet-XML diagnosis only works for .xlsx; re-save to .xlsx first.
Frequently asked questions
How does conditional formatting bloat a file?
Each rule is stored as XML inside the worksheet file. Rules applied to whole columns, duplicated by copy-paste, or fragmented into many sub-ranges multiply that XML far beyond the data. The result is an xl/worksheets/sheet*.xml much larger than its rows justify.
Can the analyzer list my conditional-formatting rules?
No — it sizes internal files, it doesn't parse rules. What it does is pinpoint the bloated sheet XML in the Top 10 list. You then open that sheet's Manage Rules in Excel to see and clean up the individual rules.
How do I know it's conditional formatting and not data?
Compare the sheet XML size to its row count. A sheet with a few thousand rows but a 30 MB XML is disproportionate — that gap is the fingerprint of formatting bloat. A large XML that matches a genuinely large dataset is just real data.
What's the difference between worksheet_data and styles bloat?
worksheet_data is the per-sheet XML (where conditional-formatting rules and cell data live). styles is xl/styles.xml — the workbook-wide table of distinct cell formats. Thousands of unique formats explode styles; whole-column rules explode worksheet_data. The analyzer separates them.
How do I remove excessive conditional formatting?
Open the flagged sheet -> Home -> Conditional Formatting -> Manage Rules -> 'This Worksheet'. Delete whole-column rules and re-create them against the real data range; remove copy-paste duplicates. Or Clear Formats and reapply only what you need. Then save and re-run the analyzer.
What's a whole-column rule and why is it bad?
A rule whose 'Applies to' is $A:$A covers all 1,048,576 rows, so Excel persists formatting metadata for the entire column. Scope rules to the actual range ($A$2:$A$10000) to keep the sheet XML small.
Why is my sheet XML huge when most cells are empty?
Formatting was applied past the used range — often to the bottom of the sheet. Press Ctrl+End: if it jumps far below your data, delete those empty formatted rows/columns and save. The analyzer's oversized-XML-vs-data signal flags this.
Does Clear Formats fix it?
Yes — Home -> Clear -> Clear Formats removes all conditional and direct formatting on the selection, which resets the bloat. The catch is it removes formatting you want too, so reapply the necessary rules afterward, scoped tightly.
Will the analyzer change my workbook?
No. It's diagnostic only (outputType: report) — no download, no edits. It identifies the bloated sheet; you fix the rules in Excel and re-run to confirm the size dropped.
Why did copy-paste create duplicate rules?
Pasting copies all formatting, including conditional rules. Repeated pasting stacks identical rules, each adding to the sheet XML. After heavy copy-paste, check Manage Rules for duplicates and delete the extras.
What tier and formats are supported?
Pro or above; .xlsx/.xlsm only. CSV has no worksheet XML or formatting to analyse. Pro allows 50 MB uploads, Pro-media 200 MB, Developer 500 MB.
Is my sheet content uploaded for the analysis?
No. JSZip unzips and measures the file in your browser. The values and formatting in your sheets never leave your machine — only the byte sizes of internal files are reported.
Privacy first
Every JAD Excel tool runs entirely in your browser using SheetJS and ExcelJS. Your spreadsheets, formulas, and data never leave your device — verified by zero outbound network requests during processing.