Get Data from your Pivot Table with the GETPIVOTDATA Function | Google Sheets
ฝัง
- เผยแพร่เมื่อ 25 ก.ค. 2024
- Learn how to use the GETPIVOTDATA function to extract the values from your pivot table. This tutorial will walk you through the steps to pull out the numbers you want, and the function will not break when you move the pivot table values around.
Comprehensive pivot table tutorial video: • Google Sheets Pivot Ta...
#googlesheets #pivottables #prolificoaktree - วิทยาศาสตร์และเทคโนโลยี
This helped with my EXACT problem I had for an HOUR! Now my Google Sheet Personal Financial Statement is complete for the new year 😊 thank you!
Good luck in 2023!
Thank you for this video. It helped me with exactly what I needed.
Awesome help!
what if i just want to find total quantity of "online", regardless product?
What if you need to get the total? For example Total Fish Food in your sample pivot table?
=GETPIVOTDATA("Total dollars",$D$4,"Product","Fish Food")
How to return row data, not value column(s) data
I have this pivot table same as yours but instead of price, the values are words. How can I get the value of the cell in the pivot data?
Thanks in advance
You can count the words with COUNTA.
Hi, thanks for the video! I have a problem when a use dates as a pivot item. I already found a way (not a good one) to solve this problem: duplicate all date columns and set them as text, as well as the cell reference.
For example:
A1 = 01/10/2023 (mm/dd/yyyy format)
A2 = TEXT(A1,"#")
And them a use the cell A2 as reference.
Do you know any other way to do this?
Thanksss and regards from Brazil.
You've gotta make them valid dates. Run the -ISNUMBER function on them or use Data validation.
I am attempting to start from the most basic extract from a more elaborate Pivot Table that uses custom labels (as yours does), as well as calculated fields, etc. I am unable to get the most elementary form of GetPivotData to work:
=gpd("Minors", ), where "Minors" is a relabeling of "SUM of Minors". This field is visible in the table, yet returns a REF error, saying "Field combination not found in pivot table for function GETPIVOTDATA".
Same behavior whether I reference the name of the field from a reference to the text in the table itself, or a string literal.
I am presenting my values as Rows rather than Columns. I am also applying various Filters.
By the documentation, this should just give an unqualified total, but gives an error instead. What is the issue?
There's too much that could be going on for me to harbor a great guess, but maybe try to flip the arguments on the function around if the table is flipped???
swapping the arguments would give a type mismatch error. Second argument is a reference, not a string
I was wondering if you had any knowledge about more comprehensive documentation and troubleshooting for the function. The Google reference page necessarily does not address why this doesn't work, and I was hoping to not have to deconstruct the whole stinking table to find out where it breaks down.
Even then, it wouldn't solve the problem, only point to something for me to report to Google to be ignored. Sheets is replete with features that don't behave according to their own documentation, was hoping someone ran into something like this before.
Thanks for the video. However the way this functionality was created by Google is to me mind blowing how bad they made it. One would have thought that Google with all it´s resources and money would have come up with a better way of doing the GETPIVOT reference without having to type it manually (in Excel you just click anywhere in a pivot, the formula is created, and you just change the "FIELD" to cell references). And the fact that the formula is an actual cell reference (i.e. it still has to be within the actual pivot table range), that is just crazy in my opinion. Then what is the point having the GETPIVOT formula in the first place if it STILL has to be referred to e.g. $D$5 or similar? Google sheets still prove to me to be just a massive disappointment and like 20 years behind Excel in functionality.
Thanks for watching the video and your feedback. How would you do it better? In my opinion, I don't like how Excel creates the formula automatically as it makes it harder to work with if I just need a cell reference. As far as the cell reference to denote the pivot table, my guess is it's that way for backwards compatibility but also because Pivot Tables don't have names so I'm not sure how else you would reference them. It's as if they should create a totally new method and keep GETPIVOTDATA around for compatibility reasons.
@@ProlificOaktree I´m not sure I could do it better, I just meant I found the google sheet way of doing pivot formulas 20+ years behind excel. I watch your video searching for quick tutorials on pivot tables in google sheets, since my current employer prefers that to excel - something I still struggle to understand (being a very advanced excel user for many years). My point I guess was that in google sheets, you have to spend time to actually manually type the entire formula yourselves, as well as knowing (crazy) how big the pivot table is or WILL be in the future - i.e. if you update the raw data with 10,000 new rows of data... you have to manually extend your pivot formula range? That I find crazy if so.
In excel you click literally anywhere in the pivot and get e.g.; =GETPIVOTDATA("Revenue ",$A$3,"Market","England","Date",DATE(2020,1,5))... were it clearly lays out what you are looking at.Then you simply open that formula and change e.g. "England" to a cell reference where you may have a list of countries already written in a retrieval sheet. Takes 10 seconds to build and no need to ever touch or "extend" the formula ever again.
@Mattias You don't have to update GETPIVOTDATA when the size of the pivot table changes. Just specify the cell in the upper left the first time. If you are very advanced in Excel though, you will find that Google Sheets does not allow for nearly as many advanced features for sure.