Reduce the file size of Excel [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

This question does not appear to be about a specific programming problem, a software algorithm, or software tools primarily used by programmers. If you believe the question would be on-topic on another Stack Exchange site, you can leave a comment to explain where the question may be able to be answered.

Closed 8 years ago.

开发者_开发百科 Improve this question

I'm working in an excel application and providing a menu to the user to add a new worksheet in that excel application (Excel Workbook).

The worksheet will be added once the user clicks the "OK" button and I'm using a template to add this worksheet (The template has lot of formatting and formulas in it)

Lets say the file size is 10 MB after adding a worksheet if the workbook is saved. Then I close the Excel application and reopen it and save the file then the file size is getting reduced to 8 MB.

Can anybody let me know what could be the reason for the same?

---

Unused formatting is the most common cause of 'file bloat' in Excel: a close second is that the 'used range' on a sheet is larger than you think - Excel maintains metadata about every cell on a sheet from A1 to the lowest, furthest cell you've ever edited and that takes a lot of memory.

But the 'used range' in a new worksheet is saved as cell A1 to the last cell with data in it.

A hint: take a look at the sliders in the scroll bars for your worksheet, vertical and horizontal: are they chunky grey blocks that look as if they'll only scroll fifty rows or columns? Or are they little grey dashes that scroll hundreds of rows if the mouse goes near them? When a slider bar moves a distance equal it's own length, it completes one 'page down' (or page sideways) in the available page space; the larger the space or page set (or used range in Excel), the smaller they will be.

I'd suggest de-bloating (There's got to be a better word! Deflating? Detumescing? Deforming?) your workbook. There used to be lots of third-party tools for this but Excel 2003 onwards has a built-in workbook rebuilder: just click 'Save' and 'Save As...' a web page. Then exit Excel, reopen Excel, and use 'Open' to open the html workbook (despite being an HTML file, it'll have an Excel icon), and 'Save As...' an xls file again. Close Excel again, and have a look at the file size.

---

You can clear all columns and rows after the last cells realy used to reset the last cells ( [Ctrl]+[End] ). You can also enable the compression of images.

I dev an AddIns with auto install (in french) to include in context menu 3 new buttons:

1. Optimize
2. Optimize and Save
3. Toggle Case

This is based on KB of Microsoft office 2003 with personals improvement :

1. add compression of images
2. fix bug for Columns
3. feat compatibility with excel 2007 - 2010 - ...

SOLUTION > you can download my *.xla file here

---

Nile is correct, the "UsedRange" does not correctly get reset causing a lot of bloat. You can reset this very simply with 1 line of VBA code.

ActiveSheet.UsedRange

Open the Developer tab in Excel, insert a Module, add the following macro, and run it

Public Sub ResetUsedRange()
    ActiveSheet.UsedRange
End Sub

You could also tie this macro to an Excel keyboard shortcut for quicker access using the Application.OnKey event

---

You can use save as macro enabled file to reduce it to optimal memory capacity. I used it to reduce the size of around 10 MB to 4.5 MB. it helped me a lot in solving my problem.