Power Query Problems

Fix Power Query and PivotTable Data Missing in Excel XLTM Templates

John Wilson Oct 1, 2026 868 views

Question details

Users encounter blank PivotTables and missing field names when creating a new file from an Excel macro-enabled template (.xltm) that contains Power Query data.

How to Fix Power Query and PivotTable Data Missing in Excel XLTM Templates

Product: Microsoft Excel
Device & OS: not provided
Scenario: Saving a workbook with Power Query and PivotTable connections as an Excel macro-enabled template (.xltm) and distributing it to recipients.
Observed behavior: Upon opening a new workbook generated from the template, the PivotTables appear completely blank and the Power Query data fields are missing.

Before you start

Ensure that the original data source paths used in your Power Query connections are accessible to the recipients opening the template, as broken network paths will prevent data refreshes.

Solution 1Recommended

Use a Protected Master Workbook Instead of an XLTM Template

Since .xltm files have known limitations with storing Power Query caches, using a standard macro-enabled workbook (.xlsm) is the most reliable workaround.

Excel macro-enabled templates (.xltm) often drop the background cache for Power Query and PivotTables to reduce the template's file size. Replacing the template with a 'Read-Only Recommended' master file ensures data integrity while preserving template-like behavior.

Open the original source workbook

Open the fully functional workbook where the Power Query output and PivotTables display correctly.

Refresh all data connections

Navigate to the 'Data' tab on the ribbon and click 'Refresh All' to ensure the Power Query output table and PivotTable cache are up to date.

Save as a standard macro-enabled workbook

Click 'File' > 'Save As', and choose 'Excel Macro-Enabled Workbook (*.xlsm)' from the format dropdown instead of an .xltm template.

Set the file to Read-Only Recommended

Before saving, click 'Tools' next to the Save button, select 'General Options', check the 'Read-only recommended' box, and click 'OK'. This forces users to save a copy.

Use a Protected Master Workbook Instead of an XLTM Template

Microsoft Feedback: The .xltm caching behavior is a known limitation. You can report this to Microsoft through the Excel Feedback portal under 'Help' > 'Feedback'.

Solution 2

Add a VBA Macro to Auto-Refresh on Open

If you must use the .xltm format, you can add a Workbook_Open macro to automatically refresh the data connections when a new file is created.

Free Microsoft Office alternative

Try WPS Office for Reliable Spreadsheet Management

If you frequently encounter formatting limitations and caching errors in Microsoft Excel templates, WPS Office offers a lightweight, free alternative. With a familiar interface and excellent compatibility, you can manage complex datasets and PivotTables seamlessly.

1. Download and install WPS Office: Visit the official WPS website, download the free version, and install it on your device.
2. Open your spreadsheet: Launch WPS Spreadsheets and open your existing .xlsx or .xlsm files directly without losing any formatting.
3. Analyze your data seamlessly: Use the built-in PivotTable tools under the Insert tab to efficiently summarize and analyze your data sets.

Fully compatible with Microsoft Excel formats (.xlsx, .xlsm, .xltm)Lightweight software that processes large data files smoothly without lagFamiliar user interface requiring zero learning curve for Excel usersFree to use with powerful built-in PivotTable and data analysis tools

microsoft office alternative - wps office

Frequently Asked Questions

Why do PivotTable fields disappear in Excel templates?

Excel's template formats (.xltm and .xltx) often clear external data caches, including Power Query outputs, to minimize the template's initial file size. This causes the associated PivotTables to lose their data source and appear blank upon creating a new document.

Can I force a PivotTable to refresh when opening a file?

Yes. Click anywhere inside the PivotTable, go to 'PivotTable Analyze' > 'Options' on the ribbon, select the 'Data' tab, and check the box for 'Refresh data when opening the file'.

How do I ensure Power Query refreshes automatically?

Go to the 'Data' tab, click 'Queries & Connections', right-click your specific query in the side pane, and select 'Properties'. Under the 'Usage' tab, check the box for 'Refresh data when opening the file' to keep data continuously updated.

Excel & Data Problems