How to Display Excel PivotTable Values in Shapes
Question details
The user wants to dynamically retrieve and display values from a PivotTable directly inside a shape to create dashboard data cards.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Creating interactive dashboards where shapes are used as visual data cards that need to reflect changing summary data from PivotTables.
- Observed behavior
- Excel shapes cannot directly query or retrieve PivotTable values on their own; they require an intermediary linked cell to display dynamic data.
Ensure your PivotTable is fully configured, appropriately filtered, and refreshed so that the values you intend to display are accurate and finalized.
Use a Helper Cell with GETPIVOTDATA to Link the Shape
This is the standard and most reliable built-in workaround. By extracting the PivotTable data into a standard worksheet cell, you can easily link your shape to that cell.
Excel shapes are designed to display the contents of a specific cell reference (like A1) but cannot process complex formulas or natively understand PivotTable fields. The GETPIVOTDATA function bridges this gap.
Select a blank cell on your worksheet (outside the PivotTable area) and type an equal sign (=).
Click on the specific value inside your PivotTable that you want to display. Excel will automatically generate a GETPIVOTDATA formula for you. Press Enter.
Navigate to the Insert tab on the ribbon, click on Shapes, and draw the shape (e.g., a rectangle or circle) you want to use as your data card.
Select the newly created shape, click inside the Formula Bar at the top of the Excel window, type '=', click on your helper cell, and press Enter.

Use Minimal PivotCharts as Data Cards
If linking dozens of individual helper cells is too time-consuming, you can create stripped-down PivotCharts that mimic the appearance of standard shapes.
Create Dynamic Data Dashboards in WPS Spreadsheet
WPS Office provides robust support for PivotTables, the GETPIVOTDATA function, and advanced shape linking. You can seamlessly execute the helper-cell workaround to build beautiful, dynamic dashboards without any hassle.
- 1. Open your file: Launch WPS Spreadsheet and open your document containing the PivotTable.
- 2. Set up a helper cell: Click a blank cell, type '=', and click the target value in your PivotTable to generate the GETPIVOTDATA formula.
- 3. Insert a shape: Go to the Insert tab, click Shapes, and draw a shape on your spreadsheet.
- 4. Link the cell to the shape: Select the shape, click into the formula bar, type '=', select your helper cell, and hit Enter.

Frequently Asked Questions
Why do I need a helper cell to show PivotTable data in a shape?
Excel shapes are programmed to display the contents of a specific, static cell reference (like A1) or a defined named range. They cannot directly parse functions like GETPIVOTDATA or query PivotTable fields natively.
What happens to the shape if my PivotTable layout changes?
As long as you use the GETPIVOTDATA function in your helper cell, the formula will accurately track the specific data point even if the PivotTable expands, shrinks, or gets rearranged. The shape will automatically update to reflect the correct value.
Can I batch-link multiple shapes to different PivotTable values at once?
No, you must link each shape individually to its respective helper cell via the Formula Bar. There is currently no built-in batch process in Excel for linking multiple shapes simultaneously.
Why does my helper cell return a #REF! error when I click the PivotTable?
This happens if the field you clicked is hidden or collapsed within the PivotTable, or if the 'Generate GetPivotData' setting is turned off. Ensure the value is visible in the PivotTable and that GETPIVOTDATA is enabled in the PivotTable Analyze options.




