How do I get row headers (in my data) into column headers (in my pivot table)?

smart analyst

New Member
Joined
Mar 27, 2018
Messages
6
Hi all,

I'm trying to figure out two things here:

1.) How do I get my row headers (from data) into specialized, newly created header fields (in my pivot table)?

2.) How do I then go on to create a calculated field from these two new specialized headers in the pivot?


Simple example below:


1619362743740.png



1619362769921.png




The yellow section highlights my first question - how do I get separate row headers as unique field items in columns in my pivot table

The green section highlights my second question - how do I get get a calculated field in my pivot table (in this case variance) with those newly created unique fields?


Thank you!
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,094
Office Version
  1. 2016
Platform
  1. Windows
Put TYPE in column section.
 

smart analyst

New Member
Joined
Mar 27, 2018
Messages
6
Put TYPE in column section.
Hey Mart,

I understand this part - however, I need to be able to calculate the items in the type field, which means that I would have to keep them in values, if I remember correctly. So in order to get a calculated variance between both row types, how would I go about doing that?
 

rhombus4

Active Member
Joined
May 26, 2010
Messages
446
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Department in rows
Type in columns, Type in values as count
Insert calculated field -
formula = budget - actual

Difficult to be more detailed as typing this on phone
 

smart analyst

New Member
Joined
Mar 27, 2018
Messages
6

ADVERTISEMENT

Department in rows
Type in columns, Type in values as count
Insert calculated field -
formula = budget - actual

Difficult to be more detailed as typing this on phone
Hi rhombus,

When I try this method, I don't have "budget" and "actual" to choose from when I try to calculate field. Is there a way I can have these row headers appear as individual fields so that I can calculate them in my pivot table? Right now, I only see "type", "department", and "headcount" as fields that can be calculated.
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,094
Office Version
  1. 2016
Platform
  1. Windows
Instead of calculated field you can use calculated item.

Put only TYPE in ROWS
Select a item
Make a calculated item (now you can choose BUDGET and ACTUAL)
Move TYPE to COLUMNS
Add the other fields
 
Solution

smart analyst

New Member
Joined
Mar 27, 2018
Messages
6
Instead of calculated field you can use calculated item.

Put only TYPE in ROWS
Select a item
Make a calculated item (now you can choose BUDGET and ACTUAL)
Move TYPE to COLUMNS
Add the other fields
Thank you, Mart!

This accomplished what I needed to do.

Although, I am curious - if I wanted to get "actual", "budget" and "variance" as separate field items (sitting in the values portion of the pivot table), how would I do that?

There's a report that I see that was able to do something like that, and I'm very curious how I would do it.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,970
Messages
5,656,164
Members
418,284
Latest member
LaurentT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top