Create a pivot table that is based on a dynamic data source -- that is a range that adjusts automatically, if new records are added or existing data is changed or removed.
Author: Debra Dalgleish
The first step in creating a Pivot Table is to organize your data in a list of rows and columns. Make sure that there is a heading in every column, and check that there are no rows that are completely blank.
In the screen shot below, the data is set up correctly, and has these features:
Tip: To make sure the data is in a solid block of cells, try this:
Before you build your own pivot table in an Excel workbook, you can see how a pivot table works, by trying the interactive Microsoft Excel example shown below.
Note: This embedded interactive Excel file might not work in all web browsers.
Next, set up the data range as a dynamic range, using one of the options shown below.
1) Named Table
Advantages of using a dynamic range as a data source:
NOTE: You must update pivot table after making changes to the records in the dynamic data source. The pivot table does NOT update automatically, to show revised or new data
In Excel 2007 and later versions, you can format your list as a Named Table, and use that as the dynamic source for your Pivot Table.
To see the steps in creating an Excel Table, you can watch this short video tutorial. There are written steps below the video, and more Table tips and videos on the Excel Tables page.
Note: If you're using Excel 2003, organize your data in Excel Lists
After your data is organized, as described above, follow these steps to create a named Excel Table.
If you don't want to use a named table, you can use an Excel formula to create a dynamic range. This formula can use the INDEX or OFFSET functions to create the range.
This video shows the steps for using the OFFSET function, in Excel 2007, and the written instructions are below the video.
To create a dynamic named range, based on a formula, follow the steps below.
Note: Dynamic named ranges will not appear in the Name Box drop down list. However, you can type the names in the Name Box, to select that range on the worksheet. Or, go to the Name Manager, and you can see all the names listed there.
Once you have the dynamic range set up, you can create a pivot table, based on that range.
Watch this video, to see the steps for creating a pivot table in Excel 2013 and later, using a dynamic pivot table data source. There are written instructions below the video, and you'll find more pivot table tips on the Create an Excel Pivot Table page.
NOTE: To create a pivot table in Excel 2010 or Excel 2007, follow the pivot table setup instructions here
First, follow these steps, to see which pivot table layouts are suggested by Excel.
The pivot table will appear on the Excel worksheet, based on the recommended layout that you selected.
Note: If you change column headings in the source table, the pivot table will not recognize the old names any longer. If the old names were in the pivot table layout, they will disappear. You'll need to manually add the renamed fields.
In this video, you'll see how to locate the data source for a pivot table. Then, check the data source, to make sure it includes all the rows and columns that you need. If necessary, change data source, or adjust the change the pivot table data source range, to include new rows or columns.
For the written instructions and sample file, go to the Pivot Table Source Data page.
Note: If you want to try this technique with other types of sample data, such as work ordersdata, or hockey player statistics, go to the Excel Sample Data page, and take a look at the sample files there.
Last updated: November 17, 2022 10:44 AM