Excel Performance Problems

How to Measure Excel Worksheet File Size Using VBA and Open XML

John WilsonJohn Wilson Nov 11, 2026 869 views

Question details

The user needs an efficient way to calculate or measure the file size of individual Excel worksheets without using the slow method of copying sheets to temporary workbooks.

How to Measure Excel Worksheet File Size Using VBA and Open XML
Product
Microsoft Excel
Device & OS
not provided
Scenario
Analyzing Excel file sizes to optimize workbook performance and identify large, resource-heavy worksheets.
Observed behavior
Traditional methods involving copying sheets to temporary workbooks are slow and inaccurate because they include unrelated shared workbook components.
Before you start

Ensure your workbook is saved in an Open XML format (.xlsx or .xlsm) before attempting to measure the internal XML file sizes. You will also need to enable the Developer tab if you plan to run VBA macros.

Solution 1Recommended

Inspect the ZIP Package to Measure Worksheet XML Files Manually

Directly analyze the internal XML structure of your XLSX or XLSM file by treating it as a ZIP archive to find the exact storage size of each worksheet component.

XLSX and XLSM files are essentially ZIP containers containing separate XML files for worksheets, relationships, styles, and metadata. By renaming the extension to .zip, you can access the internal structure and view the exact file size of each worksheet's XML file.

This method is much faster and more accurate for finding the storage footprint of specific sheets than copying them to new workbooks.

1
Create a copy of your workbook

Save a duplicate of your target Excel file (.xlsx or .xlsm) to a new folder on your computer to avoid altering the original file.

2
Rename the file extension

Right-click the copied file, select Rename, and change the file extension from .xlsx (or .xlsm) to .zip.

3
Open the ZIP archive

Double-click the newly created .zip file to open it using standard Windows Explorer or your preferred ZIP extraction tool.

4
Navigate to the worksheets folder

Inside the ZIP archive, open the 'xl' folder, and then open the 'worksheets' folder.

5
Check worksheet file sizes

View the file size of 'sheet1.xml', 'sheet2.xml', and other sheet files to determine the exact storage size of individual worksheets.

Inspect the ZIP Package to Measure Worksheet XML Files Manually
Accounting for Shared Components: Remember that the worksheet XML only measures the sheet data. Shared strings, named ranges, styles, and macros are stored in separate XML files within the package.
Free Microsoft Office alternative

Try WPS Office for Your Spreadsheet and VBA Needs

Dealing with large, slow Excel files can be frustrating. WPS Office provides a lightweight, highly compatible alternative to Microsoft Office, featuring robust VBA support and optimized performance for handling complex spreadsheets and Open XML formats.

  1. 1. Download the installer: Visit the official WPS website and download the free WPS Office installer.
  2. 2. Install the software: Run the setup file and follow the on-screen instructions to complete the installation.
  3. 3. Open your workbooks: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm files.
  4. 4. Run your VBA macros: Navigate to the Developer tab to access the VBA editor and execute your spreadsheet analysis macros seamlessly.
Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .csv)Built-in VBA/Macro support for powerful task automation and file system interactionLightweight installation with exceptionally fast loading times for large workbooksFree to use with a familiar tabbed user interface for seamless migration
microsoft office alternative - wps office

Frequently Asked Questions

Why is my Excel file size so large even with very little data?

This often happens due to invisible formatting applied to entire columns, hidden objects, or a bloated 'Shared Strings' table. Inspecting the Open XML ZIP structure can help you identify if a specific worksheet's XML or the styles.xml file is causing the excessive file size.

Does the XML file size exactly match the memory used by the worksheet?

No, the XML file size represents the compressed storage size on your hard drive. When a spreadsheet application loads the file, the XML data is parsed into memory, which typically uses significantly more RAM than the raw file size indicates.

Can I delete heavy sheets directly from the renamed ZIP folder to fix my file?

No, deleting a worksheet XML file directly from the ZIP archive will corrupt the workbook. The core [Content_Types].xml file and relationship files (.rels) will still contain references to the deleted sheet. You should always delete sheets from within the spreadsheet application interface.

Are WPS Spreadsheet macros fully compatible with Excel VBA?

Yes, WPS Office includes advanced VBA support that is highly compatible with Microsoft Excel macros. This allows you to run standard scripts, manipulate workbook structures, and interact with the file system without modifying your existing code.