Pivot Table Change Data Source

Change Data Source for Pivot Table

Pivot Tables are powerful and help us big time in analyzing the data. Before creating a pivot table, we need to select the data range that we are covering in the pivot table report generation. Any deletion of data will affect if the refresh button is clicked, and any of the values are changed will also affect the refresh button.

However, one of the complaints of the pivot table users (not experts) is any additional data beyond the data range selection will not impact the pivot table report; for me as well, it was one of the problems because every time whenever there is an additional data we need to change the data range every now and then. However, in this article, we will show you how to change the data range for the pivot table, and 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, and we don’t know exactly where is the data source.

Following are the steps to locate data source of pivot table.

  1. For example, look at the below pivot table.

    Change Data Source Example 1

  2. To identify the data source, place a cursor inside the pivot table in any of the cells, and it will open up two more tabs at the ribbon as “Analyze” and “Design”.

    Change Data Source Example 1-1

  3. Go to “Analyze” and click on “Change Data Source”.

    Change Data Source Example 1-2

  4. This will open up the below window, and also it will take you to the data worksheet with the data range selection.

    Change Data Source Example 1-3
    As you can see that the data source as ‘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 download this Pivot Table Change Data Source Excel Template here – Pivot Table Change Data Source Excel Template

How to Change Data Source in Excel Pivot table?

Below are some examples of changing the data source.

#1 – Change Data Source of Pivot Table

As of now pivot table has been created for the range of cells from A1 to D11, so anything happens in this range will be reflected in the pivot table with the help of the Refresh button. For example, now we will change the sales number for the “Furniture” category.

Change Data Source Example 1-4

By going to the pivot table sheet and by right click and choose the “Refresh” option to update the pivot table report.

Change Data Source Example 1-5

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

This is fine!!!

However, we have a problem here that is our data range selected for the pivot table is A1:D11; now, we will add 3 more lines of data in rows from 12 to 14.

Example 1-7

Now Pivot Table “Refresh” will not work because the range of cells reference given to the pivot table is limited to A1:D11, so we need to change the date range manually.

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

option Example 1-2

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

Example 1-3

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

range update Example 1-8

Click on “Ok,” and the pivot table will show the updated data range result.

Change Data Source Example 1-9

Looks fine but imagine the scenario of data range is increasing every day, and if you have 10 to 20 pivot tables, we cannot go to each pivot table and change the data source range, so we have a technique for this.

#2 – Auto Data Range Source of Pivot Table

With a normal data range, we cannot make the pivot table to pick any additional data source, so for this, we need to make the data range to convert to Excel Tables.

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

Change Data Source Example 2

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

Table header Example 2-1

Usually, what we do is select the data and then insert the pivot, but with Excel Table, we need to just select at least one cell in the Excel Table range and insert the pivot table.

Change Data Source Example 2-2

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

add data Example 2-3

Now come back to the pivot table and refresh the report to update the changes.

Change Data Source Example 2-4

This is the beauty of using Excel Tables as a data source for the Pivot Table, so this will make the data range selection automatic, and we just need to refresh the pivot table.

Things to Remember

This has been a guide to Pivot Table Change Data Source. Here we discuss how to change the data source in a pivot table (by manually & automatic) along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion