How to add and remove data from a PivotTable.
Thanks for the great feedback from our article on PivotTables. One concern some of you have is about adding or removing source data after the PivotTable has been created.
That’s understandable because the original PivotTable releases where unclear and curiously sensitive about the data ranges used. Happily, there are many options now available and the data source is easier to change.
A typical reader email said simply “I’m not sure how to update the next month’s data“.
Really it’s just a matter of adding more rows to the original list and making sure all the rows are included in the PivotTable data range.
The direct option is to change PivotTable Tools | Analyze | Data | Change Data Source which lists the explicit data range the PivotTable is using. Change that and the PivotTable will change accordingly.
Sidebar: Note above the vital Refresh button. PivotTable’s do NOT update automatically when original data changes, so remember to click that button to keep the PivotTable in sync with the source information.
There are simpler options that let you add and remove rows from the data table so they’ll be included in the PivotTable automatically.
Tables
From Excel 2007 onwards you can have a list in the form of an Excel Table (previously Excel List).
Excel Tables have the filtering tools in the heading of each column. On the Table Tools | Design ribbon there’s a place to name the table (no spaces allowed!) and a ‘Summarize with PivotTable’ button.
To convert the source data into an Excel Table, select the exact columns and rows then choose Home | Styles | Format as Table.
The bottom line of the table can have totals or a count of the rows. If you want to add more data, add the rows above the total line (Home | Cells | Insert | Insert Sheet Rows) so it’s automatically included in the Excel Table and then any linked PivotTable.
Named Ranges
Instead of an explicit range of cells like B1:C99, create a named range like “CallHistory”.
If you add/remove rows within the named range the cells used in the PivotTable will change as well.
Of course, you can also used the cell range alone e.g. B1:C99 but naming the range makes the spreadsheet easier to understand.
Whole Columns
In the first PivotTable article we made that simple by making the PivotTable ranges entire columns A and B — that was simple to show but also means that you can add additional data to those columns and the PivotTable will pick them up after you press Refresh.
PivotTable experts consider that approach to be crude – and they are right – but it’s simple and works.
For beginners, try the ‘entire column’ trick since it’s easy and lets you add more information – past and future – as it’s available.
Remember to press PivotTable Tools | Analyze | Data | Refresh after adding rows.
