Excel Link Data to a PivotTable: How It Works

How-to basics · Updated November 19, 2026

Yes. To link worksheet data to an Excel PivotTable, select a clean source range or Excel Table, choose Insert > PivotTable, and build the summary from its fields; after the source changes, refresh the PivotTable. If you mean linking a separate report cell to a PivotTable result, use a direct cell reference or Excel’s GETPIVOTDATA function.

What “link data to a PivotTable” can mean

The phrase can describe two different connections. The first is the connection from the underlying records—such as orders or expenses—to a PivotTable that groups and summarizes them. This is the usual meaning: the PivotTable reads a source, stores a summarized view, and can be refreshed to pick up changes. It is not a live cell-by-cell mirror of the source.

The second meaning is linking a formula or another worksheet to a value already displayed in the PivotTable. For example, a dashboard cell might show sales for one region. That is a separate formula connection to the PivotTable’s output. Keeping these meanings distinct helps explain why editing a source row may not immediately change the displayed summary, and why a formula that points to a PivotTable result is not the same as adding new records to its source.

Prepare the source data first

A PivotTable works best with a rectangular list: one header row followed by records, with one type of information in each column. A sales list might have Order Date, Region, Product, Quantity, and Sales columns. Each row should represent one order line, and each header should be unique and nonblank.

Before creating the PivotTable, check the list for blank rows or columns inside the data, merged cells, inconsistent labels, and numbers stored as text. For instance, “West” and “west ” may be treated as different categories. A Sales column containing a mix of numeric amounts and text such as “unknown” may be counted instead of summed. Correcting those issues at the source usually produces a more reliable summary than trying to repair the PivotTable afterward.

For data that will grow, convert the range into an Excel Table: select a cell in the list and choose Insert > Table, confirm that the table has headers, and select OK. A Table expands when records are added directly below it and provides a dependable source for a PivotTable. It does not, by itself, make the PivotTable recalculate as soon as data changes; you still need to refresh the PivotTable.

Create a PivotTable from worksheet data

In Excel for Microsoft 365 on Windows, click a cell in the source list or Table, then choose Insert > PivotTable. In the dialog, check the Table/Range entry, choose New Worksheet or Existing Worksheet, and select OK. If you use Existing Worksheet, select an empty destination cell so the PivotTable has room to expand. Excel opens the PivotTable Fields pane.

  1. Drag a category such as Region to Rows to create one row for each region.
  2. Drag a field such as Sales to Values to summarize its amounts.
  3. Drag a date or another category to Columns if you want a second grouping across the top.
  4. Use Filters for a field that should filter the whole report without becoming a row or column label.

Excel commonly sums numeric fields and counts text fields, but the selected calculation can depend on the source values. To change it, right-click a value in the PivotTable, choose Summarize Values By, and select an option such as Sum, Count, or Average. You can also open Value Field Settings from the field’s menu in the Values area. For dates, Excel may offer grouping such as months or quarters; the underlying date values still need to be valid dates.

On Mac, the same basic workflow applies through Insert > PivotTable, though dialog layout and ribbon labels can vary slightly by Excel release. In Excel for the web, PivotTable creation and editing are available, but some advanced source, connection, or data-model features may require the desktop app.

Use a Table when the source will grow

A PivotTable created from a fixed cell range, such as A1:E500, may not include records later added below row 500. A Table is usually the simplest way to avoid that boundary problem: when you append a record to the Table, the Table’s source includes it, and a PivotTable based on that Table can include it after refresh. This is especially useful for monthly reports where new rows are added repeatedly.

To check or change a PivotTable’s source in Windows, select the PivotTable and choose PivotTable Analyze > Change Data Source. The dialog shows the current range or Table name. If you switch a report from a fixed range to a Table, make sure you choose the intended source and then refresh. Changing the source can also affect field names and calculated items, so review the report after making the change.

A Table does not solve every data-design issue. If new records are pasted with missing columns, mismatched headers, or formulas that do not fill down, refresh cannot invent the missing values. Treat the source list as the authoritative record and validate appended data before relying on the updated summary.

Link cells to PivotTable results with GETPIVOTDATA

If a dashboard or separate worksheet needs a particular summarized value, a regular reference such as =B5 can work, but it can point to the wrong result if the PivotTable layout changes. GETPIVOTDATA requests a value by its data field and item labels, so it can remain tied to a particular intersection even when the PivotTable is rearranged. Excel may create this formula automatically when you type an equals sign and click a PivotTable value; this behavior can be switched through PivotTable Analyze > Options.

Assume the source Table has Region, Product, and Sales columns, and the PivotTable begins at cell A3 with Sales in its Values area. If West / Desk totals 1,200, enter this formula in a report cell to retrieve that intersection: =GETPIVOTDATA("Sales",$A$3,"Region","West","Product","Desk"). The result is 1,200. The reference $A$3 must point to a cell inside the PivotTable, and the field and item names must match the PivotTable. If the requested item is absent or the anchor is outside the PivotTable, the formula may return an error.

GETPIVOTDATA is useful for a fixed set of report metrics, but its text criteria are literal in this example. You can replace the item text with cell references when users need to choose a region or product from cells. A simple cell reference to the PivotTable may be quicker for a one-off link, but it depends more directly on where the displayed result sits.

Refresh the PivotTable when its source changes

Editing source records does not necessarily update an existing PivotTable immediately. Select the PivotTable and choose PivotTable Analyze > Refresh, or right-click inside it and choose Refresh. To refresh multiple workbook connections and PivotTables, choose Data > Refresh All. On Windows, Alt+F5 refreshes the active PivotTable, while Ctrl+Alt+F5 refreshes all; shortcut availability can depend on keyboard and system settings. On Mac, use the ribbon or context menu if those Windows shortcuts do not apply.

For a PivotTable based on a Table, refresh after adding or correcting records so the summary reads the updated source. If you use formulas in the source, Excel must first calculate those formulas; then refresh the PivotTable. For external sources, refresh may also require access to the file, database, or connection and may be subject to credentials or permission settings.

You can ask Excel to refresh a PivotTable when the workbook opens. Select the PivotTable, open PivotTable Analyze > Options, and use the Data tab in PivotTable Options to enable Refresh data when opening the file. This is helpful for reports that should update on opening, but it does not guarantee that an external source is reachable or that a refresh will succeed without the required access.

Connect to external or multiple sources

A PivotTable can use sources beyond a list on the current worksheet, including another workbook or a supported external data connection. The available choices depend on the source and Excel version. For recurring imports, Power Query can connect to files or databases and shape the records before they feed a PivotTable. In Microsoft 365 for Windows, the Power Query tools are generally found on the Data tab under Get & Transform Data.

When a report needs relationships between multiple tables—for example, orders linked to a separate product list—adding the tables to the Data Model can be more appropriate than combining them manually into one flat list. The Data Model supports related tables and measures, but it introduces extra setup and is not necessary for a simple one-table summary. Features and dialog options can differ across Windows, Mac, and web editions, so check that the needed connection or model workflow is supported in the edition used to maintain the workbook.

Common problems and how to fix them

  • New records do not appear: Refresh the PivotTable, then check whether its source is a Table or a fixed range that stops before the added rows.
  • Numbers are counted instead of summed: Inspect the source column for text values, blanks, or mixed types. Correct the data and refresh, then choose Sum in Value Field Settings if appropriate.
  • A field is missing: Confirm that the source has a nonblank header for that column and that the PivotTable source includes it. If you changed the source structure, use Change Data Source and refresh.
  • Labels split into unexpected groups: Look for extra spaces, inconsistent spelling, or different data types in the source. Standardize the values and refresh.
  • The PivotTable overwrites nearby content: Move it to a clear area or a new worksheet. PivotTables can grow when new categories appear, so leave space around them.
  • GETPIVOTDATA returns an error: Check that the anchor cell is inside the intended PivotTable and that field names and item labels match the current report.

Refreshing updates the summary; it does not necessarily repair a malformed source or change the PivotTable’s layout choices. When results seem wrong, inspect the source records, the source boundary, and the Values calculation before assuming Excel has lost the link.

Choose the right linking method

Use a PivotTable when the goal is to group many records and summarize them by categories such as month, region, or product. Use a Table as the source when the list will expand. Use GETPIVOTDATA when a report needs a named summary value from the PivotTable, and use a direct cell reference when a fixed position is intentionally part of the design.

For a small, fixed report, formulas such as SUMIFS may be easier to audit than a PivotTable, especially when the output layout must remain exactly the same. For repeatable imports and cleanup, Power Query can reduce manual preparation. For multiple related tables, consider the Data Model. These approaches can complement PivotTables, but they solve different problems: a formula does not automatically create the PivotTable’s interactive grouping, and Power Query prepares data rather than serving as a replacement for every PivotTable feature.

Frequently Asked Questions

Can a PivotTable update automatically when I change a cell?
Usually, you must refresh it after a source change. A PivotTable based on an Excel Table can include newly appended rows after refresh, but the Table does not force the PivotTable to recalculate immediately. You can enable refresh on workbook open in PivotTable Options.

How do I add new rows to an existing PivotTable?
Add the records inside or directly below the source Excel Table, then refresh the PivotTable. If the source is a fixed range, use PivotTable Analyze > Change Data Source to extend the range, or convert the source list to a Table for future additions.

Can I link one PivotTable to another?
You can use formulas such as GETPIVOTDATA to retrieve values from one PivotTable for another report. A second PivotTable usually has its own source and refresh behavior; it is not automatically a live copy of the first PivotTable’s displayed layout.

Why does Excel count my amounts instead of summing them?
The source column likely contains at least some values Excel treats as text, or its data types are inconsistent. Correct the source values, refresh the PivotTable, and set the field’s summary to Sum if the values are numeric.

Can I use formulas as PivotTable source data?
Yes. A source column can contain formulas, but the PivotTable summarizes the calculated results rather than the formula text. Let Excel recalculate the source formulas and then refresh the PivotTable to bring the updated results into its summary.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.