How to Extract PivotTable Data into a Separate Excel Table
Question details
The user needs to extract specific item quantities and corresponding dates from an existing PivotTable into a new table, as standard lookup formulas are failing to return the correct data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to build a separate reporting table based on aggregated PivotTable data.
- Observed behavior
- Standard VLOOKUP and HLOOKUP formulas do not provide the required results because dynamic PivotTable structures break static cell references.
Ensure you have access to the original raw dataset that feeds the PivotTable, as querying the source directly is often the most reliable method.
Build the Target Table Directly from Source Data
Bypassing the dynamic PivotTable to query the raw data prevents formulas from breaking when the PivotTable layout changes.
PivotTables are dynamic, meaning their cell references shift when data is refreshed or groupings change. Using standard lookup formulas directly on a PivotTable often leads to broken links or incorrect values. Extracting the metrics directly from the raw dataset ensures stability and accuracy.
Identify the worksheet or data connection containing the original dataset used to generate the PivotTable.
Open a new worksheet and set up your target table headers, such as 'Dates' and 'Item Quantities'.
Use functions like SUMIFS, COUNTIFS, or INDEX/MATCH directly on the raw data columns to pull the specific metrics into your new table without relying on the PivotTable.

Use Power Query to Transform Data
Ideal for reshaping data automatically without manually writing complex lookup formulas.
Use the GETPIVOTDATA Function
If you must extract directly from the PivotTable, GETPIVOTDATA safely retrieves aggregated data regardless of layout changes.
Seamlessly Manage PivotTables with WPS Spreadsheet
WPS Spreadsheet offers powerful data analysis tools, including advanced PivotTables and GETPIVOTDATA support, making it simple to organize, extract, and analyze your data without formatting issues.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your PivotTable and original source data.
- 2. Extract using GETPIVOTDATA: In a new sheet, type '=' and click on the desired PivotTable value to instantly generate an extraction formula that adapts to changes.
- 3. Build a new PivotTable: Alternatively, go to the Insert tab, select PivotTable, and use your raw data to build a custom secondary table for your specific reporting needs.

Frequently Asked Questions
Why do VLOOKUP and HLOOKUP fail on PivotTables?
VLOOKUP and HLOOKUP rely on fixed grid coordinates. Because PivotTables are dynamic and frequently change size or shape when refreshed, standard lookup formulas easily pull data from the wrong cells.
Can I link a normal Excel table directly to a PivotTable?
Yes, you can link cells using the GETPIVOTDATA function. This function searches for data based on field names rather than static cell locations, making it resilient to layout changes.
Is it better to pull data from the PivotTable or the source data?
It is generally best practice to extract data directly from the original source data using functions like SUMIFS or Power Query, as it provides greater flexibility and stability for secondary reporting.




