How to Change Data Source Reference – Excel Pivot Table

This tutorial demonstrates how to change the data source reference in an Excel pivot table.

Change Data Source Manually

  1. Click within your pivot table in order to show the PivotTable Analyze tab in the Ribbon.
  1. In the Ribbon, select PivotTable Analyze > Data > Change Data Source.

 

datasource ribbon change

 

  1. Click the small arrow to the right of the existing range that is already entered in the Table/Range box.

 

datasource change source

 

  1. Select the new Range of cells for your Pivot table and then click on the small drop-down arrow to the right of the selected range.

 

datasource source changed

 

  1. Click OK to amend the data source.

Change the Data Source Automatically

If your data is set up as an Excel table, and you add data to this table, the data source for your Pivot will automatically be updated to include any extra data that is added to your table.

 

  1. Before you create a Pivot table, click within your data, and then, in the Ribbon, select Insert > Table.

 

datasource table

 

  1. Your data area should all be selected as long as there are no blank cells, rows or columns in the data area.

 

datasource create table

 

  1. Click OK to create your table.
  1. Now, create your Pivot table by clicking in the middle of the table of data, and in the Ribbon, select Insert > Pivot table. Add the Column, Row and Value areas as required.

 

datasource pivottable

 

  1. Next, go back to your data table, and add 3 more rows to the bottom of the data.

 

datasource add rows

 

  1. Switch back to your Pivot table, and in the Ribbon, select PivotTable Analyze, Change Data Source. You will notice that the data source for your Pivot table does not show a range of data, but rather the name of the table – in this case, Table1.

 

datasource check data source

 

Therefore, you do not need to change the data source as it will automatically include the 3 new rows in your table. Rather, click Cancel and then, in the Ribbon, select PivotTable Analyze > Refresh. This will refresh the Pivot table to include the data from the new rows that were added to the table.

 

datasource refresh

 

Data Source Not Valid

If you are amending the data source of a Pivot table, and get the following message, it may be that your data source has been deleted or moved by mistake.

 

data source not valid

 

If this is the case, make suer that the Table/Range in the Select a table or range drop down box is a valid range of data.