10 Effective Ways to Make Your Excel Pivot Table File Smaller
Excel pivot table files can sometimes become very large, making them difficult to share or store. Fortunately, there are several ways to reduce the file size of an Excel workbook containing pivot tables. By optimizing your pivot table setup, data source, and Excel file, you can significantly shrink the file size while still preserving your pivot table functionality. In this article, we will provide a step-by-step guide on how to make your Excel pivot table files smaller.
Optimize Your Pivot Table Setup and Design
The first area to look at when trying to reduce pivot table file size is the design and configuration of the pivot tables themselves. By thoughtfully managing your pivot fields, calculated items, and overall table layout, you can minimize the amount of behind-the-scenes data needed to support your pivot tables.
1) Remove Unnecessary Pivot Fields
Every field that you add to a pivot table, whether in the Filters, Columns, Rows, or Values area, increases the size of the pivot cache that Excel has to maintain behind the scenes. So one of the most effective ways to reduce file size is to ruthlessly remove any pivot fields that aren’t actually needed for your analysis.
- Go through each field in the PivotTable Fields pane and remove fields that you don’t need by unchecking them or dragging them out of the pivot table areas.
- Be especially mindful of removing fields with a large number of unique items, like IDs or timestamps, as these bloat file size the most.
- If there are fields that you sometimes use but don’t always need, consider moving them to the Report Filter area instead of Rows or Columns so they are more easily toggled on and off.
- For fields that have a relatively small number of unique items, using Slicers instead of Report Filters can provide easier filtering while keeping file size down.
2) Minimize Usage of Calculated Fields and Items
Pivot tables allow you to define your own custom calculations as calculated fields and calculated items. While these can be very handy for complex analysis, they also have a cost in terms of file size, especially if you have a lot of them or if they use complicated formulas.
- Review your pivot tables to find any calculated fields or items that you’ve created but are no longer using, and delete them.
- For calculated fields and items that you do need, see if you can simplify their formulas at all. Break complicated formulas into smaller steps to aid troubleshooting.
- Avoid referencing entire columns in calculated field formulas, like
=Sales*Table1[Price], as this will cause Excel to add a lot of data to the pivot cache. Instead, reference only the cells needed for the calculation.
3) Consolidate and Reuse Pivot Tables
Every unique pivot table in a workbook requires its own pivot cache, which can rapidly inflate file size. So rather than creating many separate pivot tables, look for opportunities to reuse the same pivot table for multiple purposes.
- If you have two pivot tables based on the same data source, investigate combining them into a single pivot table, using filters or slicers to display different views as needed.
- When creating Pivot Charts, use the PivotChart Fields pane to base the chart on an existing pivot table rather than creating a new one.
- Use Slicers to interactively filter multiple pivot tables at the same time so you don’t need separate tables for different filtered views of the same data.
Optimize Your Source Data Setup
The way you organize the source data that feeds into your pivot tables has a huge effect on the resulting file size. By structuring your source data intelligently, you can help Excel store your data much more efficiently.
1) Use Excel Tables or Named Ranges
When you create a pivot table from a normal range, Excel has to maintain a separate copy of all of that data in a special format called the pivot cache, which can significantly increase file size. But if your source data is in an Excel Table or Named Range, Excel can optimize its caching to reduce file bloat.
- If your source data is in a normal range, convert it to an official Excel Table by selecting any cell in the range and pressing Ctrl+T or going to Insert > Table.
- Alternatively, use the Name Box or Formulas > Define Name to define a Named Range encompassing your source data.
- Then base your pivot table on the Table or Named Range for a leaner file size vs. a normal range.
2) Remove Unneeded Data Rows and Columns
Pivot tables will include every row and column from your source data range in the pivot cache, even if you don’t actually use most of that data in your pivot tables. So you can make your file size a lot smaller by removing data that isn’t needed for your analysis.
- Review your source data and delete any columns that you don’t need in your pivot table, being mindful that deleting columns from your source will also remove them from the pivot table.
- Filter your source data to identify any rows that aren’t relevant to your analysis, like old historical data, and delete those rows permanently to reduce file size.
- Move your pivot table source data to a separate worksheet from other data so you can make your range selection more precise to what the pivot table actually needs. Avoid selecting entire columns/rows.
3) Split Data Across Multiple Tables or Files
If you’re dealing with an extremely large data set, you may need to split your source data into separate tables or even separate file links to keep any single file from getting unmanageably large.
- Look for logical ways to categorize your data into separate tables by time period, geography, product line, etc.
- Create multiple pivot tables that each draw from a different data table, and use slicers or filters to navigate between them.
- If you need to combine data from multiple tables on the fly, look into using Microsoft Query, Power Query, or Power Pivot to link data sources.
Optimize Overall Excel File Structure
Beyond optimizing your pivot tables and source data, there are also some changes you can make to your overall Excel file to help keep file size down.
1) Save as Binary Workbook
The modern Excel file format (.xlsx) is optimized for compatibility, but you can often get a smaller file by saving in the legacy binary workbook (.xlsb) format instead.
- Go to File > Save As and select Excel Binary Workbook (*.xlsb) from the “Save as type” dropdown.
- Be aware that this older file format cannot be opened in versions of Excel prior to 2007.
2) Compress Embedded Photos and Graphics
Any photos, logos, icons, SmartArt or other graphic objects that you’ve added to your workbook can dramatically inflate file size, but you can optimize them to make your file smaller.
- Right-click a graphic and select Format Picture or Format Shape.
- In the Format pane, select the Compress Pictures or Resize options to apply compression and downsizing to the selected graphic or all graphics in the workbook.
3) Avoid Overusing Custom Styles and Formatting
Whenever you apply custom cell formatting in your workbook, like custom number formats, fonts, fills, and borders, Excel has to store extra information in the file. Multiply this by thousands of cells in a complex pivot table and it can notably increase file size.
- On the Home tab, expand the Cell Styles gallery and right-click to delete any custom styles you’ve created but aren’t using.
- Select your entire pivot table, go to the Design tab under PivotTable Tools, and click the Clear dropdown to reset some or all of the custom formatting to default.
4) Disable Saving Change History
Excel includes an Auto Recover feature that automatically saves the last few versions of your workbook inside the file in case you need to restore unsaved changes. This is convenient but noticeably increases file size.
- Go to File > Options > Advanced and scroll down to the “Data” section.
- Uncheck the option for “Keep the last autosaved version if I close without saving”.
- Click OK and manually save your file to commit the change in settings.
| Technique | Area | Benefit |
|---|---|---|
| Remove unnecessary pivot fields | Pivot Table Setup | Eliminates unneeded data from the pivot cache |
| Minimize calculated fields/items | Pivot Table Setup | Simplifies behind-the-scenes pivot table structure |
| Consolidate pivot tables | Pivot Table Setup | Reduces the number of separate pivot caches |
| Use Tables or Named Ranges | Source Data | Enables more efficient pivot caching |
| Remove extra rows and columns | Source Data | Avoids caching unused source data |
| Split data across tables/files | Source Data | Prevents individual files from getting too large |
| Save as binary (.xlsb) | Overall File | Uses a more compact file format |
| Compress graphics | Overall File | Reduces size of embedded objects |
| Avoid custom formatting | Overall File | Minimizes extraneous styling information |
| Disable Auto Recover | Overall File | Stops auto-embedding of revision history |
Final Thoughts
By taking a holistic approach that addresses your pivot table setup, source data structure, and overall workbook design, you can drastically reduce the file size of even the largest and most complex Excel pivot table files. Doing so will make your files faster to calculate, easier to share and store, and less likely to hit Excel’s limits on maximum file size.
To further minimize pivot table file size for extremely large and complex data models, look into using Power Pivot, OLAP connections, and other advanced tools in Excel for efficient data storage and analysis at scale.
FAQs
What are the main factors that contribute to large pivot table file sizes?
The main factors that contribute to large pivot table file sizes are: having too many pivot fields, using complex calculated fields and items, having multiple pivot tables with separate caches, including unnecessary source data, and using unoptimized Excel file formats and settings.
How can I reduce the number of pivot fields to make my file smaller?
To reduce the number of pivot fields, remove any fields from the Filters, Columns, Rows, and Values areas that aren’t necessary for your analysis. Consider using Slicers instead of Report Filters for fields with fewer unique items. Be especially mindful of removing fields with many unique items, like IDs or timestamps.
What’s the best way to set up source data for smaller pivot table files?
To optimize source data for smaller pivot table files, use an Excel Table or Named Range instead of a normal range. Remove any columns and rows that aren’t needed. If you have a very large data set, consider splitting it into multiple tables or files and using slicers or Power Query to combine them as needed. When optimizing source data, consider converting numbers in Excel to the appropriate format, such as dates, currency, or percentages. This can help reduce file size and make the data more user-friendly for analysis. Additionally, be mindful of any formatting or special characters that may be unnecessary for the pivot table, as these can also contribute to larger file sizes.
How can I reduce pivot table file size by changing the Excel file format and settings?
To reduce file size through Excel settings, save your workbook as a Binary Workbook (.xlsb) instead of the default .xlsx format. Compress any embedded pictures and graphics. Avoid using too many custom cell styles and formatting. Disable the Auto Recover feature that embeds previous versions in the file.
What are some advanced techniques for optimizing very large pivot table files?
For advanced optimization of very large pivot table files, use Power Pivot to create relationships between multiple data tables in a more efficient way than normal pivot tables. Use OLAP cube connections as a data source. Leverage Power Query and Microsoft Query to combine data from multiple sources dynamically.
Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.