Pivot tables are very powerful analysis tools. They can summarize vast amounts of data with just few clicks. But they are lousy when it comes to output. Imagine the horror of putting a pivot table right inside your beautiful dashboard. One refresh could ruin the layout and create half-an-hour extra work for you.
How to combine the power of pivot tables with elegance of your dashboards?
The answer is: GETPIVOTDATA()
What is GETPIVOTDATA?
As the name suggests, GETPIVOTDATA gets pivot table data. The best way to understand GETPIVOTDATA is by looking at an example.
Let’s say, you have a pivot table like the one below. And you want to know what is the Amount for Cust Area = Middle & Prod Category = Biscuits combination.
The below GETPIVOTDATA formula should work.
=GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,”Middle“,”Prod Category”,”Biscuits“)
As you can see GETPIVOTDATA has below syntax.
GETPIVOTDATA(value field name, any cell reference in pivot table, [field name 1, value1, field name 2, value 2 …])
Few more examples of GETPIVOTDATA:
Check out below examples to understand how various parameters of the GETPIVOTDATA function behave:
| GETPIVOTDATA function | What it does | Value |
|---|---|---|
| =GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,”South”,”Prod Category”,”Biscuits”) | Gets Amount for South & Biscuits combination | $609.50 |
| =GETPIVOTDATA(“Amount”,$A$3,”Prod Category”,”Biscuits”) | Gets grand total for Biscuits | $5,251.10 |
| =GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,”South”) | Gets grand total for South | $4,342.20 |
| =GETPIVOTDATA(“Amount”,$A$3) | Gets grand total of all amounts | $41,828.00 |
| =GETPIVOTDATA(“Amount”,$A$1,”Cust Area”,”West”,”Prod Category”,”Snacks”) | Gives an error as $A$1 is not part of the pivot | #REF! |
| =GETPIVOTDATA(“Amount”,$A$3,”Cust Area”,$P$2,”Prod Category”,$P$3) | Gets Amount for cust area = P2 and pro category = P3 cell values. | depends on variables |
| =GETPIVOTDATA(“Amount”,$A$3,”Prod Category”,category_name) | Gets grand total for category = category_name value | depends on variables |
| =GETPIVOTDATA($P$4&””,$A$3,”Cust Area”,$P$2,”Prod Category”,$P$3) | Gets P4 value field for Cust Area = P2 and Prod Category = P3. Note: $P$4 &”” is used to convince GPD that P4 is a string not number. |
depends on variables |
Using GETPIVOTDATA in dashboards
The idea is simple. Since GETPIVOTDATA can be parameterized with variable cells or named ranges, we can use it in dashboards to get relevant data based on user input.
Sample this:
Or this dashboard powered with GETPIVOTDATA
Things to note when working with GETPIVOTDATA:
GETPIVOTDATA is a very useful function, but it does have a few quirks.
- If your pivot table has slicers linked to them, GPD will reflect the results based on slicer selection.
- If your pivot table has any items filtered (say category Biscuits is filtered out), then GPD will return #REF error when you try to get any value corresponding to Biscuits.
- If you change the pivot table structure, your GETPIVOTDATA functions may not work as you expect.
- If you turn off grand totals or sub-totals, you can no longer get them thru GPD.
- GPD requires that your original pivot tables remain intact and visible all the time.
- If you want to completely get rid of pivot tables and still get answers to questions, then you should use CUBE formulas along with Workbook data model feature (more on this in a future post).
- The best & easiest way to write GPD is by pressing = and referencing a cell inside the pivot. This will automatically write the GPD for you. You can then customize the parameters as you need.
- You can turn-off GPD by going to Pivot Table Analyze ribbon tab & unchecking “Generating GETPIVODATA” option from PivotTable options area.
Download GETPIVOTDATA Examples workbook
Please click here to download the GETPIVOTDATA example workbook. Refer to various tabs & formulas to learn more. Don’t forget to play with the dashboard powered by GETPIVOTDATA.
Learn more about Pivot Tables
If you are new to Pivot Tables, it’s high time you started using them. Check out below pages and get started.
- Introduction to Excel Pivot Tables – article , Podcast
- Comprehensive guide to Excel Pivot Tables
- Slicers – Introduction, what are they, advanced scenarios
- Building dashboards with Pivot Tables + Slicers
- Convert regular pivots to GETPIVOTDATA – 3 part tutorial from Mike Alexander, part 2 & part 3
How do you use GETPIVOTDATA?
Let me be honest. For my dashboards, I usually write direct cell references (=A7) instead of GPD. This keeps my formulas short. For dynamic / parameterized setups, I usually write INDEX / MATCH formulas that talk to Pivot Table data. But occasionally I use GETPIVOTDATA because it is very easy to setup and does what it says on the sticker.
What about you? How do you use GETPIVOTDATA? Please share scenarios in the comments section.
21 Responses to “How to Filter Odd or Even Rows only? [Quick Tips]”
Infact, instead of using =ISEVEN(B3), how about to use =ISEVEN(ROW())
So it takes away any chance of wrong referencing.
I like Daily Dose of Excel
I like it.
Just a heads up, you do need to have the Analysis ToolPak add-in activated to use the ISEVEN / ISODD functions. An alternative to ISEVEN would be:
=MOD(ROW(),2)=0
rather than use a formula, couldn't you enter "true" in first cell and "false" in the second and drag it down and than filter on true or false.
Just for clarification, is Ashish looking to filter by even or odd Characters or rows?
so many functions to learn!
Nice support by chandoo and team as a helpdesk. Give us more to learn and make us awesome. Always be helpful.......
In case you want to delete instead of filter,
IF your data is in Sheet1 column A
Put this in Sheet2 column A and drag down
=OFFSET(Sheet1!A$1,(ROWS($1:1)-1)*2,,)
(This is to delete even rows)
To delete odd rows :
=OFFSET(Sheet1!A$2,(ROWS($1:1)-1)*2,,)
If your numbered cells did not correspond to rows, the answer would be even simpler:
=MOD([cell address],2), then filter by 0 to see evens or 1 to see odds.
I sometimes do this using an even simpler method. I add a new column called "Sign" and put the value of 1 in the first row, say cell C2 if C1 contains the header. Then in C3 I put the formula =-1 * C2, which I copy and paste into the rest of the rows (so C4 has =-1 * C3 and so forth). Now I can just apply a filter and pick either +1 or -1 to see half the rows.
Another way, which works if I want three possibilities: in C2 I put the value 1, in C3 I put the value 2, in C4 I put the value 3, then in C5 I put the formula =C2 then I copy C5 and paste into all the remaining rows (so C6 gets =C3, C7 gets =C4, etc.). Now I can apply a filter and pick the value 1, 2, or 3 to see a third of the rows.
Extending this approach to more than 3 cases is left as an exercise for the reader.
Another way =MOD(ROW();2). In this case, must to choose betwen 1 and 0.
[...] How to Filter Even or Odd rows only [...]
very different style Odd or Even Rows very easy way to visit this site
http://www.handycss.com/tips/odd-or-even-rows/
Thanks for the tip, it worked like magic, saved having to delete row by row in my database.
Thanks!
Thankssssssssssssssss
Hi Chandoo- First of all thanks for the trick. It helped me a lot. Here I have one more challenge. Having filtered the data based on odd. I want to paste data in another sheet adjacent to it. How can I do that?
For Example-
A 1 odd
B 3 odd
C 4 even
D 6 even
I have fileted the above data for odd and want to copy the "This is odd number" text in adjacent/next sheet here. How can I do that. After doing this my data should look like this
A 1 odd This is odd number
B 3 odd This is odd number
C 4 even
D 6 even
Hi! Could you please help me find a formula to filter by language?
Thank you!
Chandoo SIR,
I HAVE A DATA IN EXCEL ROWS LIKE BELOW IS THERE ANY FORMULA OR A WAY WHERE I CAN INSTRUCT I CAN MAKE CHANGES , MEANS I WANT TO WRITE ONLY , THE FIG IS FRESH, BUT IN BELOW ROW IT WILL AUTOMATICALLY TAKE THE SOME WORDS FROM FIGS AND MAKE IN PLURAL FORM , WHILE USING '' ARE'' LIKE BELOW
The fig is fresh - row 1
Figs are fresh - row 2
The Pomegranate is red - row 3
Pomegranates are red - row 4
=IF(EVEN(A1)=A1,"EVEN - do something","ODD - do something else") with iferron (for blank Cell)