I have been doing almost the same as the xlookup method with a simple sumif formula ... I type Grand Total at the top and do the sumif formula looking for those words. Thank you for showing 4 additional options
I am glad you posted a video on my query. I was using 1st approach. But now i knew 4 approaches. Thanks lot for your time and efforts and sharing with us information package. Take Care Ali Sho ( Shoaib Rehman)
Nice trick thanks . I have one more doubt that how we can get the some conditional formatting for colours will display automatically for additional added data in excel sheet.
Thanks Chandoo for the great job, as always. I noticed however the cube formula will show Grand Total even when slicers are activated, while the GetPivotData doesn't have this fault and shows the filtered result. Is this how it ought to be, or did I neglect some fine tuning in the settings?
You can put below SUM formula to get the grand total value and it would be dynamic as well. =SUM(RANGE)/2 Another formula that I use to get the last value is =LOOKUP(2,1/(RANGE),(RANGE))
Hello Chandoo. Thank you for creating such videos. One question for you - there are 2 different pivots (product item cost and product sales amount), linked with some relation. There is one slicer linked to both pivots. Now I want to calculate the difference of the amount of any product ( sale amount - item cost) in a different cell number( not inside any of the pivot). . Can you suggest if there is a way ?
You are welcome :) You can just write a formula in the cell by pointing to individual pivot values. Something like =B4-G4 where b4 is the sales amount pivot value and G4 is the cost pivot value.
@@chandoo_ thank you . If I am using a slicer for different product item and then using this formula to find the difference of a single product . Would this be still working ? Since I believe when I use the slicer the values in cell B4 and G4 will vary everytime I choose a different product. I tried this actually..but didn't worked. My whole idea is that when I am selecting any product from slicer , then the formula in a different cell ( say C10), should automatically calculate the difference from the grand total of both the pivots. I hope u get my point. I would be grateful for ur suggestions. Thank you in advance. 😊
If the slicer is linked to both pivots and they update when you click on it, then the calculation in C10 should update too. If it is not, then either your formulas are set to manual mode or something else must be wrong. Please check.
Hi Chandoo.. could you pls tell us how to get grand total of any column by applying filters of another column... That grand total cell can be in one place - either above the first row or last.
This guy explains nicely , must support our Indian Excel expert.
:)
Dear Chandoo, Thanks for valuable tricks.
Kindly upload video on cube formula.
Hey Lalit... sure buddy. That one is on cards.
They are always very useful. Must watch !
I have been doing almost the same as the xlookup method with a simple sumif formula ... I type Grand Total at the top and do the sumif formula looking for those words. Thank you for showing 4 additional options
Thanks for sharing!
4th one was awesome
I am glad you posted a video on my query. I was using 1st approach. But now i knew 4 approaches. Thanks lot for your time and efforts and sharing with us information package.
Take Care
Ali Sho
( Shoaib Rehman)
Most welcome!
Sir where can i ask questions and realtime issues on your forum.
Pls share a link.
Hi Ali... I do not have such a support setup. It is not feasible. I suggest searching online or using stack overflow for help on questions :)
My mean was forum. Now i have access on it. Thanks
Great video Chandoo. Thanks!
Wow Chandoo, you are a GURU, amazing, good stuff
Thank you Rodney...
Amazing, well done cube will do my job. Well done 👍👍
Thanks to your video I managed to do also Grand Total at top using 3 slicers 👏🏼🏆
Nice work!
Learnt new option with Cube formula
Thanks Samir... :)
You make my day sir
Lots of Love from India
Thanks Dev... 😊😍
Thanks! Sir
Amazing ... thanks for sharing
My pleasure 😊 Faraz... :)
Vera level anna Mass pantringa 🔥🔥🔥🔥🔥
Chandoo, thanks for the video. It is very useful. You could also vlookup for "Grand Total" and return value at right,
Yes, you are right
This might not work if the workbook is opened by someone with other language settings.
Superb...
Thanks Santosh...
Thank you. very inspiring.
You are so welcome!
Thanks for sharing
Good one sir. Please share a video with more info on cube formulas.
Thanks Santosh... I will share a video with CUBE formula intro soon.
Nice trick thanks .
I have one more doubt that how we can get the some conditional formatting for colours will display automatically for additional added data in excel sheet.
Thanks Chandoo, I rather the four. I hope you want to make cube formulas video. Bye.
Coming soon!
I am using vlookup for showing Grand total in top
Thanks Chandoo for the great job, as always.
I noticed however the cube formula will show Grand Total even when slicers are activated, while the GetPivotData doesn't have this fault and shows the filtered result. Is this how it ought to be, or did I neglect some fine tuning in the settings?
Hi Max... The grand total row in CUBE formulas removes any filter conditions. You can add the condition again and it should work.
@@chandoo_ How to add slicer condition in Cube functions?
Thanks but it is not working with slicers with power pivots. When I select any value in slicer.. the Result is not dynamic.
You can put below SUM formula to get the grand total value and it would be dynamic as well.
=SUM(RANGE)/2
Another formula that I use to get the last value is
=LOOKUP(2,1/(RANGE),(RANGE))
Great suggestions Kamran...
May be - vlookup("grand total"....
Hello Chandoo. Thank you for creating such videos. One question for you - there are 2 different pivots (product item cost and product sales amount), linked with some relation. There is one slicer linked to both pivots. Now I want to calculate the difference of the amount of any product ( sale amount - item cost) in a different cell number( not inside any of the pivot). .
Can you suggest if there is a way ?
You are welcome :)
You can just write a formula in the cell by pointing to individual pivot values. Something like =B4-G4
where b4 is the sales amount pivot value and G4 is the cost pivot value.
@@chandoo_ thank you . If I am using a slicer for different product item and then using this formula to find the difference of a single product . Would this be still working ? Since I believe when I use the slicer the values in cell B4 and G4 will vary everytime I choose a different product.
I tried this actually..but didn't worked. My whole idea is that when I am selecting any product from slicer , then the formula in a different cell ( say C10), should automatically calculate the difference from the grand total of both the pivots. I hope u get my point. I would be grateful for ur suggestions. Thank you in advance. 😊
If the slicer is linked to both pivots and they update when you click on it, then the calculation in C10 should update too. If it is not, then either your formulas are set to manual mode or something else must be wrong. Please check.
@@chandoo_ Sure I will check it. Is " Getpivotdata" option should be checked or unchecked in this ?
Uncheck it as with GetPivotData, you won't get the true cell values.
Hi Chandoo.. could you pls tell us how to get grand total of any column by applying filters of another column... That grand total cell can be in one place - either above the first row or last.
Hmm.. not sure I follow. When you filter, by default Pivot will show grand total for that data alone.
There is also one more way, 5th - Power Query + PowerPivot + DAX
Sandeep Kothari
Dear Chandoo, Also show us how to do same with macros.
Sandeep Kothari
Is the scheduled time 13:00 in IST (14 Jul 2020)?
why I can't use olap tools for average of Sales. It's gonna have N/A after that
They only work on data model based pivots.
Using of cube formula can the size of excel reduce when compared with pivot table
Hi Bharathram... I would think so as there is no pivotcache involved. You can check with two versions of the file post the outcome.
Hello @Chandoo,
We can use below formula in CELL REF and it will be gives more accurate result.
=VLOOKUP("Grand Total",B4:C35,2,0)
Yes, the vlookup option is the best and easiest method.