Includes new data automatically in a PivotTable?

Question: How to make a PivotTable automatically select new data as it is added to the source?

Answer: Follow these steps – or even just the first one – to see your data source extend automatically for any PivotTable

Step 1 – Convert your Source Data into a Table

Excel provides a great feature called Tables (that’s the name since Excel 2007, it used to be called Lists in 2003 and didn’t have as many options) which automatically expands when you add data. So that’s a good start! Here’s how.

1. Select your source data
2. On the Insert Tab, click Table or press CTRL+T. (If you use Excel 2003, select List in the Data menu).

Convert an Excel range to a table. It resizes automatically for charts, pivottables and includes many other features.

Excel Table: Give your table a name if you want. It makes formulas e.g. VLOOKUP so much easier to read back

Add a column or row and it gets added to that table automagically. Let’s continue.

Step 2 – Create the PivotTable

1. As usual, select your source data and then on the Insert tab, click PivotTable – or press ALT,N,V,T.
2. In the Create PivotTable dialog, just check that the Table/Range source is the name of your table. All good? Click OK.
   

   Create PivotTable dialog: select the table used as data source

3. Drop fields into your PivotTable.

Step 3 – Refresh PivotTable on Open

PivotTables need to be refreshed (recalculated) when the source data changes but you can also make this happen automatically each time the PivotTable file is open.

1. Right-click anywhere inside the PivotTable and select PivotTable Options in the contextual menu (or press ALT,JT, T,T). The Options dialog opens.
2. On the Data tab, activate the checkbox for Refresh data when opening the file. Click OK
   

Sin é. One less thing to worry about when you create a PivotTable and need to update it with current data! You know it is always up to date.