PIVOT tables in Excel are common-place but not many analysts or Excel users create them so the PIVOT Table updates automatically to new data, learn how with this short guide that will save you time and impress your colleagues…
Excel PIVOT tables are a powerful tool for summarising raw data into more meaningful tables, reports and charts and due to these benefits you will find them in many Excel reports and dashboards.
As most reporting is dynamic it tends to get refreshed on some frequency, be it daily, weekly, monthly. One of the dangers of creating an Excel dashboard or report with many PIVOT tables is that if you are not careful you can end up with something that takes an eternity to update every refresh as you will have to redo all the data ranges for every PIVOT table, ensuring it takes the new data into account. This leads to inefficiencies and more importantly it creates potential for errors when an analyst forgets to update one or more of the PIVOT tables.
The way around this problem is to create your PIVOT table and set it up so that it will update the data range automatically.
How to make sure your PIVOT table updates the range automatically
The first step is to create a dynamic named range for your dataset.
You can read and learn more about dynamic named ranges in one of my other posts here. The basics are we define a name for our dataset that covers the entire range of data, and then expands as your data expands.
Take the following example:
Cells A1 to C19 contains the raw data, which in turn feeds the PIVOT table starting in cell E1. What we must do is create a dynamic named range for the dataset so the first step is to Define a name for that range by selecting Formulas, and then Define Name from the menu:
In the New Name box that appears label the range, in this example I have used the name “MyDataset” and then applied the dynamic named range formula, ensuring that the right amount of columns are included (represented by 3 at the end of the formula in this example as there are 3 columns of data):
The next step is to amend the PIVOT table to refer to the dynamic named range, if you look at a PIVOT tables data source when first created the source refers to specific cells.
First click on the PIVOT table, and then select Options and Change Data Source from the menu:
The data source will look something like below, specifically set to a certain range of cells.
The formula for the PIVOT table data source has to change to the dynamic named range, in this example it becomes the label “MyDataset”:
The PIVOT table is now set to update automatically when new data is entered.
If the data expands to become cells A1:C28 all that is needed to update the PIVOT table is select Data then Refresh All from the menu:
I’m sure that will be of use to many of you out there as PIVOT tables are commonly used in Excel dashboards and reporting.
Why not make an effort to complete your next PIVOT table this way, I’m sure once you see how easy it is you will soon make sure every PIVOT table you create is dynamically linked to update automatically.
As always please share any feedback you have in the comments section below.
You can download the sample file here.