How to Measure Excel Worksheet File Size Using VBA and Open XML
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.

- 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.
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.
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.
Save a duplicate of your target Excel file (.xlsx or .xlsm) to a new folder on your computer to avoid altering the original file.
Right-click the copied file, select Rename, and change the file extension from .xlsx (or .xlsm) to .zip.
Double-click the newly created .zip file to open it using standard Windows Explorer or your preferred ZIP extraction tool.
Inside the ZIP archive, open the 'xl' folder, and then open the 'worksheets' folder.
View the file size of 'sheet1.xml', 'sheet2.xml', and other sheet files to determine the exact storage size of individual worksheets.

Use a VBA Script to Automate Open XML Size Extraction
Write a VBA macro that programmatically interacts with the file system's ZIP namespace to extract and measure the XML files without manual renaming.
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. Download the installer: Visit the official WPS website and download the free WPS Office installer.
- 2. Install the software: Run the setup file and follow the on-screen instructions to complete the installation.
- 3. Open your workbooks: Launch WPS Spreadsheet and open your existing .xlsx or .xlsm files.
- 4. Run your VBA macros: Navigate to the Developer tab to access the VBA editor and execute your spreadsheet analysis macros seamlessly.

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.




