Pivot Table Issues

How to Link an Excel PivotTable to Word Using a Dynamic Range

Khadija Khan Oct 9, 2026 869 views

Question details

The user needs to link an Excel PivotTable to a Word document in a way that allows the linked object to expand or contract automatically when the PivotTable size changes.

How to Link an Excel PivotTable to Word with a Dynamic Range

Product: Excel, Word
Device & OS: not provided
Scenario: Embedding or linking dynamic Excel reporting data into a Word document for automated report updates.
Observed behavior: A standard named range creates a fixed reference that does not resize, causing cut-off data or empty rows in Word when the PivotTable data updates.

Before you start

Ensure both your Excel workbook and Word document are saved on your local drive before creating the link, as linking between unsaved or cloud-only temporary files may cause broken references.

Solution 1Recommended

Use a Dynamic Named Range in Excel to Link to Word

Define a dynamic range using Excel formulas that adjusts to your PivotTable's size, and then paste that dynamically named range into Word as a linked object.

A PivotTable cannot be directly referenced as a standard Excel Table in the Name Manager. By using formulas like OFFSET and COUNTA, you can create a named range that dynamically calculates the boundaries of your PivotTable. When this dynamic range is linked into Word, it will expand or shrink accurately.

Create a Dynamic Named Range

In Excel, go to the Formulas tab and click 'Name Manager'. Click 'New', enter a name (e.g., 'DynamicPivot'), and in the 'Refers to' box, input an OFFSET and COUNTA formula that encompasses your PivotTable area.

Select and Copy the Dynamic Range

Click the Name Box (located to the left of the formula bar), type the name you just created ('DynamicPivot'), and press Enter to select the area. Press Ctrl+C to copy the range.

Paste Special in Word

Open your Word document, place your cursor where you want the PivotTable to appear. Go to the Home tab, click the arrow below 'Paste', and select 'Paste Special'.

Establish the Link

In the Paste Special dialog, select the 'Paste Link' radio button, choose 'Microsoft Excel Worksheet Object' from the list, and click OK. The link is now dynamic.

Use a Dynamic Named Range in Excel to Link to Word

Refresh and Test: Always test the connection by modifying the source data, refreshing the PivotTable in Excel, and verifying that the linked object in Word adjusts its dimensions automatically.

Solution 2

Link the Entire Worksheet as an Alternative

If defining a dynamic range using formulas is too complex, you can dedicate an entire worksheet to the PivotTable and link the whole file into Word.

Efficient Document Linking

Easily Link Spreadsheets to Documents with WPS Office

WPS Office offers seamless integration between WPS Spreadsheet and WPS Writer. You can effortlessly copy and paste dynamic data ranges, ensuring your reports stay up to date without manually adjusting cell references.

1. Define the Range in WPS Spreadsheet: Open your workbook in WPS Spreadsheet, navigate to the Formulas tab, and use the Name Manager to define your dynamic range.
2. Copy the Range: Select the newly created dynamic range using the Name Box and press Ctrl+C to copy.
3. Paste Special in WPS Writer: Open your document in WPS Writer, click the Paste dropdown on the Home tab, and select Paste Special.
4. Paste as Link: Choose 'Paste Link' and select 'WPS Spreadsheet Object' to insert a dynamically updating table.

Seamless data linking and object embedding between WPS Spreadsheet and WriterFully compatible with Microsoft Excel (.xlsx) and Word (.docx) formatsAdvanced formula support for creating dynamic named ranges easilyLightweight application with a highly familiar user interface

microsoft office alternative - wps office

Frequently Asked Questions

Why does my linked PivotTable get cut off in Word when the data expands?

This happens because standard named ranges or static cell selections create a fixed reference. When the PivotTable grows, the newly added rows or columns fall outside this fixed boundary, causing Word to only display the originally selected cells. Using a dynamic named range solves this issue.

What Excel formulas are needed to create a dynamic named range?

Typically, you use a combination of the OFFSET and COUNTA functions. OFFSET sets the starting point, while COUNTA counts the number of non-empty cells in the rows and columns to determine the exact height and width of the range dynamically.

Does the dynamic range update automatically in Word when the Excel file is closed?

Word usually prompts you to update linked fields when opening the document. You must ensure the source Excel file remains in its original location and approve the update prompt in Word to reflect the latest changes from the closed Excel file.

Excel & Data Problems