Excel Advanced - Calculated Items in a PivotTable - using percentages

Excel PivotTables have calculated fields and calculated items. In this video, I'm going to take a field called Year, which has 2022 and 2021 in it and create a calculated item. In case you are wondering, what is the difference between a calculated field and a calculated item? That is easy to explain. Your data has a header row that contains fields. The items in the field are items.

Example: If you were tracking employees, you could have a field called Department. Listed below department is Accounting, Marketing, Sales, Customer Service. Those four are items, but the department is the field.

Blog post on calculated item

#1 is fields or header row #2 is items in the department field

Chapters
0:00 Introduction
0:17 Calculated field and items explained
1:26 Insert Pivot Table
2:25 Number format
2:57 Calculated Item
4:42 Add percentages - TIP

And make sure you subscribe to my channel!

-- EQUIPMENT USED ---------------------------------

-- SOFTWARE USED ---------------------------------

DISCLAIMER: Links included in this description might be affiliate links. If you purchase a product or service with the links I provide, I may receive a small commission. There is no additional charge to you! Thank you for supporting my channel, so I can continue to provide you with free content each week!

[Robert_]

Amazing trick changing those numbers to percentage! I've been using the hold Ctrl method for years. Thanks Chris 👍🏼

[gw]

Thank you for this, best explanation out of several I've looked at

[rotethongvanit]

Wow! It's like magic. Thanks Chris.

[KeithHarrison]

I KNEW there was a way to do this! Thank you very much!! This is a real life saver... ;-)

[shoppersdream]

Nice, thanks! Now I understand Calculated Items. Thanks

[arthittayaphetkul]

Than you very very much!! This is so helpful and easy to understand!

[pipo]

Smart Chris Menard. Thanks a lot. Great tutorial.

[aformagie]

Great video, love the tips and tricks. Keep 'em coming

[JR-lybx]

Wow i thought i knew everything about PivotTables... Thanks

[deonvangreuning]

Hi Chris, is there a method to recalculate the Total or Grant Total of a product or division based on a "inserted Calculated Item"? I have added a Calculated field to automatically work out the year on year growths for products or divisions which works fine, but the Total or Grand total "Sum's" the individual lines instead of calculating the growth correctly. Do you have a video that maybe explains how to fix this? Thanks

[pipo]

Dear Chris, I have a question about calculated field in Pivot table. It seems I can only use the fields from the table where I have all data. Is it possible to use calculated fields and use one column (field) from the pivot table itself in calculated field formulas?

[NadiaEmamboccus]

This is great but as soon as I start using it on large data it takes forever to calculate and then everytime I make a change in the pivot it's very clunky and takes 5 minutes to update. Is there a way to make this more efficient?

[TheKarolans]

Any idea how to make this work on averages? I have multiple invoices for one product in a year and want to take the average and compare it to the average of that product in the prevoius year. Doing calculated item tells me Excel does not support this on averages.

[zuzanazuscinova]

How do you make a change to the formula once you created the new item?

[bhawanimurariya]

But sir When we are doing at Grand total and Subtotal not coming correct

[fardinkhan]

Well if I click on Calculated Items this appears to me "If one or more fields in the PivotTable have calculated items, no fields can be used in the data area two or more times, or in the data area and another area at the same time. If you are trying to add a field, remove the calculated items and add the field again. If you are trying to add a calculated item, change the PivotTable report so that no field is used more than once and then add the calculated item." Now please help me how can I use calculated item in excel.