Automatically Updating Your Pivot Table

Maybe it is a coincidence or maybe because it is because I am updating my Pivot Table EBook for Excel 2010 for CPASelfstudy.com  but I have been seeing  a lot of questions and comments regarding updating pivot tables automatically as new data is added. So, of course I thought I would talk about it here.

The good news is that in Excel 2007 and Excel 2010, it is very easy to set your data up so that Excel will automatically pull it into your existing pivot tables. The reason for this is that Excel 2007 and Excel 2010 allow you to easily format your data as a table. Once Excel considers your data a table, it automatically treats all the data as part of the table even as data is added or removed from the parameters of the table. You still need to click the Refresh button when the data changes but you don't have to change the source data location each time anymore.

Here is how to change your data into a table

• Click in the data
• Click Insert tab
• Click Table
• Verify the data range  and make sure the checkmark is in "My table has headers"
• Click OK

That's it, Excel now considers your data to be a table.  If you open your Pivot Table and re-select  your data source, the pivot table will now update as the range changes.  Now, if data is added it will automatically be incorporated into the parameters of the original source data.


By the way, another plus of turning your data into a table is that Excel automatically displays alternating row shading and AutoFilter drop down boxes for you.