One of the most powerful features of Excel is the pivot table. This will allow you to analyze countless spreadsheet entries and quickly summarize, investigate and present your data. By its nature, the pivot table takes up a huge number of columns and rows of data, therefore, as Excel stores and displays all this data. Understanding this will allow you to avoid bloating your Excel textbooks and unnecessary bloating of your Excel files.
Excel will take a snapshot of your dataset when you create a pivot table report and stores it in pivot cache. This is simply the area where your data source is duplicated for easy access; this is important to understand, because every time you create a Pivot Table report from a separate data source, the size of your file also increases.
This can damage your file size, and this increase in file size will depend on the size of your source data that you are analyzing. Currently it can easily double the size of your book.
I have some tips to avoid bloating in your Excel workbooks.
1. Remove the tab “Source Data”.
If your table and source data tab are present, you are definitely losing space. You keep two copies of the same data! You can safely delete the original data, and your pivot table will function just as well. After saving, your fill size will decrease.
This type of action is useful if you send your pivot table to other users, the only functionality lost in the ability to update is because there is no source data, but I find it once I analyze it; s is the right way to reduce the size of an Excel file.
2. Copy and paste -Not create from scratch
As I said in the introduction to this question, when we create a pivot table, a pivot cache is created by Excel, so it makes sense, if you create multiple pivot tables from the same data source, you use the same right cache key, Thus, we again prevent the size of the Excel file from becoming inflated.
If you want to create a new pivot table of the tables, the same data will simply copy the past of the original table, and then edit and modify it as necessary. We simply avoided creating another cached area with the same data. Well done boys!
