Excel - Combine Pivot tables - confused about 'how-to'

Asked By heyredon on 06-Mar-09 03:26 PM
I have read several instructions on-line & here on combining pivot table data
and am confused and/or I'm doing it wrong.  Anyone's help would be sincerely
appreciated! Using Excel 2003.

I have pivot tables of employee time punch data and more than/less than
number of hours worked per day calculations. 1 pivot table worksheet = 1
month; 1 workbook = 12 months (Jan-Dec); there are 5 workbooks (2004-2008). I
want to combine pivot table data for months Jan-Dec into 1 pivot table. Can I
do that?  If so, how?

Data labels are "Last Name", "First Name", "Badge", "More than 3.5 hrs?",
over the course of 1 year, the count of 'True' and 'False' and 'n/a'  results
for each employee.  I will then need to combine over the course of 2004-2008,
the same count results for each employee.  I'll start with info. per year for
now, though.

I hope this makes sense. Need more information, please ask. Thank you very

Herbert Seidenberg replied on 06-Mar-09 10:20 PM
Excel 2007 (Looks better than 2003)
Pivot Table, consolidate months/sheets.
Count True/False/NA
Ashish Mathur replied on 06-Mar-09 09:04 PM

I was trying your solution but could not quite get your result.  When I
select the range and click on 1 (for I will select 1 page field), I do not
see any any option in the drop down there.  Could you please elaborate on
the process.


Ashish Mathur
Microsoft Excel MVP
Herbert Seidenberg replied on 08-Mar-09 09:17 AM
In step 2b of the PivotTable Wizard,
select the number of page fields (i.e. 1),
select a range in the "All ranges" window,
(i.e. Table5[[#All],[Badge]:[Less12]])
and type in an arbitrary label into "Field one",
(i.e. May).
After typing in the months for all the ranges,
click on the arrow and all the months will show,
(for no practical purpose)
Ashish Mathur replied on 07-Mar-09 04:13 AM

Thank you for the solution.  I still cannot get it right.  I have taken
another example at the following link -


My question is - Why does "Sector" and "Department" (which are non-numeric
fields) appear in the data area?  I want to show it (as a text filed) in the
row area or column area.  I'd appreciate if you could elaborate the steps.

Thank you for your time and help.


Ashish Mathur
Microsoft Excel MVP
Herbert Seidenberg replied on 08-Mar-09 09:17 AM
Excel 2007
Pivot Table
Consolidate multiple sheets.
Multiple text row/column fields.
Requires Reverse Pivot Table,
done manually or here done with a macro.
Ashish Mathur replied on 08-Mar-09 11:29 AM

Thank you for your help.


Ashish Mathur
Microsoft Excel MVP