Excel Power Query Incremental Refresh: How to Set It Up

How-to basics · Updated November 22, 2026

Excel Power Query incremental refresh lets you load only new or changed rows from a large data source instead of reloading everything each time, and you set it up by enabling the “Incremental refresh” option in Power Query’s data load settings (available in Excel for Microsoft 365 and Excel 2024). This feature dramatically speeds up refresh times for datasets that grow over time, such as sales transactions or log files, by using a date or numeric filter to pull only the latest data.

What Is Incremental Refresh in Power Query?

Incremental refresh is a data loading strategy that divides your source data into two parts: a historical “archive” that stays in the workbook and a “new” slice that is re-queried on each refresh. In Excel, this is implemented through the Power Query editor’s data load options, not through a separate button. When you enable it, Excel stores a copy of the already-loaded data in the workbook (or in the data model) and then queries only the rows that fall within a rolling window you define, such as the last 30 days.

This is different from a full refresh, where Power Query re-runs the entire query against the source, downloads all rows, and replaces the existing data. Full refresh is simple but becomes slow and bandwidth-heavy as the source grows. Incremental refresh is designed for large datasets—think hundreds of thousands or millions of rows—where a full refresh would take minutes or hours.

How Incremental Refresh Works in Excel

Excel’s incremental refresh relies on a parameter-based filtering mechanism. You define two parameters in Power Query: a start date and an end date. The query then filters the source to only those dates. When you enable incremental refresh in the data load settings, Excel automatically manages these parameters during each refresh, replacing them with the appropriate range based on the current date and the refresh window you set.

Here is the step-by-step process (Excel for Microsoft 365 on Windows):

  1. In Power Query Editor, create two parameters: StartDate and EndDate (use the Manage Parameters button under the Home tab).
  2. Apply a filter to your date column using those parameters. For example, with a column named OrderDate, use the custom filter: [OrderDate] >= StartDate and [OrderDate] < EndDate.
  3. Close & Load the query to a worksheet or the Data Model.
  4. Go to Data > Queries & Connections, right-click the query, and select Properties.
  5. In the Usage tab, check "Enable incremental refresh".
  6. Set the "Archive data older than" value (e.g., 30 days) and the "Refresh data within" window (e.g., 1 day).

On each refresh, Excel will only pull rows where OrderDate falls within the last 1 day (or whatever you set), while keeping the older rows stored locally. The parameters are updated automatically, so you do not need to edit them manually.

Choosing the Right Refresh Window

The two key settings are the archive threshold and the refresh window. The archive threshold defines how far back you keep data in the workbook. The refresh window defines how much of the recent data is re-queried each time. For example, if you set archive to 365 days and refresh window to 7 days, Excel will re-pull the last 7 days of data on every refresh, while preserving the previous 358 days locally.

Consider these factors when choosing:

  • Data volume: A larger refresh window means more data is downloaded each time, but it also ensures that corrections to older recent records are picked up.
  • Source system load: Frequent small refreshes put less load on the source database than one massive full refresh.
  • Data change frequency: If your source updates records within the last month (e.g., status changes), set the refresh window to cover that period so those changes are captured.
  • Storage: The archived data resides in the workbook or data model, so a very long archive can bloat your file size.

A common starting point is 30 days of archive and 1 day of refresh window, but you should adjust based on your data's actual update pattern.

Common Problems and Misconceptions

One common misconception is that incremental refresh works with any data source. In reality, it requires a date or numeric column that can be used for filtering, and the source must support query folding (i.e., the filter is pushed to the source). For example, SQL Server, Azure SQL, and OData feeds support query folding, but CSV files or Excel workbooks do not—in those cases, Power Query downloads the entire file each time, and incremental refresh will not provide a speed benefit.

Another issue is that incremental refresh is only available in Excel for Microsoft 365 and Excel 2024 (on Windows and Mac), not in Excel 2019 or earlier. Also, if you load data to a worksheet (not the Data Model), the feature works, but the archived data is stored in the worksheet, which can slow down Excel if the dataset is huge.

Users often see an error like "This query cannot be incrementally refreshed because it does not use parameters"—this happens when you forget to apply the parameter filter in the query before enabling the option. Make sure the filter step is present and uses the parameter names exactly.

Incremental Refresh vs. Full Refresh

Full refresh is the default behavior: every refresh re-runs the entire query, downloads all rows, and replaces the existing table. It is simpler and ensures the data is always completely up-to-date, but it is inefficient for large datasets. Incremental refresh trades that simplicity for speed and efficiency.

Here's a quick comparison:

  • Speed: Incremental refresh is much faster for large data because it only downloads a small slice.
  • Data consistency: Full refresh guarantees that deleted rows are removed and all changes are captured; incremental refresh only captures changes within the refresh window, so if a row older than the archive threshold is deleted in the source, it will remain in your workbook.
  • Complexity: Incremental refresh requires parameter setup and careful configuration; full refresh is one click.
  • Use case: Use incremental refresh for append-only data (like logs) where old rows rarely change; use full refresh for smaller or frequently-changing datasets.

Alternatives and When Not to Use It

If incremental refresh is not available or too complex, consider these alternatives:

  • Power BI: Power BI has a native incremental refresh feature that is more robust and easier to configure than Excel's. If you are working with very large data and have a Power BI license, that may be a better tool.
  • Manual filtering: You can write a query that only loads the last N days by using the Date.From(DateTime.LocalNow()) function in a filter, but you lose the automatic archiving of historical data—each refresh would replace the entire table with just the recent window.
  • Append queries: Keep a historical table in a separate sheet and append new data using a query that only loads new rows, but this requires manual or VBA-based merging.
  • Use a database view: If your source is a database, create a view that only returns recent rows, and load that view—this reduces the data transferred but still requires a full refresh of the view.

Incremental refresh is not a good fit when your source data changes frequently across the entire history (e.g., a sales table where past orders are often edited). In such cases, you would miss those changes unless you set a very large refresh window, which defeats the purpose.

Frequently Asked Questions

Does incremental refresh work with CSV files?
No. CSV files do not support query folding, so Power Query must read the entire file to filter it. Incremental refresh will not speed up the process; it will still download the whole file each time. Use a database or an API that supports server-side filtering.

Can I use incremental refresh if I load data to a PivotTable?
Yes, as long as you load the query to the Data Model (which is required for PivotTables). The incremental refresh settings are the same. However, the archived data is stored in the data model, which can increase the workbook's memory usage.

Why is my incremental refresh not working?
Check that your query uses a filter with the two parameters, that the data type of the date column is Date/Time, and that the source supports query folding. Also verify that you are using Excel for Microsoft 365 or Excel 2024—the feature is not in older versions.

Does incremental refresh reduce file size?
Not necessarily. The archived data is stored in the workbook, so the file size grows as you accumulate history. The benefit is in refresh speed and network load, not file size. To limit file size, reduce the archive period.

Can I set incremental refresh to run on a schedule?
Excel itself does not have a built-in scheduler for refreshing queries. You can use Power Automate or a VBA macro with the Application.OnTime method, but the incremental refresh logic will still apply when the query is refreshed.

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.