After creating a Pivot Table, we can change the range of the Pivot table Source data (the origin of the data). We can also select the range and expand the source data to include more rows or reduce the rows. The steps below will walk through the process.
Figure 1: Changed Pivot Table Source Data
The Data
- We will change the source data of the Pivot Table in figure 2, add to the rows and also remove some rows.
Figure 2: The data
Changing the data source of the Pivot Table
- We will change the data source of the Pivot table in figure 2 to that of figure 3
Figure 3: New data source
- We will click on any cell within the Pivot Table of figure 2
- We will click on Analyze
Figure 4: Click on analyze
- We will click on Change Data Source
Figure 5: Change Data Source
- We will see the Change Pivot Table Data Source dialog box
Figure 6: Change Pivot Table Data Source dialog box
- We will ensure that the data we want to insert into the pivot table is open in another excel sheet
Figure 7: Go to the excel sheet containing the data
- We will open this excel sheet and select the range that we want to insert
Figure 8: Selected range
- We will click OK
- We will receive the prompt in figure 9. We will click Yes
Figure 9: Prompt to Replace Pivot Table
- We will notice a change in our Pivot Table Fields to the fields of the data we want to enter
Figure 10: Check new Pivot Table Fields
- We will check the fields
Figure 11: Changed Pivot Table Source Data
Adding and Removing data from the New Pivot Table
- We can add data to the new Pivot table by clicking on the last cell of the first column of the data source.
- We will press enter
- This automatically adds another row to the range. We will add an extra date and sales amount.
Figure 12: New data added to Row 20
- We will go to the workbook of our Pivot Table
- We will click on any date in the Pivot Table and right-click
- We will click on Refresh and click Yes at the Prompt
Figure 13: Adding data to the New Pivot Table
- We can remove data by clicking on the values we do not want and press the backspace key
Figure 14: Removing data from the New Pivot Table
- We will as well click on Refresh
Figure 15: Removing data from the New Pivot Table
Note
- The dates have been sorted by right-clicking to return values from Oldest to Newest dates, i.e. 2016 values first before 2019 values
- Values can be added to the existing Pivot Table by copying and pasting the range if the data to be added has the same headers as the current table. In this case, we will simply click on Refresh
Instant Connection to an Expert through our Excelchat Service
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment