Power Query Problems

How to Refresh Formulas in Closed Excel Workbooks with Power Query

Camila Milosovich Oct 8, 2026 868 views

Question details

The user needs to know how to force Power Query to retrieve updated formula results from source Excel workbooks without having to manually open them.

Can Power Query Refresh Formulas in Closed Excel Workbooks?

Product: Microsoft Excel
Device & OS: not provided
Scenario: Importing data into a main workbook via Power Query from several source workbooks that rely heavily on formulas.
Observed behavior: Power Query imports the last saved static values from the closed files instead of recalculating the formulas to fetch the latest dynamic results.

Before you start

Verify that your source workbooks have been recently saved, as Power Query fundamentally reads the underlying XML data (the last saved state) of a closed file rather than interacting with Excel's live calculation engine.

Solution 1Recommended

Move Calculations Directly to Power Query (Recommended)

Instead of relying on Excel formulas in source files, migrate the calculation logic into Power Query. This eliminates the need to open source files and vastly improves stability.

By extracting only raw data from your source files and performing the math inside Power Query or Power Pivot, you bypass the limitation of closed-workbook calculations entirely. This is the most robust approach for large datasets.

Connect to raw data sources

Use Power Query to connect to the raw data tables in your source workbooks rather than the formula-driven summary sheets.

Open Power Query Editor

Click 'Transform Data' to open the editor where you can manipulate the incoming raw data.

Recreate logic with Custom Columns

Go to the 'Add Column' tab and click 'Custom Column' to write M-code or basic arithmetic that replicates your old Excel formulas.

Load the processed data

Once your calculations are set up, click 'Close & Load' to bring the fully calculated data into your main workbook.

Move Calculations Directly to Power Query (Recommended)

Best Practice: Moving transformations to Power Query ensures your queries refresh instantly without file-locking issues or system crashes.

Solution 2

Automate Workbook Recalculation Using VBA

If you cannot migrate your formulas to Power Query, you can use a VBA macro to automatically open, recalculate, save, and close the source files before running the refresh.

Free Microsoft Office alternative

Try WPS Office for Lightweight Data Processing

If VBA automation and heavy Power Query operations are causing your system to freeze or crash, consider switching to WPS Office. It provides a lightweight, highly compatible, and free alternative to Microsoft Office, ensuring smooth performance even when handling complex formulas and large datasets.

1. Download WPS Office: Visit the official WPS website and download the free installation package.
2. Install the software: Run the installer and follow the quick on-screen instructions to set up WPS Office.
3. Open your workbooks: Launch WPS Spreadsheets and seamlessly open your existing .xlsx files without losing formatting or formula integrity.

Fully compatible with Microsoft Excel formats (.xls, .xlsx, .csv).Lightweight architecture prevents system crashes with multiple large files.Familiar ribbon interface requires zero learning curve.Free to use for everyday data analysis and spreadsheet management.

microsoft office alternative - wps office

Frequently Asked Questions

Why does Power Query show outdated formula results from closed workbooks?

Power Query reads the underlying XML structure of a closed Excel file, which only stores the values generated during the absolute last save. Because the file is not actively open, Excel's calculation engine is not triggered to update the formulas.

Will setting Excel's calculation to 'Automatic' fix this Power Query issue?

No. The automatic calculation setting only functions when the workbook is actively open in the Excel application. Closed workbooks will remain static regardless of this setting.

Can I use Power Automate to refresh closed workbooks?

Yes. You can create a Power Automate Desktop flow or a cloud flow (if using Excel Online) to automatically open, recalculate, and save the source files on a daily schedule just before your main Power Query dataset triggers its refresh.

Is it better to use a database instead of linking multiple Excel files?

Absolutely. If your data logic has outgrown standard Excel formulas, moving your raw data to a centralized database (like SQL Server or Access) eliminates file-locking constraints and drastically improves data retrieval performance for Power Query.

Excel & Data Problems