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.
To save time when building, formatting and modifying your pivot tables, use the tools in my Pivot Power Premium add-in. With just a few clicks, you can:
and much more!
Last updated: January 15, 2020 10:50 AM