Thank you Debra for this well explained video! I have a situation where I have a sheet containing tree diameters and tree health, the health are from 1 to 4 and 4 being dead. So what I would like to do is show the counts of dead trees (4) in my pivot table.
You're welcome, Mark, and thanks for your question. In the pivot table, put Health in the Rows area. Then, put another copy of Health into the Values area. Right-click on the value field, click Summarize Values by, click Count. Then, if you only want to see the #4 trees, filter the Row field to show 4 only
Thanks! Very Nice. Can you give more examples of when to use Calculated Items and when to use Calculated Fields that is not very clear to me at this moment? Thanks
Excel limits are on this Microsoft page, and shows "limited by available memory" for many pivot table features. support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
Thank you for the video. Is it possible to create a formula with calculated fields that uses two different columns? For example, if in my pivot table I have a revenue column and an expense column, can I use a calculated field to subtract the expenses from the revenues?
You're welcome, Trevor, and thanks for your question! If the revenue and expense are in different columns in your source data, use a calculated field like this in the pivot table: =Revenue - Expenses
I could not find grand total when i wanted to create calculated item or field within the same item... i need to divide two field items to get a percenatge ... couldnt do it!
Instead of a calculated field, try the Show Values as feature. This video shows % of Parent, but you could use % Of... ruclips.net/video/NZaMVirDXrw/видео.html
Can you suggest how we can sum multiple items when choosing the items to add to arrive at calculated item. In your example: a formula to add all order status less "cancelled" order status. kindly advice. thank you
I have problem to connect timeline to two pivot chart or graph.after connect of timeline to graph one graph working fine but second take multiple dat.Please help it is much need to me .
Select any cell in the pivot table, and new tabs should appear on the Ribbon -- they have different names in some versions of Excel. For example, in Excel 2007, you'd click the Options tab, and then Formulas.
hi...thanks for your explanation its very helpful, how can I use calculated fields when im in "data model pivot"? why im using the data model pivot, its becs i have to eliminate duplicates items in pivot, but in other way I also have to accumulate the data in few columns. looking forward for your adv. Thanks
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.
What about getting a percentage? I have a pivot of the counts of different categories and a grand total. I'd like to get a percentage of calculation in the pivot. So (grand total-missing)/grand total for a percentage of the grand total that are missing. I've been stuck on this one for a while.
This might do what you need - add the same field again, and set it to show the count. Then, right-click on one of the numbers, click Show Values As, and click on % of Column or % of Parent Column. I've got a short video here: ruclips.net/video/NZaMVirDXrw/видео.html
Thanks alot for the answer, it's working now.. But i have another question.. is it posible to place "values" type to the left of "rows" type in vipot table?
@@ibnuamaru You could put the serial number into the Rows area, before the other fields. Change its setting to repeat in all rows, if you want that. Turn off subtotals for serial number, and use Tabular layout
Not only was this extremely helpful, but it only took 5 minutes of my time. Bravo for an informative AND efficient video.
Thank you, Brian, and I appreciate hearing that!
--Debra
This is the first video I see from you, it is short, to the point, and explained very clearly. Thank you!!
You're welcome, Eran, and thanks for letting me know that you like the video!
You are a born teacher Madam. Fantastic!
Thank you, Shankar!
Contextures is a neat website for excel users without endless scrolling. Def one of the sources I check first. Thank you for these great videos.
You're welcome, Güneycan, and thanks for your lovely comment - I appreciate it!
Very useful, thank you
You're welcome, Leo, and thanks for your comment!
Very clear in explanation, thanks!
You're welcome, Chrissa, and thanks for your comment!
This was great helpful to me thanks for this tip and tricks
You're welcome, Murtuza, and thanks for letting me know the video helped you!
Thanks very much..
You're welcome, and thanks for your comment!
Thank you :)
You're welcome, Louis, and thanks for your comment!
Thank you Debra, very well explained.
thanks
You're welcome, Gladwin, and thanks for your comment!
Thanks for the reminder Deb! I sometimes forget about Calculated Items....they are use helpful.
Thanks, Kevin! Excel has so many features that it's impossible to remember them all
Thanks for helpful tips
You're welcome, Aravindh, and thanks for your comment!
- Debra
Hi Debra.. thanks for the tip on calculated Fields and Items and especially the List Formulas choice.. had not used that before. Thumbs up!
You're welcome, Wayne, and glad to show something you haven't used before!
Many thanks Debra!
You're welcome, Zahid, and thanks for your comment!
Thank you, That's all
its awsom description ...must watch....
Thanks a lot. I was looking for this.
Thank you it's useful video.
I have question.whether its possible to use ppm formula for grand total that is showing at the end
My calculated field does not show up in the Field List?
Thanks alot
Thank you Debra for this well explained video!
I have a situation where I have a sheet containing tree diameters and tree health, the health are from 1 to 4 and 4 being dead. So what I would like to do is show the counts of dead trees (4) in my pivot table.
You're welcome, Mark, and thanks for your question. In the pivot table, put Health in the Rows area. Then, put another copy of Health into the Values area. Right-click on the value field, click Summarize Values by, click Count. Then, if you only want to see the #4 trees, filter the Row field to show 4 only
@@contextures Double thumbs up Debra for that help! Worked like a charm!
Thank you Debra :)
Thank you Debra for the effecient video.
I am in a puzzle, Lets say I have columns 1 to 10 and need to calculate only 9&10 is it possible?
You're welcome, Preetham, and the calculated field would apply to all the columns - you can't exclude specific columns
Thanks! Very Nice. Can you give more examples of when to use Calculated Items and when to use Calculated Fields that is not very clear to me at this moment? Thanks
im always getting the error "There are too many records to complete this operation."
For how many lines will the calculated filed work?
Excel limits are on this Microsoft page, and shows "limited by available memory" for many pivot table features. support.microsoft.com/en-au/office/excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3
I can't locate the data file used in this video. The one on the link that I had to search for (should be easier to find) does not match the video.
Thank you for the video. Is it possible to create a formula with calculated fields that uses two different columns? For example, if in my pivot table I have a revenue column and an expense column, can I use a calculated field to subtract the expenses from the revenues?
You're welcome, Trevor, and thanks for your question! If the revenue and expense are in different columns in your source data, use a calculated field like this in the pivot table:
=Revenue - Expenses
I could not find grand total when i wanted to create calculated item or field within the same item... i need to divide two field items to get a percenatge ... couldnt do it!
Instead of a calculated field, try the Show Values as feature.
This video shows % of Parent, but you could use % Of... ruclips.net/video/NZaMVirDXrw/видео.html
Trying to figure out how to edit the formula. I still do not know how to do that. Any idea how to edit a formula we used for a calculated field?
This video shows how to change a calculated field's formula: ruclips.net/video/2n1U1lOplJ4/видео.html
create pivot table from 2 tabs, the calculated field options doesn't work anymore, please help
Can you suggest how we can sum multiple items when choosing the items to add to arrive at calculated item. In your example: a formula to add all order status less "cancelled" order status. kindly advice. thank you
I have problem to connect timeline to two pivot chart or graph.after connect of timeline to graph one graph working fine but second take multiple dat.Please help it is much need to me .
How to display the Analyse tab on ribbon
Select any cell in the pivot table, and new tabs should appear on the Ribbon -- they have different names in some versions of Excel. For example, in Excel 2007, you'd click the Options tab, and then Formulas.
How to add calculation field base on add to value field
Hi
How to subtract east total from west total?
It shows group error. Please help me out to solve it
hi...thanks for your explanation its very helpful, how can I use calculated fields when im in "data model pivot"? why im using the data model pivot, its becs i have to eliminate duplicates items in pivot, but in other way I also have to accumulate the data in few columns. looking forward for your adv. Thanks
How can I add Slicer in Calculated Field in Pivot Table
Grand total row does not show correct figure of growth%. It sums the column items.. How to get it right???
❤❤❤❤
Thank you, Nazar!
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.
🤯
What about getting a percentage? I have a pivot of the counts of different categories and a grand total. I'd like to get a percentage of calculation in the pivot. So (grand total-missing)/grand total for a percentage of the grand total that are missing. I've been stuck on this one for a while.
This might do what you need - add the same field again, and set it to show the count. Then, right-click on one of the numbers, click Show Values As, and click on % of Column or % of Parent Column. I've got a short video here: ruclips.net/video/NZaMVirDXrw/видео.html
my calculated field/items menu is disabled (not clickable), why is that?
If you checked the "add to data model" box, when creating the pivot table, those commands aren't available, because the pivot table is OLAP-based.
Thanks alot for the answer, it's working now..
But i have another question.. is it posible to place "values" type to the left of "rows" type in vipot table?
If it's not posible, do you have another trick to add serial number column on the left of "rows" type?
@@ibnuamaru No, the values are always to the right of the rows
@@ibnuamaru You could put the serial number into the Rows area, before the other fields. Change its setting to repeat in all rows, if you want that. Turn off subtotals for serial number, and use Tabular layout