Change Source Data in Pivot Table
Changes the range of the source data for a specified pivot table. You can use this activity if the source range has been modified by adding or removing rows. If the source data has changed substantially, for example columns have been added or removed, you should create a new pivot
table instead. The activity can be used with an Excel file selected for a parent Use Excel File activity or with the Project Notebook. Updated 2 years ago
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 TableOne of the things is when we get the Excel workbook; sometimes, we may have a pivot 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. Following are the steps to locate the data source of the PivotTable.
How to Change Data Source in Excel Pivot table?Below are some examples of changing the data source. #1 – Change Data Source of Pivot TablePivotTable 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 TableWe 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
Recommended ArticlesThis 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:-
|