Pivot Table Multiple Sheets Video Home > Pivot > Create > Multiple Sheets This video shows how to create a Pivot Table using data from different sheets in a workbook. Get the sample workbook, and a full transcript is below the video. NOTE: For written step-by-step instructions, go to the Multiple Consolidation Ranges page

Pivot Table from Multiple Sheets

To see how to create a pivot table from data on different sheets, watch this short video. The full transcript is below the video. Get the completed workbook, to see the pivot table.

Video Timeline

• 0:00 Excel File - Two Sheets
• 0:37 Open PivotTable Wizard
• 1:13 Select Worksheet Ranges
• 1:32 Page Field Settings
• 2:03 Format the Pivot Table
• 3:33 Filter the Data

 

Video Transcript

If you're creating a Pivot Table in Excel, it's best if you have all your data on one worksheet and create the pivot table from that.

But if your data's on separate sheets and you can't change it, you can use multiple consolidation ranges to create a Pivot Table.

Here we have a workbook with two sheets. There's a sheet with data from the East region and some from the West. The sheets are set up the same, they have the same column headings. They just have different data and there's a different number of rows of data on each sheet. As long as the columns are set up the same, we'll be able to create our Pivot Table.

There's no command on the ribbon in Excel 2007, but on your keyboard, you can press ALT+D, and then type a P and that opens the PivotTable and PivotChart Wizard.

In step one, you're going to click multiple consolidation ranges, and then click Next.

In step two, you have a choice of a single page field or creating your own, and I usually select that and click Next.

Now here, we're going to select our ranges of data, so on the worksheet, I'm going to select all the data on the East sheet and click Add, and then I'll go to the West sheet and select all the data, and click Add again.

At the lower section here, I'm going to create one page field and each range that I've added can have a label in the dropdown for that page field. So, if I click on East, when I use that dropdown, I'd like to see East to represent that data, and here, I'll type West, and then click Next.

And I'd like my pivot table on a new worksheet, so I'll leave that and click Finish.

Here's our pivot table and here's the page field that we created, and it shows East and West, and I'll click OK.

It, by default, is showing us the count of these values and I'd rather it show a sum, so I'm going to just right-click on one of the value cells and go down to Summarize Data By and select Sum.

Now, some of these columns, we don't really need. If we look at our pivot table, Colour, I don't need, or Date. The Price, the sales rep.

I would just like to keep the Total and the Units, so I'm going to click the arrow for Column Labels and get rid of the check marks for Colour, Date, Price, and Rep, and click OK. So, it's looking better now.

This grand total, though, is adding up these other two columns, so I don't want that. I'm going to right-click and go to PivotTable Options, and here on Totals & Filters, I'll remove the check mark for grand total for rows and click OK. So now, we can just see the total dollars and the number of units that were sold.

And the last change I'll make here, instead of calling this Page 1, I'll just type Region for that.

So now, I can select a region, I can select East and just see its data, or West, or All. That just makes it a little more clear what that page represents.

If I wanted to, I could move Region down below the Row field so it would show the total for pens, and then each region below it.

Get the Sample File

Get the completed Excel workbook that was used in this video -- Pivot Table from Multiple Sheets. The zipped file is in xlsx format, and does not contain macros.