How to reduce file size and speed it up

Contents

If at one point you realize that your main work file in Excel has swollen to several tens of megabytes and you can safely pour yourself coffee while opening the file, then try going over the points described below – perhaps one or more of them will shorten your “overgrowth ”to a sane size and disperse its “brakes” 🙂

Issue 1: The used sheet range is larger than needed

If your table is 5 by 5 cells, this does not mean that Excel remembers only 25 data cells when saving this file. If you have used any cells on this sheet in the past, they are automatically included in the used range (the so-called Used Range), which is remembered when you save the book. The problem is that when clearing used cells, Excel does not always automatically exclude them from the used range, i.e. begins to store more data in the file than is actually available.

It is easy to check this – press the key combination on the keyboard Ctrl + End and see where the active cell moves to. If it jumps to the actual last data cell on the sheet, great. And if it suddenly jumps much to the right and / or lower “into the void”, it’s bad: Excel also remembers all these unnecessary empty cells inside the file.

It is treated, however, quite easily:

Highlight the first empty row below your table
Press the keyboard shortcut Ctrl+Shift+Down Arrow – all empty lines to the end of the sheet will be selected.
Delete them by pressing on the keyboard Ctrl+minus sign or by selecting on the tab Home – Delete – Delete rows from sheet (Home – Delete – Delete rows).
Do the same with columns.
Repeat all the above procedures on each sheet, where when you click on Ctrl + End the active cell does not move to the actual last cell with data, but “into the void” below and/or to the right.
Save the file (mandatory, otherwise the changes will not take effect!)

If your book has a lot of such sheets, then it is probably easier to use a short macro.

Issue 2: The old XLS format is used instead of the new XLSX, XLSM, and XLSB

For many years and versions in a row since the early nineties, Excel has had one file format – XLS. This, of course, removed compatibility problems, but, in itself, this format was outdated long ago and had many unpleasant drawbacks (large size, opacity of the internal data structure, easy damage, etc.)

Starting with Excel 2007, Microsoft has introduced new file saving formats that make life and your files much easier:

XLSX is essentially zipped XML. The size of files in this format compared to Excel 2003 is smaller, on average, 5-7 times.
XLSM is the same, but with macro support.
XLSB is a binary format, i.e. in fact – something like compiled XML. Usually 1.5-2 times less than XLSX. The only negative: there is no compatibility with other applications except Excel, but the size is minimal.

Conclusion: whenever and wherever possible, switch from the old XLS format (perhaps inherited from previous employees) to new formats.

Problem 3: Over-formatting

Complex multi-color formatting, of course, negatively affects the size of your file. And conditional formatting also significantly slows down its work, because. makes Excel recalculate the conditions and update the formatting on every sneeze.

Leave only the essentials, don’t exaggerate. Especially in those tables that no one else sees except you. To delete only formats (without losing content!) select the cells and select from the drop-down list Clear – Clear formats (Clear — Clear Formats) tab Home (Home):

Especially “load” the file formatted whole rows and columns. Because the size of the sheet in the latest versions of Excel is greatly increased (> 1 million rows and > 16 thousand columns), then a lot of resources are needed to remember and process such formatting. In Excel 2013-2016, by the way, there was an add-in Inquire, which contains a tool for quickly getting rid of such excesses – the button Remove redundant formatting (Clean Excess Cell Formatting):

It instantly removes all unnecessary formatting to the end of the sheet, leaving it only inside your tables and without damaging your data in any way. Moreover, it can do this even for all sheets of the book at once.

If you do not see the tab in your interface Inquire, then it must be connected on the tab Developer – COM Add-Ins (Developer — COM Addins).

Problem 4. Unnecessary macros and forms in VBA

Large Visual Basic macros, and especially userforms with embedded graphics, can add a lot of weight to your book. For removing:

click Alt + F11to enter the Visual Basic Editor
find the Project Explorer window (if it is not visible, then select from the menu View — Project Explorer)
remove all modules and all forms (right click – remove – further in the window with a question about export before deleting – No):

Also, the code can be contained in sheet modules – check them too. You can also just save the file in XLSX format without macro support – all macros and forms will die automatically. You can also use the Clear Macro Book tool from the PLEX add-on.

Problem 5: Named Ranges

If your file uses named ranges (especially with formulas, dynamic or filtered), then it makes sense to abandon them in favor of saving the size of the book. You can view the list of available ranges by clicking Ctrl + F3 or opening a window Name Manager (Name Manager) tab formula (Formulas):

Also clean up named ranges with errors (they can be quickly selected using the button Filter in the upper right corner of this window) – they will definitely not be useful to you.

Issue 6: High-Resolution Photos and Invisible AutoShapes

If we are talking about photos added to the book (especially when there are a lot of them, for example, in the product catalog), then they, by themselves, increase the file size. I advise you to compress them, reducing the resolution to 96-150 dpi. On the screen in terms of quality, this is not felt at all, and the file size is reduced significantly. Use the button to compress Compress drawings (Compress pictures) tab Framework (Format):

In addition to visible pictures, the sheet may also contain invisible images (drawings, photographs, autoshapes). To see them, select any picture and on the tab Framework (Format) click Selection area (Selection Pane).

To delete all graphic objects on the current sheet, you can use a simple macro:

Sub Delete_All_Pictures() 'macro to delete all pictures on the current sheet Dim objPic As Shape For Each objPic In ActiveSheet.Shapes objPic.Delete Next objPic End Sub

Problem 7. Initial data of pivot tables

By default, Excel saves data for calculating the pivot table (pivot cache) inside the file. You can opt out of this feature, significantly reducing the file size, but increasing the time to update the summary the next time you open the workbook. Right click on the pivot table and select command Table properties (Pivot Table Properties) – tab Data (Date) – uncheck the box Save original data with file (Save source data with file):

If you have several pivot tables based on the same data range, then the method when all pivot tables after the first one is built on the basis of the cache already created for the first table helps to reduce the file size. In Excel 2000-2003 this is done by selecting the radio button in the first step PivotTable Wizards when building:

In Excel 2007-2016 button PivotTable Wizards you need to add it to the panel manually – there is no such command on the ribbon. To do this, right-click on the Quick Access Toolbar and select Customizing the Quick Access Toolbar (Customize Quick Access Toolbar) and then find the button in the full list of commands PivotTable Wizard (PivotTable and PivotChart Wizard):

Problem 8. Changelog (logs) in a shared file

If your file has tab sharing enabled Review – Book Access (Review — Share Workbook), then inside your Excel file on a special hidden sheet, the entire history of document changes begins to be saved: who, when and how changed the cells of all sheets. By default, such a log stores change data for the last 30 days, i.e. when actively working with a file, it can easily take up several megabytes.

Moral: don’t share unnecessarily, or reduce the number of days your log data is kept by using the second tab Details (Advanced) in the window Book access. There you can find the option Keep change log for N days (Keep change history for N days) or turn it off completely:

Problem 9. Lots of junk styles

I already wrote about this dirty trick in detail earlier in the article on how to defeat the error “Too many cell formats”. The bottom line, in short, is that if you expand on the tab Home list Cell styles (Home — Cell Styles) and you see a lot of incomprehensible and unnecessary styles there, then this is bad – both for the size of your Excel file and for its speed.

You can remove unwanted styles using a macro or a ready-made command from the PLEX add-on.

Problem 10. Lots of notes

Notes to cells, of course, not the most harmful moment from all listed. But some files may contain a lot of text or even pictures in the cell notes. If the notes do not contain information that is useful to you, then they can be easily deleted using the command on the tab Home — Clear – Clear Notes (Home — Clear — Clear Comments).