Pivot Table Issues

How to Extract PivotTable Data into a Separate Excel Table

Muhammad TalhaMuhammad Talha Nov 4, 2026 870 views

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.

How to Extract PivotTable Data into a Separate Excel Table
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.
Before you start

Ensure you have access to the original raw dataset that feeds the PivotTable, as querying the source directly is often the most reliable method.

Solution 1Recommended

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.

1
Locate the raw data

Identify the worksheet or data connection containing the original dataset used to generate the PivotTable.

2
Set up a new table

Open a new worksheet and set up your target table headers, such as 'Dates' and 'Item Quantities'.

3
Apply conditional formulas

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.

Build the Target Table Directly from Source Data
Best Practice: Building reports from raw data rather than PivotTables significantly reduces the risk of reporting errors during subsequent data updates.
Easy Data Management with WPS Office

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. 1. Open your file in WPS: Launch WPS Spreadsheet and open the workbook containing your PivotTable and original source data.
  2. 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. 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.
Fully compatible with Microsoft Excel (.xlsx) formatsIntuitive PivotTable creation and managementBuilt-in support for GETPIVOTDATA and advanced lookup functionsLightweight, fast, and free to use
QA img-9

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.