Excel Guides

Reducing File Sizes for Workbooks with PivotTables in Excel

PivotTables are a great way to summarize data in Excel, but they can also make your workbooks much larger in size. There are a few things you can do to reduce the file size of your workbook when using PivotTables.

1. Use the Data Model

When you create a PivotTable from multiple tables of data, Excel automatically creates what's called a Data Model. This is basically a copy of your data in a format that's optimized for use with PivotTables. The Data Model is stored in your workbook, and can make it quite large.

You can reduce the file size of your workbook by using the Data Model instead of creating multiple copies of your data. To do this, select your data and go to Insert > PivotTable. In the Create PivotTable dialog box, check the Use an external data source option, and then click Choose Connection. Select the connection that contains your data, and then click Open. Finally, check the Add this data to the Data Model option, and then click OK. Your PivotTable will be created using the Data Model, and your file size should be reduced.

2. Use Single-Cell References

Another way to reduce the file size of your workbook is to use single-cell references instead of ranges. For example, let's say you have a range of cells that contains sales data for each month. You could create a PivotTable from this range, but it would be much more efficient to create a PivotTable from a single cell reference. To do this, select any cell in your range, and then go to Insert>PivotTable. In the Create PivotTable dialog box, check the Use an external data source (this will ensure that only one cell is used as the source for your PivotTable). Then click Choose Connection, select the connection that contains your data, and then click Open.

Finally, check "Add this data to the Data Model", and then click OK.


Your PivotTable will be created using only one cell as its source, which will reduce the file size of your workbook.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.