Auto updating pivot table

The basics are we define a name for our dataset that covers the entire range of data, and then expands as your data expands.

auto updating pivot table-40auto updating pivot table-57

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 “My Dataset”: Click OK.

The PIVOT table is now set to update automatically when new data is entered.

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.

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.

Tags: , ,