To do this, select a cell in the Pivot Table, display the Options tab of the ribbon, click the Change Data Source tool in the Data group, and finally choose Change Data Source. Make sure the cell range in the Table/Range field reflects accurately the range you want included in the Pivot Table.
Excel switches to the worksheet containing your data table and displays the Change Pivot Table Data Source dialog box. You should note that if you are adding rows in the middle of the Pivot Table's data range, or if you delete rows, you do not need to be concerned about the cell range reflected in the Change Pivot Table Data Source dialog box. (You only need to be concerned when you add rows or columns to the end of the cell range.) is your source for cost-effective Microsoft Excel training.
This is great if your Pivot Table’s data is linked to another workbook that gets updates by your colleagues and you only get to see the Pivot Table report.
Select the Data tab and check the “Refresh data when opening the file” checkbox and OKNow each morning that you open up your Excel workbook, you can be sure that the Pivot Table is refreshed!
If you have added information at the end of the data table, either manually or using a data form, you will want to redefine the data range used to create the Pivot Table.This is a very nice feature in the latest Excel versions.I have shown my Free Pivot Table webinar to over 40,000 people over the last couple of years and I continually get the same questions from my webinar attendees regarding the little issues they have when using a Pivot Table.This is a great feature and one that most people don’t know about.It allows you to Refresh your Pivot Tables as soon as you open up your Excel workbook.