Create a pivot table that is based on a dynamic data source -- that is a range that adjusts automatically, if data is added or removed.
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 no rows that are completely blank.
In the screen shot below,
Next, set up the data range as a dynamic range, using one of the 3 options shown below.
Advantages of using a dynamic range as a data source:
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.
There are detailed instructions here: Excel Tables -- Creating an Excel Table.
To see the steps in creating an Excel Table, please watch this short video tutorial.
In Excel 2003, use the List feature to create a dynamic list. It is a built-in tool that will create a List object on the worksheet.
To create a List:
When a cell in the List is selected, the list has a solid blue border around it, and there are filter drop down arrows in the heading row. There is also a blank row at the end, where you can add new items.
If you prefer not to use a named table or list, you can use a formula to create a dynamic range. This formula can use the INDEX or OFFSET functions to create the range.
The written instructions are here: Create a Dynamic Named Range in Excel 2003
This video shows the steps for using the OFFSET function, in Excel 2007.
Once you have the dynamic range set up, you can create a pivot table, based on that range.
Create a Pivot Table in Excel 2003
You can download the zipped sample file used for this tutorial.
Last updated: October 10, 2020 3:45 PM