How To Prevent Pivot Table Bloating In Microsoft Excel

How To Prevent Pivot Table Bloating In Microsoft Excel

One of Excel's most powerful features is that a Pivot Table. It will allow you to analyse countless spreadsheet records and quickly summarise, explore and present your data. By it's very nature, a Pivot Table will take vast amounts of columns and rows of data, so how does Excel store and display all of this data. Understanding this will allow you to avoid bloating of your Excel workbooks and unnecessary bloating of your Excel files.

Excel will take a snapshot of your data set when you create a Pivot Table report and stores it in a pivot cache. This is simply just an area where your data source is duplicated for easy access, this is important to understand as each time you create a a Pivot Table report from a separate data source your file size increases also.

This can bloat your file size and this increase in file size will depend on the size of your original data you are analysing. It can easily in now time double the size of your workbook.
I have a few tips to avoid Bloating in your Excel work books.

1. Delete Your Source Data Tab.
If your Table and your source data tab are present you are definitely wasting space. You are keeping two copies of the same data!. You can safely delete your source data and your Pivot Table will function just as well. Once saved your source fill size will reduce.
This type of action is useful if you are sending your Pivot Table to others to use, the only functionality lost of the ability to refresh as the original data is not there, but as a once off piece of analysis- I find it's the right way to reduce the size of the Excel file.

2. Copy And Paste -Don't Create From Scratch
As I said in the introduction to this tip, when we create a Pivot Table, a pivot cache is created by Excel, so it makes sense if you are making multiple Pivot Tables from the same same data source, you use the same pivot cache right?. In this way we again prevent the Excel file size bloating.

When you want to create a new pivot Table form the same data just copy an past the original table, then edit and changes as required. We have just avoided creating another cached area of the same data. Well done guys!

BJ Johnston has been an advanced Excel user for 15 years and is the creator of http://www.howtoexcelatexcel.com/?page_id=2 a site that shares Excel tips and tricks with it's enthusiastic members. To join in the discussion, where users are working smarter and faster with Excel sign up for the FREE newsletter and as a bonus receive a FREE EBook- 50 Top Tips and Tricks. http://www.howtoexcelatexcel.com/

Article Source: http://EzineArticles.com/?expert=BJ_Johnston

Article Source: http://EzineArticles.com/8513861