0

I'm trying to put togeter a pivot table from an Excel spreadsheet. The spreadsheets look similar to the following:

DeptHead, Emp,  Increment
x,        A,      2.5%
x,        B,
y,        C,      1.5%
y,        D,
y,        E,      2.0%

I would like to make a pivot table that looks like the following;

DeptHead, CountOfEmp,  CountOfIncrement
x,        2,           1 
y,        3,           2

So it provides a count of total number of Emps and total number Increments for each DeptHead ignoring the blanks. I have tried to do this in many ways in Pivot table, but the two counts are only appearing in rows and not in columns as above.

Is there any way to achieve this please? Thanks

Improve this question

2 Answers 2

Reset to default
1

enter image description here

Does your layout look like this? And, you might select the 'classic' layout as well, in the 'Display' tab on the 'Pivot Table Options' dialog form.

Improve this answer
2
  • Thanks for that, but it looks like Excel 2007. I'm still using 2003 and I don't see the same UI. I have uploaded a sample file if that helps. Thanks. Sample File
    – Sivakanesh
    Feb 9, 2011 at 8:29
  • Ah -- sorry; didn't think about 2007 vs 2003. However: in your "Sample File", and using 2007 (sorry, that's what I have access to!) I clicked in cell B3 (which says "Data") so that it was highlighted. Then, I dragged that cell and dropped it into cell C3. The pivot table rearranged to your desired order. I HOPE it works the same way in 2003 (I think it does.)
    – F106dart
    Feb 14, 2011 at 17:09
1

It could be you are just not placing the fields correctly in the Pivot Table field list.

Place the Emp and Increment fields in the "Values" section.

Improve this answer
2
  • I've done this, but this places the Emp and Increment fields below each other on rows, grouped by DeptHead, rather than putting them in separate columns.
    – Sivakanesh
    Feb 3, 2011 at 8:01
  • You must put Sum Values in the Column Labels Section like in the picture above not in the Row Label Section
    – kev
    Jan 12, 2018 at 7:07

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .