Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
All articles PIVOT TABLE Using a Range with a Pivot Table in Excel

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.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc