How to Create a Dynamic Pivot Table in Excel
How to Create a Dynamic Pivot Table in Excel. A powerful feature of Microsoft Excel that I encourage everyone to check out is the Pivot Table. This element of Excel allows you to get great insights into your data. One thing that confuses people though is when they add to their source data but the Pivot Table does not reflect the changes.
Many Excel users are not familiar with, or are intimidated by Pivot Tables, one of the most powerful features in Excel. A pivot table is a great reporting tool that sorts and sums independent of the original data layout in the spreadsheet. If you never used one, this post may be of your interest.
Creating a Dynamic Pivot Table
The trick is to create a “dynamic named range”. Rather than just add your table in the usual way, you need to create your pivot table using the named range, then you can add data, refresh, and the new data will automatically show up.
Creating the Named Range
- To create your named range in Excel 2007 go to Formulas>Define Name
- You will need to supply a name for the range, for example “Data”.
In the Refers To box, enter an Offset formula. This defines the range size in the following way:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),7)
- Reference cell: Data!$A$1
- Rows to offset: 0
- Columns to offset: 0
- Number of Rows: COUNTA(Data!$A:$A)
- Number of Columns: 4
Create the Pivot Table using Your Named Range
Now you need to create the pivot:
- Choose Insert>PivotTable
- Select Table/Range.
- For the range, type your range name, e.g. Data
- Click OK
- Continue creating the pivot table as you normally would…
- Click OK
And there you have it – Your dynamic pivot table.
Summary
As is usual with the more powerful Excel features, knowing the trick is just half the battle. Then you need to put the knowledge into practice! Why not create some test pivot tables now so you can really get to grips with this great functionality?
About the author
Yoav Ezer co-authors the technology and productivity blog Codswallop. He is also the CEO of a company that produces PDF to Excel conversion software.
For more Excel tips from Yoav, join him on Facebook or Twitter
