Forum Discussion
Slicer
Hi Expertises,
Need your help again. I have pivot table as below. I need to capture the Product A sales amount under the following scenarios.
- Filter. Select any one country in slicer
- NO Filter. Select all country
My problem is when I add formula in text box like =B2, under scenario 1, the answer is correct no matter I select which country in slicer. However, for scenario 2, when i did not use filter, the total of Product A Sales amount should refer to B6 instead of B2. So, i got incorrect answer.
May i ask your advice in this case? Thanks a lot
Regards
Lawrence
| Country | Product A Sales | Product B Sales |
| AU | 100 | 40 |
JP | 80 | 60 |
| HK | 60 | 80 |
| KR | 40 | 100 |
| Total | 280 | 280 |
2 Replies
Use GETPIVOTDATA...
This function references PivotTable values by their labels, not by cell coordinates — so it works regardless of filter state.
=GETPIVOTDATA("Product A Sales", $A$1)
- "Product A Sales" – the value field name
- $A$1 – any cell inside your PivotTable (e.g., top-left corner)
Important: This returns the filtered total automatically.
- If one country is selected → shows that country’s Product A Sales
- If all countries are selected → shows Grand Total for Product A Sales
My answers are voluntary and without guarantee!
Hope this will help you.
Hello Lawrence_Lam_320,
The issue is happening because =B2 is a fixed cell reference, while the PivotTable layout changes depending on the slicer selection.
Instead, use GETPIVOTDATA.
Example:
=GETPIVOTDATA("Product A Sales",$A$1)
Where $A$1 is any cell inside the PivotTable.
This will return the Product A Sales value for the selected Country when a slicer filter is applied, and the Total Product A Sales when all Countries are selected.
You can also type = and click the PivotTable value cell directly. Excel will automatically generate the GETPIVOTDATA formula for you.
