Ace My Paper - get your Excel tasks done by experts. - makes your homework shine.


Excel tutorials

Create Pivot Table From Multiple Sheets

How to Make a Pivot Table in Excel that Pulls Data from Multiple Worksheets

Take for Excel example that we have some sort of Table Data for which we have grouped some data. For that kind of grouping in the same or another Worksheet, we will use the wizard to create a Pivot Table. Pivot tables are used for the organization (grouping) of data according to certain criteria (conditions). If you want to create a PivotTable from Multiple Sheets then use the 'PivotTable and PivotChart Wizard', which is not on the ribbon, or you can run it through a shortcut or shortcut key.

Shortcuts for starting PivotTable and PivotChart Wizard:

Excel 2003

  • ALT+D


Excel 2007, 2010, 2013

  • ALT+D+V
  • ALT+D+P
  • (ALT/D)+P+P (with left hand press together 'ALT' and 'D' keys so they hold, then press 2x key 'P')

Our Table looks like as in the pictures below. Note the names of three identical tables that are located on three sheets. For every coffee bar at a time. So drink delivery is done in three cafes and we have feedback on the quantities sold. We want to automate a review to rapid filtration and similar actions on a worksheet have access to desired information. In this case, the ideal is our Pivot Table.

Range of Data for creating Pivot Table from multiple Worksheets

PivotTable and PivotChart Wizard in Excel

Position on a specific sheet on which to Insert a PivotTable. So let's start the wizard for pivot table ie. Using 'PivotTable and PivotChart Wizard'. Run it through a shortcut (shortcut key), in this case for Excel 2013 shortcut is Alt+D+P. Turn on the radio button for options. The first option required 'Multiple consolidation ranges' and another option of your choice. If you want to only pivot table or chart and graph (PivotChart). Click on the Next button.

Pivot Table and Pivot Chart Wizard in Excel

It opens a new window where you turn on the option 'Create a single page field for me'. Click on the Next button.

It opens a new window where you turn on the option 'Create a single page field for me'. Click on the Next button.

It opens a new window where you need to click on the icon for selecting a range of data to a specific worksheet.

Add data from range specific sheets

Now you have a small window which do not enter anything but click on the name of the first worksheet and on it select the range of data that will be included in the PivotTable. Re-click on the icon at the right of the down arrow to return to the dialog box (Step 2b of 3).

Find range of data for adding in PivotTable from multiple worksheets

In this dialog box after selecting range is necessary to add the respective range in PivotTable by clicking on the Add button, and then again click on the icon with the arrow => selection range on another worksheet, and so on until you have added all the worksheets. After the addition of a range of data and worksheets, click on the Next button.

Added data ranges in PivotTable wizard

In the new dialog box in step '3 of 3' must be selected on the Excel worksheet should create a PivotTable. If you are positioned at the very beginning (as I wrote at the beginning) to a worksheet that was called to see the PivotTable then select the second option "Existing worksheet" otherwise select the first option. Click the Finish button.

Choose destination to place PivotTable from multiple sheets


Destination PivotTable from multiple sheets

View Created PivotTable From Multiple Worksheets in Excel

And finally, we have created a Pivot Table from Multiple Worksheets. Of course by clicking on the drop-down arrow can be filtered, if desired, or by moving selected elements in the right part of the window. Removing an element we're using the "drag and drop method".

Layout Created and Filtered PivotTable from multiple Worksheets