Pivot Table Calculated Item and Calculated Field

MVP

If you're not sure how to use Excel's pivot table formulas (calculated items and calculated fields), I've made a short video that shows how they work.

See how to set up simple formulas, and then use a built-in command to quickly create a list of all the pivot table formulas.

See the video, and more details on pivot table formulas, in my Pivot Table blog.

NOTE: These formulas are not available for pivot tables based on the Data Model.

 

calcitemcalcfield01.png

 

1 Reply

@Debra Dalgleish 

I have a pivot table with years as columns, regions as rows, and sales total value in the table body. I have added a calculated item "CAGR" to the string of year to easy see growth over the period for each region.

 

Q1. How can I force the CAGR values to show as percentage values, and stay showing that way even if the table is changed around, even though the sales totals under year remain in currency?

 

Q2. Some regions are missing sales in some year, creating #DIV/0! (no sales first year) errors. How can I have the error indication suppressed and just a blank left in that place when the CAGR calculation doesn't work?