How to Create an Excel Pivot Table from Multiple Sheets

http://www.contextures.com/xlPivot08.html If Excel data is on different sheets, you can create a pivot table using multiple consolidation ranges. This video shows you the steps in Excel 2007, to create the pivot table and set up page fields.

To create a regular pivot table from data on multiple sheets, you can use a macro that creates a union query from all the data. There is a sample file to download at http://blog.contextures.com/archives/2009/08/24/create-a-pivot-table-from-multiple-sheets/

---

Closed Caption:

if you're creating a pivot table in
excel
it's best if you have all your data on
one worksheet and create a pivot table
from that but if your data is own
separate sheets and you can't change it
you can use multiple consolidation
ranges to create a pivot table
so 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 have the
same column headings
they just have different data and
there's a different number of rows of
data on each sheet but as long as the
columns are set up the same will be able
to create our pivot table
there's no command on the ribbon in
Excel 2007
but on your keyboard you can press all d
and then type a pee and that opens the
pivot table and pivot chart wizard in
step 1
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 are 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 then I'll go to the
west sheet and select all the data and
click add again and 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 drop down for that page
field
so if i click on East when i use that
drop down i'd like to see east to
represent that data and here all 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
so here's our pivot table and here's the
page field that we created and it shows
east and west
now click OK
it's by default is showing us the count
of these values might 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 some now
some of these columns we don't really
need if we look at our pivot table color
I don't need or date the price the sales
rap 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 checkmarks for color 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 pivot
table options and here on totals and
filters
I'll remove the check mark for grand
total for Rose and click ok so now we
can just see the total dollars and the
number of units that were sold and the
last change all make here and 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 so it just makes it a
little more clear what that page
represents and if i wanted to i could
move region down below the row field so
it would show total for pens and then
each region below it