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
chrismenardtraining.com/post/...
Screenshot showing fields and items drive.google.com/file/d/1xaqF...
#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 --------------------------------
○ My camera - amzn.to/3vdgF5E
○ Microphone - amzn.to/3gphDXh
○ Camera tripod - amzn.to/3veN6Rg
○ Studio lights - amzn.to/3vaxyy5
○ Dual monitor mount stand - amzn.to/3vbZSjJ
○ Web camera - amzn.to/2Tg75Sn
○ Shock mount - amzn.to/3g96FGj
○ Boom Arm - amzn.to/3g8cNi6
- SOFTWARE USED --------------------------------
○ Screen recording - Camtasia - chrismenardtraining.com/camtasia
○ Screenshots - Snagit - chrismenardtraining.com/snagit
○ KZread keyword search - TubeBuddy - www.tubebuddy.com/chrismenard
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!
Пікірлер: 33
The blog post of calculated items chrismenardtraining.com/post/calculated-items-in-a-pivottable
Amazing trick changing those numbers to percentage! I've been using the hold Ctrl method for years. Thanks Chris 👍🏼
@ChrisMenardTraining
2 жыл бұрын
You are welcome RW.
Wow! It's like magic. Thanks Chris.
@ChrisMenardTraining
2 жыл бұрын
My pleasure!
Thank you for this, best explanation out of several I've looked at
@ChrisMenardTraining
Жыл бұрын
Awesome. Thank you.
I KNEW there was a way to do this! Thank you very much!! This is a real life saver... ;-)
Great video, love the tips and tricks. Keep 'em coming
@ChrisMenardTraining
2 жыл бұрын
More to come! I LOVE Excel.
Nice, thanks! Now I understand Calculated Items. Thanks
Thank you, this is really helpful!
You are a Star. Thank you so much.
Than you very very much!! This is so helpful and easy to understand!
@ChrisMenardTraining
Жыл бұрын
You're very welcome!
Well explained!. Thanks Chris
@ChrisMenardTraining
Жыл бұрын
My pleasure!
Wow i thought i knew everything about PivotTables... Thanks
@ChrisMenardTraining
Жыл бұрын
You are welcome.
Smart Chris Menard. Thanks a lot. Great tutorial.
@ChrisMenardTraining
Жыл бұрын
You're welcome!
@pipo441
Жыл бұрын
Any examples with more then 2 items ? More the. 2 years?
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
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?
Thank You :)
@ChrisMenardTraining
Жыл бұрын
You're welcome!
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.
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?
How do you make a change to the formula once you created the new item?
But sir When we are doing at Grand total and Subtotal not coming correct
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.
Can u make it in columns?
@ChrisMenardTraining
Жыл бұрын
Yes, I made a video on that recently. When I return from vacation, I'll find it. It was posted within the last two weeks.