Rafiul Haq
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.
- After that, we Save and Close the file.
- Lastly, we can see the file size is 1,007 KB.
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.
- Next, the “PivotTable from table or range” dialog box will appear.
- Then, we have kept every setting as it is.
- After that, press OK.
- So, a new Worksheet named “Sheet1” will be created on the Workbook alongside the PivotTable1.
- 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.
- 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”.
- So the Value Field Settings box will pop up.
- Then, select Count and press OK.
- Afterward, the pivot table will look like this.
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.
- Next, create a new Workbook and press CTRL+V to paste the pivot table.
- Then, Save and Close the 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.
- Now, if we want to see the details of the dataset, then we can simply double-click on the Grand Total value.
- Lastly, a new Worksheet called “Sheet2” will be created to show the details of the data from the Pivot Table.
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!
