Delete unused formats

Location on ribbon: Tab DVD Addin → Group Cells and Rows

Command ID: btnDeleteUnusedFormats

What it does

Clears the formatting from everything outside the real data area of the active sheet, then forces Excel to recompute its used range. This is the classic cure for a bloated workbook: a quantity sheet with a few hundred rows that weighs tens of megabytes, where Ctrl+End jumps to row 60000, the scrollbar is a sliver, the file opens slowly and printing produces dozens of blank pages. The usual cause is someone shading, bordering or format-painting whole rows and columns.

How to use

1. Open the sheet you want to clean (the command works on the active sheet only and needs no selection).
2. Go to tab DVD Addin → group Cells and Rows → click Delete unused formats.
3. The add-in finds the last cell that really holds data and compares it with Excel's Ctrl+End last cell.
4. Every row below the last data row and every column right of the last data column has its formats cleared and its fill removed.
5. Excel is asked to recompute the used range, and the status bar reports something like Cleaned formats outside the data area (last: R320C11).
6. Save and reopen the file to see the size drop and Ctrl+End land on the correct cell.

Example

Sheet TONGHOP_KL has real data up to K320, but Ctrl+End jumps to BX65000 because someone shaded entire columns yellow and bordered them to the bottom of the sheet. The file is 42 MB, scrolling stutters, and printing adds 28 blank pages.

Click Delete unused formats → the status bar reports (last: R320C11) → save and reopen: Ctrl+End stops at K320, the file is down to roughly 3 MB, and the printout is exactly the pages that have content.

Notes

• It runs on the active sheet only; repeat it per sheet in a multi-sheet workbook.
• The operation cannot be undone with Ctrl+Z — formatting you deliberately prepared (borders, fills) for rows not yet filled in will be lost.
• File size usually shrinks only after saving and reopening; checking immediately may show no difference.
• Only formatting is removed — data and formulas inside the data area are untouched.
• If a stray cell far down holds an empty string or a formula returning "", Excel still counts it as data, so less gets cleaned than expected — delete those cells first and run again.

Related

• Delete cell styles — purge the junk Cell Styles that pile up in copied workbooks.
• Delete error names — remove broken or hidden defined names, another cause of file bloat.
• Delete blanks — compact the table before cleaning the formatting.
• Get sheet print settings — re-check the print area after cleaning.