How to Reduce Excel File Size with Pivot Table

Excel file sizes can get bigger if we have a large amount of data inside them. Decreasing the file size is one of the most important tasks whenever we want to share that file. There are numerous methods for reducing file sizes in Excel, but we will only concentrate on one of them. In this article, we will show you the quick steps to how to reduce Excel file size with the pivot table.


Download Practice Workbook


Pivot Table

The pivot table is one of the most useful tools in Microsoft Excel. This feature accumulates tons of data into an easy-to-read data table. This summarizes data into a report format. This feature saves a ton of time, and everyone who uses Excel as a daily driver should know how to work with a pivot table. There are mainly four layout components to a pivot table: “Filter”, “Column”, “Row”, and “Value”. We can drag fields to these layout components to create a variety of pivot tables. When we create a pivot table, it creates a pivot cache, which remembers the source dataset. We can copy the pivot table into a new Workbook and it will help us reduce the file size in Excel.


Step-by-Step Procedures to Reduce Excel File Size with Pivot Table

We will describe to you 3 easy steps to reduce file size with the pivot table in Excel.


Step 1: Setting up Source Dataset

In the first step, we will prepare the source dataset.

  • At first, there are 3 columns in our dataset consisting of: “Car Model”, “Car Maker”, and “Model Year”. This represents data from car dealers in the USA.
  • Then, we have kept the row headings fixed up to row 4.
  • Next, we can see that there are 50,000 rows in this dataset.

How to Reduce Excel File Size with Pivot Table 1

  • After that, we Save and Close the file.
  • Lastly, we can see the file size is 1,007 KB.

How to Reduce Excel File Size with Pivot Table 2

Read More: Reduce Large Excel File Size by 40-60% (12 Proven Methods)


Step 2: Inserting Pivot Table

In this step, we will insert a pivot table using the source dataset.

  • To begin with, select anywhere inside the source dataset, we have selected cell B5.
  • Then, from the Insert tab → select PivotTable.

How to Reduce Excel File Size with Pivot Table 3

  • Next, the “PivotTable from table or rangedialog box will appear.
  • Then, we have kept every setting as it is.
  • After that, press OK.

How to Reduce Excel File Size with Pivot Table 4

  • So, a new Worksheet named “Sheet1” will be created on the Workbook alongside the PivotTable1.

Pivot Table Default

  • Then, drag all three fields (“Car Model”, “Car Maker”, and “Model Year”) to the Rows area.
  • Afterward, drag the “Model Year” field to the Values area.

How to Reduce Excel File Size with Pivot Table 6

  • After that, in the Values area “Sum of Model Year” will be shown. However, we want the “Count of Model Year”.
  • In order to change that, click on it.
  • Then, select “Value Field Settings”.

How to Reduce Excel File Size with Pivot Table 7

  • So the Value Field Settings box will pop up.
  • Then, select Count and press OK.

How to Reduce Excel File Size with Pivot Table 8

  • Afterward, the pivot table will look like this.

Pivot Table

Read More: [Fixed!] Excel File Too Large for No Reason (10 Possible Solutions)


Step 3: Copying Pivot Table to New Workbook

In this last step, we will copy the pivot table to a new Workbook and thus reduce the file size in Excel.

  • Firstly, select the pivot table and press CTRL+C to copy it.

Copy Pivot Table

  • Next, create a new Workbook and press CTRL+V to paste the pivot table.
  • Then, Save and Close the Workbook.

Pivot Table Paste on New Workbook

  • After that, we can see that the original file size increased a bit.
  • Our new Workbook is 327 KB in size compared to 1308 KB in the original file size. That is a whopping 75% reduction in file size. In other words, the reduced file size is only one-third of the original file size.

How to Reduce Excel File Size with Pivot Table

  • Now, if we want to see the details of the dataset, then we can simply double-click on the Grand Total value.

Double Click

  • Lastly, a new Worksheet called “Sheet2” will be created to show the details of the data from the Pivot Table.

Data from Pivot Cache

Read More: How to Determine What Is Causing Large Excel File Size


Conclusion

We have demonstrated how to reduce the Excel file size with the pivot table in just three simple steps. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, and keep doing well!


Related Articles

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I am passionate about all things related to data and MS Excel is my favorite application. I want to make people's life easier by writing easy-to-follow and in-depth guides here at Exceldemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo