Dynamic PivotTable Data Range (so you don't have to keep changing the range to add new rows and columns!)
When you create a PivotTable, Excel selects the range of contiguous cells. But if you add columns or rows either above/below or to the left/right you need to reselect the data range to refresh your PivotTable.
My normal workaround for this is to create a dynamic named range, and get the PivotTable to refer to that.
Named Range DATA
=OFFSET($A$1, 0, 0, COUNTA($A:$A), COUNTA($1:$1))
This works but I have to remember to set it up each time.
It would be nice to have an option when creating a new PivotTable, to select something like "Make range dynamic?" that automatically adds new rows and columns that are contiguous to the original range.
Believe the easy fix is PivotTable data source is a Table. I am using Excel 2010.
1. Select any cell within the range of cells you want included in the table
2. Ctrl+T on keyboard (or from Excel Ribbon go to Insert tab & click Table in the Tables group of commands)
Note: Table Tools tab appears in Ribbon (only when Table is active whereby any cell within the Table is selected). Best practice is to rename Table Name to simple descriptor i.e. tSales which table contains Sales data.
PivotTable on Table as data source (various methods to achieve this):
1. Insert PivotTable
2. Enter Table Name in the Select a table or range option
@Andi You can apply a filter to your pivot to not include blanks, then when you refresh your pivots, this stays the same
@Bernie Korcyzk, this suffers from the problem of dealing with "(blank)" appearing in the rows section of my PivotTable, and isn't dynamic if columns are added or removed.
@Jon Pelter - I didn't want to bore people with all the detail of why I don't like Tables for this purpose, but seeing as it's you, I'll respond ;)
First let me say I'm very happy to spent time carefully configuring data I care about, and in Workbooks where I want to carefully manage the structure.
However, many of the pivot tables I produce are rough, quick summary tables using transient data of a large size - and based on the results in the Pivot I'll likely go and tweak my dataset and repeat the process until I've got a final dataset I'm happy with. Then in my actual report I'll name all the Tables semantically and reference things properly, etc.
Basically, think of my suggestion as pertaining to Excel as data hacking tool, as opposed to report writing tool.
My problem with Tables in data hacking mode is that they slow me down.
1) Ctrl+T gets me a table but I still then have to either click "my table has headers" or tap TAB, Space, Enter. (Little things I know, but repeated become an irritant)
2) Sometimes my iterated dataset returns fewer rows or columns than last time. Without Tables, from anywhere within my data, I can press Ctrl+A, delete and it removes all data, which I can then paste over the top. My dynamic range formula works out the size of the new range, I can refresh my pivot table and I'm away.
With Tables I have to either:
a) Select any cell within my data, press Ctrl+A, delete, paste my new data in (being careful not to accidentally include my headers again), then go and specify and delete the rows or columns I don't need anymore. Then I can refresh my pivot table. (If I don't remove the unused rows/cols I may get "(blank)" appearing in my row labels or duplicated column names)
b) be very careful to select one of the headers, press Ctrl+A, delete, which deletes the entire table. I can then paste my new data in its place, but I can't refresh my PivotTable because the name of the Table is now different (eg "Table3" instead of "Table2"). So I have to reselect my PT range.
My suggestion is simply to make PivotTables, on refresh, include any new adjacent rows/columns in a non-Table range. This would be similar to the External Data Range Properties option "If the number of rows in the data range changes upon refresh".
Sorry for the length! Hope that clarifies my suggestion.
Bernie Korczyk commented
Another trick is to set your data range using only the columns (ex Range = $A:$D). This only works if there is not data below your table. You never have to worry about changing the PivotTable range, but when you add data to your table, you do have to refresh the PivotTable
Jon Peltier commented
What hassle? Just click in the data range and press Ctrl+T. No more worries.
Yeah, I know but it's a hassle when I just want to paste the data in and get on with it!
Col Delane commented
In XL2007+, you can overcome this by making your source data a Structured Reference Table ("Excel Table") that will automatically expand when new data (rows or columns) are added, and link your PT to the Table name.