How to Reduce Excel File Size: Part 2

This article is the second in a series that will help you put some of your Excel files on a diet and get them in tip-top shape.

In the first article of this series, we discussed the concept of Excel’s Used Range, how to find out how large the Used Range is in a particular worksheet, and to see how it affects the file size of each of the individual worksheets in an Excel file.

In this article and others that will follow, we add to the first article and move beyond the size of a worksheet to features of Excel that, through either file longevity, external links, or compatibility issues, can cause a file to increase in size and decrease in speed.

Removing Unneeded Named Ranges

Named Ranges are sticky. Most professionals create Named Ranges one-by-one in their files and, therefore, have control over the number contained in a workbook. However, as workbooks get shared and tabs swapped between files, Named Ranges move with them and can grow and grow (with or without users noticing).

Users adding Named Ranges intentionally is one thing, but they can also be created by external programs and applications. For instance, many popular third party plug-ins can generate hundreds, if not thousands, of Named Ranges in a single file. Compound this with worksheet swapping within a team and you have a recipe for Named Range bloat. 30,000+ of these in one file is not uncommon.

Typically, Named Ranges will not significantly increase file size. Sometimes, however, they get corrupted in some fashion and add significantly to the file size. We recently received a workbook which contained over 2,600 Named Ranges, many of which were hidden and included external links. By removing the 2,500+ that were not used, the file size was successfully decreased from ~8MB to ~1MB.

Step 1:  Many Named Ranges may be hidden and cannot be viewed or removed via the Name Manager (CTRL F3), so the first step is to unhide them. To do so, you can either search for and download an add-in utility, or you can add and run a macro yourself. Here is the code you will need:

Sub Unhide_All_Named_Ranges()

'This macro will unhide all hidden Named Ranges

'Created by The Marquee Group: info@MarqueeGroup.ca

    Dim my_name As Name

    For Each my_name In ActiveWorkbook.Names

my_name.Visible = True

    Next

    MsgBox Format(Names.Count, "#,##0") & " Named Ranges found in file."               

End Sub

Step 2:  The next step is to delete the Named Ranges you want to remove. You can do this by opening up the Name Manager (CTRL F3) and deleting the Named Ranges one at a time, in groups, or all at once.