Pivot Tables with text fields

[Edward Chambers]

Hello! Please let me know if there's a better place to
post this question.

I'm building a pivot table based on a spreadsheet that
holds staffing information for all employees about job
positions, hiring dates, salaries, etc. Many of these
fields are text fields, like job title, department name,
etc.

I want to pull from this and create something like this:

Department Name Title Hire Date Salary
Marketing
James Smith Product Mgr 1/1/02 $45,000
Patty Jones VP Mktg 1/1/00 $75,000
Will Roberts Mktg Asst 1/1/03 $35,000
IT
Gary Davids VP IT 1/1/01 $75,000
Bob Seater IT Analyst 1/1/03 $50,000

BUT.... What I get by putting Title and Hire Date in a Row
Area, is this, using indented formatting:

Department Name Title Hire Date Salary
Marketing
James Smith
Product Mgr
1/1/02 $45,000
Product Mgr Total $45,000

James Smith Total $45,000
Patty Jones
VP Mktg
1/1/00 $75,000
VP Mktg Total $75,000

Patty Jones Total $75,000

And so on.....

I obviously don't need the total three times! Is there a
way to not have the table calculate totals for every level
of detail?

The other way I tried was to put Title and Hire Date in a
Data area, but because these have to perform a function
like Sum or Average or Count, I can't get it to just
display the value of the field. This way would be ideal,
because all the info would be in one row.

Please help!!

Thanks.

 

[Bernie Deitrick]

Edward,

Put those fields into the Row area, then right-click on each of their grey
buttons, select 'Field Settings....' and set 'SubTotals' to None.

HTH,
Bernie
MS Excel MVP

 

[Edward Chambers]

THANK YOU!

That worked perfectly. It still printed each of those
sections into new lines, but there was a layout
option, "show items in tabular form" that fixed that up.

Most impressive Bernie. You are awesome!