Understanding the GetPivotData Formula in Excel

A trick to reporting off a Pivot Table using the GetPivotData formula.
If you'd like to support Scouting, my troop is raising money through online popcorn sales. Here is the store - www.prpopcornstore.com/?sid=T....
Get an Income & Expense Tracker Excel Template here: www.etsy.com/listing/10895849...
Get 30% off QuickBooks Online with this link - quickbooks.intuit.com/partner...
Get Rewind Backups for QuickBooks Online - rewindio.grsm.io/hn7nw5voyc5p
Need a new Payroll provider? Go with Gusto - gusto.com/?referral_token=ste...
Looking for a new bank account? Try Relay - app.relayfi.com/register?refe...
Click here to download a comparison guide cheat sheet that lists out the differences between Simple Start, Essentials, Plus and Advanced subscriptions.
1drv.ms/x/s!AmOflzuTYrYwmvc67...
Got questions? Email Steve at [e-mail]
Disclaimer - I offer affiliate links. This may result in commissions being paid to me by 3rd parties which will have no impact on the purchaser.

[@arianohayadi]

thanks, man!
I learned two things from your video:
1. getpivotdata
2. cell locking reference
cheers!

[@user-nx3je3db3i]

Thank you so much! I was reading an Excel tutorial and I could not figure out what's this formula until i saw your video. Thank you again!

[@viknigam6324]

Thanks for letting me know😊.

[@romaissaa5841]

A very simple, straightforward way to understand the function and modify it to your needs. Thank you!

[@MrSpottedMoose]

Thank you Steve! Easiest explanation of getpivots out there. SUBSCRIBED

[@AbhinavSingh-xc7yv]

Thanks Steve. You saved my life.

[@apriljadewild101]

Very straightforward, was trying to teach myself through looking up the formulas on google but this explains it much more simply :)

[@nycollassilva3558]

Dvcdmrw11cjhv

[@nycollassilva3558]

Ddcfscd111dfvmmj

[@manuelmabuki3107]

Thanks. Steve. U save my time

[@kawsarahmed5366]

Thank you so much for sharing your knowledge. This is the exact tutorial that I am looking for. 🙏👌👍

[@SteveChase1]

Wonderful

[@alokjha2000]

Thanks Steve, its really help me to ressolve my issue

[@pieteroberholzer7779]

Thanks, video makes it easy to understand

[@alexrosen8762]

Very useful. Thanks!

[@Alice-gl6um]

omg thank you so much! it helped

[@SteveChase1]

awesome!

[@lifeignite2048]

Thank you so much

[@LIB0406]

Thank you Bro

[@prititiwari6897]

Thank you..

[@Shorts-ek1qw]

So how is it different from just creating a pivot table with the same format used above in the video?

[@MusicPancho]

Thanks!

[@robertrotich3958]

Hi, how do we reference the "Grand Total cells" so that whenever the pivot table expands it returns the values automatically?

[@1stcivilare]

thanks, but if you want to perfect the video record it again and make sure that you have the same distance to the mic all the time, you sound distance sometimes.

[@minabeshir2018]

sounds great, but what if i changed the pivot table values is it change also automatically in the upper written table?

[@SteveChase1]

After you refresh it will update. Pivottable values won't refresh automatically until you either right click and refresh or go to the data tab and select the refresh all button.

[@danielatamayo8856]

What if I don’t want a columns field and therefore there is no ‘sum of sales’ cell

[@talebbagazi1435]

Hi Steve, currently im working on analyzing a complex data with regular formula which is (+,-,/)...etc. i highlight the table and i inserted a pivot table. in the columns i putted years. then, row has customer, project,aging,invoice number.after that, my value contains total payment, total received, and VAT. so far the table its looks good to understand. but when i transfer the data to the new table by clicking =the data from the pivot it gives the correct number but when i try to edited the hard copy its gives me #ref! can you help me to know what to do now ?
i wish i can hearing an answer from you.
Regards
Taleb bagazi

[@SteveChase1]

Maybe you can do copy and paste values?

[@melindam2776]

So Getpivotdata is a fancy Sumifs?

[@feyi_bab9130]

yo bro what do i do when anytime i edit the pivotdata cell just the way you did and click enter, it gives me "ref error"

[@SteveChase1]

Do you have a space throwing it off?

[@sreejx]

Hi Steve if I want to show a combined total for Black and Blue ink for a particular month how can i do that.

[@SteveChase1]

Well there's the sumifs function that could do that. You would have 6 arguments and this can be done outside of the pivottable with the raw data table. Something like this =SUMIFS(sumrange,productcolumn,"black",productcolum,"blue",monthcolumn,"Jan").

[@sreejx]

@@SteveChase1 Thanks Steve for quick response ,I was trying something like this on my dummy data "SUM(GETPIVOTDATA("Qty",A3,"Product",{"Apple","Orange","Pineapple"},"Month","Jan"))" it gives correct value but then even if one row item is null it gives #REF! error,can you think of some fix for this? :)

[@rogeriopenna9014]

Is it my impression or the Pivot Table must be on exactly the same sheet as the formula?? Otherwise Excel creates a stupid fixed reference (and if you change the Pivot Table size, you get errors)

[@azharshaikh3583]

we can use a slicer

[@ariskavanessen7430]

Azhar Shaikh . Yes they are great but my version of excel doesn't have Slicers.

[@shamisocarpenter3218]

What does a slicer have to do with this technique?