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 table, and we don’t know exactly where is the data source.
Following are the steps to locate data source of pivot table.
- For example, look at the below pivot table.
- 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” & “Design.”
- Go to “Analyze” and click on “Change Data Source.”
- This will open up the below window, and also it will take you to the data worksheet with the data range selection.
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.
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.
By going to the pivot table sheet and by right click and choose the “Refresh” option to update the pivot table report.
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.
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.”
This 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 on “Ok,” and the pivot table will show the updated data range result.
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.
Make sure the “My table has headers” checkbox is ticked and click on “Ok” to convert the data to the “Excel Table” format.
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.
Now add three lines of data just below the existing data table.
Now come back to the pivot table and refresh the report to update the changes.
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
- Excel Tables as the pivot table makes the data range selection automatic, and with the shortcut excel key of ALT + A + R + A, we can refresh all the pivot tables in a single shortcut key.
- To convert a data to an excel table, press the shortcut key Ctrl + T.
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 –