I 💓 GETPIVOTDATA and why you should too!

in its simplest form the getpivotdata function enables you to extract values from a pivottable report but if you're like me when you first tried getpivotdata you were less than pleased with the results and understandably so because in its default form it's quite inflexible however the benefit in using get pivot data as opposed to a regular cell reference is huge in terms of reducing your ongoing workload in maintaining your reports and ensuring their accuracy in this video i'm going to show you how to overcome some common frustrations in working with getpivotdata and show you how to handle referencing dates which isn't as obvious as it should be i'll be using regular pivot tables in this tutorial as opposed to a power pivot or data model pivot table and the references to a pivot table based on the data model are slightly different and i've covered them in a separate get pivot data tutorial which is linked to in the card in the top right of the video okay in order to have excel generate getpiv data formulas you must first have the preference turned on so if you've turned it off in frustration you can turn it back on by having the pivot table selected and then on the analyze tab over in options generate get pivot data now before we look at get pivot data i want to show you the source data which is on a separate tab here notice it has a column containing dates called order date and if you look at the pivot table you can see i've grouped those dates by month and year and if we look at the field list which i'll drag over into view you can see the month field is called order date and then we have an extra field for years which is generated when we group the dates you'll see why this is important in a moment now when you reference a cell in the pivot table values area excel automatically inserts a get pivot data formula for you in english this formula reads return the order amount from the pivot table located in cell a4 for the order date 1 which remember is the month so in this case it's january for the year 2009 seems like a big formula for what would usually be just equals cell d7 but remember there are big advantages to using get pivot data the annoying thing with get data or one of them is that when you copy and paste the formula for example if i copy it down column f the references aren't relative that is they don't update like normal cell references to pick up the next cell in the range so you end up with a column of identical values like these so let's look at how we can make getpivotdata formulas dynamic we'll start by making the formula update so that it picks up each month when it's copied down the column the trick here is knowing how pivot tables represent dates while the months appear to be the month names jan feb and march in the pivot table for the purpose of get pivot data those months are actually numbers 1 2 3 through 12. so we need to replace the month number argument in the get pivot data formula with something that will count up automatically as we copy the formula down the column and for this we can use the row function so if i reference cell a1 the row function will return the number one and if i copy it down you can see it counts up 1 through 12. so what we can do is insert this row formula in place of the hard-coded month number in the getpivotdata formula i'm just going to paste that in you can see it returns the same result but now when i copy it down it dynamically updates to pick up february march april may and so on now you might be thinking that's a lot of work when we could have just said equal cell d7 and copied that down and we get the same results but remember the benefits of get pivot data mean a more robust formula able to withstand significant changes to your pivot table without losing its place and that's super important if you have slices connected to the pivot table for example if i edit this pivot table let's go back to the field list and i remove country my pivot table is now only made up of two columns so my order amount is now in column b you can see my get pivot data formula hasn't broken but my basic formula that reference the old order amount total now returns zero now in this example the months are going down the rows but if they were going across the columns you could use the columns function or column i should say and again reference column one and then as we left click and drag you can see it dynamically counts up so that's an alternative if you have your months going across the columns let's say we wanted to get the grand total for each country and toggle between the two using this data validation list here i'll start by letting excel write the get pivot data formula for me by referencing the grand total for the uk then all i need to do is replace the hard-coded reference to the uk with a reference to the cell containing the data validation list you can see it's now picking up the us grand total and as i choose a different country it automatically updates and this is a great alternative to using slices in your reports if you're working with limited space because data validation lists take up a lot less room by the way if we look at the formula notice there aren't any month or year arguments and that's because we're picking up the grand total so all we have is an argument for the country now i usually let excel write my pivot data formulas by typing an equals in a cell and then referencing the cell that i want to pick up this gives me a pre-written formula and all i need to do then is edit it to make it dynamic and that's a lot quicker than writing it out from scratch you can make any of the arguments in get pivot data dynamic not just the examples i've shown you here however you can only use get pivot data to pick up values that are visible in the pivot table report it can't query the source data itself if the value described in the argument isn't present in the pivot table it's going to return the hash ref error of course you could wrap it in if error if you're confident errors are allowed intentionally you can also use it to return values from calculated columns basically you can reference any fields in the values area of the pivot table and just like any other function you can nest it in formulas or apply math logic to it so for example i could increase these values by 10 and now i have my grand total plus 10 do you love it now maybe it's too soon in your relationship with get pivot data but i hope you can see its benefits and you're keen to give it a go you can download the file for this lesson from the link here and if you like this video please give it a thumbs up and subscribe to our channel for more and why not share it with your friends who might also find it useful thanks for watching [Music]

Channel: MyOnlineTrainingHub

Views: 16,481

Rating: 4.9623432 out of 5

Keywords: getpivotdata, excel pivottables, pivottable, getpivotdata function

Id: LDf_ORnSCu4

Length: 7min 28sec (448 seconds)

Published: Thu Sep 09 2021