Pivot Table Data Field Option

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thanks for your time.

My data is as follows:
Connection City Type
1 LA P
1 NY T
2 LA T
3 NY P
3 DC P
3 LA T

I have a standard Pivot Table with 1 heading on the left table and 1 heading
on the top. Nothing fancy. Here is the layout:

Count of Type City
Connection DC LA NY
1 1 1
2 1
3 1 1 1

What I'm looking to due is instead of showing "Count" of Type, I.E. the
number 1, I want it to give me the value (P or T) from the Type Column. Like
this:

Value of Type City
Connection DC LA NY
1 P T
2 T
3 P T P

Thanks again for your time.
 
In the source data, add a column where you assign a number to the type,
either manually or with a formula. For example:
=IF(C2="P",1,2)

Then, in the pivot table, add the new field to the data area.
Right-click on one of the data cells, and click on Field Settings
Summarize by Max, and click the Number button
Apply a custom format to the numbers:
[=1]"P";[=2]"T";General
 
Genius. Sounds like you do the same stuff I do most of the time. Find a
nice work around for the issue. Thanks for you time. You have a great day.

Debra Dalgleish said:
In the source data, add a column where you assign a number to the type,
either manually or with a formula. For example:
=IF(C2="P",1,2)

Then, in the pivot table, add the new field to the data area.
Right-click on one of the data cells, and click on Field Settings
Summarize by Max, and click the Number button
Apply a custom format to the numbers:
[=1]"P";[=2]"T";General

Fred said:
Thanks for your time.

My data is as follows:
Connection City Type
1 LA P
1 NY T
2 LA T
3 NY P
3 DC P
3 LA T

I have a standard Pivot Table with 1 heading on the left table and 1 heading
on the top. Nothing fancy. Here is the layout:

Count of Type City
Connection DC LA NY
1 1 1
2 1
3 1 1 1

What I'm looking to due is instead of showing "Count" of Type, I.E. the
number 1, I want it to give me the value (P or T) from the Type Column. Like
this:

Value of Type City
Connection DC LA NY
1 P T
2 T
3 P T P

Thanks again for your time.
Click to expand...
Click to expand...
 
Back
Top