How to Reduce Excel File Size with Pivot Table

Get FREE Advanced Excel Exercises with Solutions!

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.


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 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.


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

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.

  • 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.
  • We have kept the row headings fixed up to row 4.
  • 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.
  • We can see the file size is 1,007 KB.

How to Reduce Excel File Size with Pivot Table 2


Step 2: Inserting Pivot Table

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

  • Select anywhere inside the source dataset, we have selected cell B5.
  • From the Insert tab → Select PivotTable.

How to Reduce Excel File Size with Pivot Table 3

  • The PivotTable from table or range dialog box will appear.
  • Then, we kept every setting as it is and press OK.

How to Reduce Excel File Size with Pivot Table 4

  • A new worksheet named Sheet1 will be created on the workbook alongside the PivotTable1.

Pivot Table Default

  • 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

  • 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

  • The Value Field Settings box will pop up.
  • 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


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.

  • Select the pivot table and press CTRL+C to copy it.

Copy Pivot Table

  • 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

  • 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-fourth 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

  • 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 Reduce Excel File Size Without Deleting Data


Download Practice Workbook


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 website for more Excel-related articles. Thanks for reading, and keep doing well!


Related Articles


<< Go Back to Excel Reduce File Size | Excel Files | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Rafiul Haq
Rafiul Haq

Rafiul Haq worked as an Excel and VBA Content Developer in Exceldemy for over two years and published almost 200 articles for the website. He is passionate about exploring new aspects of Excel and VBA. He received his Bachelor of Science in Mechanical and Production Engineering (MPE) from the Islamic University of Technology. Rafiul furthered his education by obtaining an MBA in Finance from the Institute of Business Administration (IBA) at the University of Dhaka. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo