Go Back

Using a Range with a Pivot Table in Excel

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.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:

Leave a Comment

avatar