Change Data Source for Pivot Table

PivotTables are powerful and help us in analyzing the data. Before creating a PivotTable, we must select the data range we cover in the PivotTable report generation. Any deletion of data will affect if the refresh button is clicked. Likewise, any values that are changed will also affect the refresh button.

However, one of the complaints of the PivotTable users (not experts) is that any additional data beyond the data range selection will not impact the PivotTable report. It is also one of the problems because whenever there is extra data, we need to change the data range. However, in this article, we will show you how to change the data range for the PivotTable. Also, we will show you an automatic data range picker at the end of this article.

Locate the Data Source of Pivot Table

One of the things is when we get the Excel workbook; sometimes, we may have a pivot tablePivot TableA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more, We do not know exactly where the data source is.

How to Change Data Source in Excel Pivot table?

Below are some examples of changing the data source.

  • For example, look at the PivotTable below. To identify the data source, place a cursor inside the PivotTable in any of the cells. It will open up two more tabs on the ribbon “PivotTable Analyze” and “Design.” Go to “PivotTable Analyze” and click “Change Data Source.” It will open up the below window. It will take you to the data worksheet with the data range selection. You can see that the data source is ‘Data Sheet’!$A$1:$D$11.In this, “Data Sheet” is the worksheet name, and “$A$1:$D$11” is the cell address. Like this, we can locate the data source range in Excel.

You can see that the data source is ‘Data Sheet’!$A$1:$D$11.In this, “Data Sheet” is the worksheet name, and “$A$1:$D$11” is the cell address. Like this, we can locate the data source range in Excel.

#1 – Change Data Source of Pivot Table

PivotTable has been created for the range of cells from A1 to D11 to reflect anything in this range in the PivotTable with the help of the “Refresh” button. So, for example, now we will change the sales number for the “Furniture” category.

Go to the PivotTable sheet and right-click the “Refresh” option to update the PivotTable report.

The “Furniture” category sales numbers will change from 69525 to 40432.

It is fine!!!

However, we have a problem: our data range selected for the PivotTable is A1:D11. So now, we will add 3 more data lines in rows from 12 to 14.

PivotTable “Refresh” will not work because the cell reference range given to the PivotTable is limited to A1:D11. So, we need to change the date range manually.

Go to “PivotTable Analyze” and click “Change Data Source.”

It will take you to the actual data sheet with the highlight of the already selected data range from A1:D11.

Now, delete the existing data range and choose the new data range.

Click “OK,” and the PivotTable will show the updated data range result.

It looks fine. Assume that the data range scenario is increasing daily. If you have 10 to 20 PivotTables, we cannot go to each PivotTable and change the data source range, so we have a technique for this.

#2 – Auto Data Range Source of Pivot Table

We cannot make the PivotTable with a normal data range and pick any additional data source. So, we need to make the data range for converting to Excel Tables.

Place a cursor inside the data cell and press Ctrl + T to open the “Create Table” window.

Ensure the “My table has headers” checkbox is ticked and click on “OK” to convert the data to the “Excel Table” format.

Usually, we select the data and then insert the PivotTable. But with Excel Table, we need to choose at least one cell in the Excel Table range and insert the PivotTable.

Now, add three lines of data just below the existing data table.

Return to the PivotTable and refresh the report to update the changes.

Such is the beauty of using Excel Tables as a data source for the PivotTable. It makes the data range selection automatic. We only need to refresh the PivotTable.

Things to Remember

  • Excel Tables as the PivotTable makes the data range selection automatic. With a single ALT + A + R + A shortcut excel keyShortcut Excel KeyAn Excel shortcut is a technique of performing a manual task in a quicker way.read more we can refresh all the PivotTables.We must press the shortcut key Ctrl + T to convert data to an Excel Table.

This article is a guide to PivotTable Change Data Source. Here, we discuss how to change the data source in a PivotTable (manually and automatic) along with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles:-

  • Power BI Pivot TableRefresh Pivot Table in VBAPivot Table From Multiple SheetsSlicer in Pivot Table