Pivot Table include values and % Of

martipe1

New Member
Joined
Mar 16, 2007
Messages
43
I have created a Pivot Table for sales comparison purposes, I summarize Units and $ and group them together by Budget, Last Year and Actual.

I can see the numbers but if I want to make a comparison and I choose % OF, the values automatically are replaced by the %.

Is it possible to create a pivot table that can have values and the %OF, comparing Units and $ actual vs. Last Year and budget in the same table?

I have done it manually in the past but it is too much work, that is the reason I prefer to use a Pivot Table (the number of records I have are more than 30,000)

Thanks
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

martipe1

New Member
Joined
Mar 16, 2007
Messages
43
Praveen,

Thank you for your answer.

I included two calculated fields and made them equal to $ and Units and modify those fields to become %OF, that solved my problem.

Is there any way to replace the annoying #DIV/0! in the %OF field?

Thanks,
 

pavin

Active Member
Joined
Jan 20, 2009
Messages
308
Use a 'IF' condition & 'ISERROR' in the Calculated Field. For example

IF(ISERROR(%OF),"",%OF)

Hope this helps.
 

martipe1

New Member
Joined
Mar 16, 2007
Messages
43
Praveen,

Thanks, as an alternative option in Pivot Table, Table Options I checked "For errors values, show:" and left it blank.

Once again thank you
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

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