How to select entire excel spreadsheet for pivot table?

2
votes

I want to be able to select and reference all of the tabs in my excel spreadsheet (to get totals of everything), but the data source only allows one tab to be selected... is there a way to select and use the entire workbook in the pivot table?

Thank you!

5
excelpivot-table
So you have the same table being continued through multiple tabs? - Jonathan M
Yes, the format is exactly the same across the whole workbook. Btw there are about 24 tabs that need to be referenced... :/ - Holly
Are you using multiple tabs because you've got more than the max number of rows allowed in a single tab? - Jonathan M
No. The multiple tabs are to show devices needed in different departments of a hospital. They need to be shown separately to show the quantity needed and total amounts for each department. - Holly

5 Answers

4
votes

You actually just have a data formatting problem. You're using tabs to separate devices by departments instead of simply creating a department field/column and putting everything in one tab. The data was likely delivered to you that way, but don't let the delivery format define the true working format of the data.

Combine everything in one tab, and then you'll be able to use the data in a pivot table just fine.

If you have more data than will fit in a tab, put it in a database such as MS Access, MS SQLServer or mySQL, and then tell excel to use the database as the pivot table source.

1
votes

I think Jonathan M has the best answer, but here's a way to do it using SQL that I've used successfully.

0
votes

I don't know any easy way of doing this, but you could write a macro to copy and paste all your existing sheets onto a single new sheet (assuming there's enough room on a single sheet).

0
votes

In 2007 Multiple Consolidated Ranges isn't in the default pivot table window. You need to create your pivot table, click on it, hit Alt + D, then P. Click back to step one and you'll have the option for multiple consolidated ranges.

0
votes

Select the data on any of the one sheet and hit Alt D > P. It will show you Step 1 of 3 for PivotTable Wizard. Now select Multiple Consolidation Ranges and hit Next.
Select and add different fields from different tabs and it will create a consolidated range for your PivotTable and then you can run your PivotTable based on data located on different tabs of sheet at the same time.

Hope it helps.