Lower Excel Data Load: Speed Up Large Workbooks

How-to basics · Updated November 18, 2026

To lower Excel data load and make a workbook open or refresh faster, bring in only the rows and columns you need, reduce unnecessary formulas and formatting, and avoid loading duplicate copies of data. If the delay happens during a Power Query refresh rather than when opening the file, filter and trim the query before loading it; if the workbook is slow to open, check calculation, links, and file size as well.

First identify what is taking time

“Data load” can mean several different things in Excel. A workbook may take a long time to open, pause while formulas calculate, refresh slowly from a database or folder, or become sluggish when you scroll and filter. The remedy depends on which task is slow, so note when the delay occurs and whether Excel displays a calculation or refresh status.

  • Opening: The delay occurs before you can work in the workbook. Large files, external links, extensive formatting, and calculation on opening can contribute.
  • Refreshing: A Power Query, connection, or PivotTable is retrieving or processing data. Source size and query design matter.
  • Editing: Cells respond slowly after the workbook opens. Formulas, conditional formatting, and other workbook features may be recalculating.

For a useful comparison, save a copy of the workbook, close Excel, and reopen the copy. Record how long opening takes, then time a refresh separately. Change one thing at a time and repeat the same action. This helps distinguish a genuine improvement from a difference in what Excel had to calculate or retrieve.

Load fewer rows and columns with Power Query

If you use Power Query, the most direct way to lower the data load is to limit the data before it reaches the worksheet or Data Model. In Excel for Microsoft 365 on Windows, select Data > Queries & Connections, open the query in Power Query Editor, and apply filters to exclude irrelevant rows. Use Home > Choose Columns to retain only the fields needed for analysis, or select unwanted columns and choose Home > Remove Columns.

For example, a sales query may contain ten years of transactions and dozens of fields, even though a monthly report needs the current year and only date, product, region, and sales amount. Filtering to the reporting period and removing unused fields can reduce the amount Excel must store and process. Keep the original source intact unless you deliberately intend to change it; make these reductions in the query or in the system that supplies the data.

When a query is only an intermediate step for another query, avoid loading that staging result to a worksheet. In Power Query Editor, use the Close & Load dropdown and choose Close & Load To, then select Only Create Connection if that intermediate query does not need its own output table. The exact options can vary with the query and Excel build. For a final query, load to a worksheet only when people need to inspect or use the rows there; use the Data Model when the analysis needs a large relational dataset or measures.

Reduce work at the data source

Filtering in the source system can be faster than bringing a very large result into Excel and discarding most of it afterward. For a database, request only the relevant date range and fields where your access and reporting setup allow it. For a folder of files, restrict the folder or file selection to the required inputs before combining them. This reduces transfer and processing, not just the visible output.

Some Power Query sources and transformations support query folding: Power Query can translate supported steps into instructions that the source performs. When folding works, filters and column selection may be applied at the source instead of after all the data has been downloaded. Support depends on the source and the steps used. In Power Query Editor, right-click a step and look for View Native Query when available; its absence does not by itself prove that every earlier step is inefficient.

Do not assume that deleting rows from a loaded worksheet reduces the source query’s work. A later refresh may retrieve them again if the query still requests them. Put lasting restrictions in the query or source definition, and verify the result with a refresh.

Control formula calculation

A workbook can feel as though it is loading data when Excel is actually calculating formulas. Large ranges, repeated lookups, complex array calculations, and formulas that depend on one another can all add work. Review whether formulas are needed in every row and whether calculations can be simplified. Where a report is a fixed snapshot, replacing finished formula results with values can reduce future recalculation, but it also removes the formulas and their ability to update.

For a workbook where users intentionally want to recalculate on demand, Windows Excel provides Formulas > Calculation Options > Manual. This is a temporary performance choice, not a general fix: formula results can become stale until recalculation. Use F9 to calculate open workbooks, or Shift+F9 to calculate the active worksheet. Return to Automatic when appropriate, and make sure other users understand the setting before sharing the file. On Mac, use Formulas > Calculation Options as well; shortcut behavior can differ by keyboard and Excel version.

Look for formulas that process entire columns unnecessarily, especially when repeated across many cells, and narrow their references to the actual data range or a suitable Excel Table column. Also review volatile functions, which recalculate more often than ordinary formulas, and avoid duplicating the same expensive calculation across many cells when a helper result can be reused. Make changes carefully: changing a reference or calculation method can alter results, so compare key totals before and after.

Keep workbook structure lean

Extra copies of the same data consume storage and can increase refresh or calculation work. Check whether a query is loaded both to a worksheet and to the Data Model without a clear need. Review PivotTables, connections, and queries that are no longer used, but confirm dependencies before removing them. A query may feed another query, a chart, or a report even if its output is not obvious on the current sheet.

Excel worksheets support far more rows and columns than most reports use. A workbook can become unexpectedly large if formatting or content has been applied far beyond the actual data. Press Ctrl+End on Windows to see the last cell Excel considers used. If it is far beyond the real data, remove genuinely unused rows or columns below and to the right of the content, save the workbook, close it, and reopen it to check the used range again. Do not delete rows or columns that contain required formulas, notes, or records.

External links and connections can also add work or trigger prompts. Review Data > Queries & Connections for connections and queries, and check Data > Workbook Links when that command is available in your Microsoft 365 build. Remove a link only after confirming what uses it. Breaking a workbook link can replace linked formulas with their current values, so save a copy first and check dependent reports.

Remove excessive formatting and conditional rules

Formatting does not usually have the same calculation cost as a large formula network, but formatting applied to huge unused ranges can inflate a workbook and make it harder to manage. Select only the unnecessary area, then use Home > Clear > Clear Formats. Clearing formats leaves cell contents in place; Clear All removes contents as well, so choose carefully. Save and reopen the file after cleanup to see whether its size and behavior improve.

Conditional formatting deserves a separate check because rules may cover far more cells than the report uses. Select a cell in the affected area and choose Home > Conditional Formatting > Manage Rules. Set the display to the current selection or worksheet as needed, inspect each rule’s Applies to range, and remove or narrow rules that cover irrelevant cells. Preserve rules that communicate important status, and verify that the intended cells still format correctly.

Choose a suitable file format

For most shared workbooks, .xlsx is the standard choice. Excel Binary Workbook (.xlsb) can sometimes make a large workbook smaller or faster to open and save, but the improvement depends on what the file contains and is not guaranteed. Test a copy with the actual workbook and typical tasks before switching. Check that recipients and other software can use the format, and remember that .xlsb can contain macros just as other macro-capable formats can.

Compressing or converting the file does not fix an inefficient query or a calculation-heavy design. Treat file format as one experiment after addressing unnecessary data, formulas, and formatting. Keep a working backup in the original format until you have confirmed that charts, connections, macros, and sharing workflows behave as required.

A safe way to make improvements

Before making substantial changes, save a separate copy. Then use a measured sequence so you can identify what helped and restore anything that changes the report’s meaning:

  1. Record the workbook’s file size and the time required to open, refresh, and recalculate it.
  2. Remove unused query columns and rows, and avoid loading intermediate query results unless they are needed.
  3. Review calculation settings, repeated formulas, external links, and connections.
  4. Inspect the used range and conditional-formatting rules for coverage beyond the real report.
  5. Repeat the same timing checks, then compare important totals and outputs with the original.

For an important workbook, preserve the original data and verify results after each change. A faster report is not an improvement if a filter accidentally excludes valid records or a formula has been replaced with an outdated value. If several people use the file, document any manual calculation setting or source filter that changes what they see.

Frequently Asked Questions

How do I make an Excel file load faster?
Start by checking whether the delay is opening, refreshing, or calculating. Remove unused data and formatting, review formulas and external connections, and test one change at a time in a copy. There is no single setting that speeds every workbook.

Does filtering a Power Query reduce the amount of data Excel loads?
Yes, a query filter can reduce the rows returned to its output. When the source supports query folding, the filter may also be applied at the source, reducing the data transferred. Folding depends on the source and query steps, so confirm the result after refresh.

Should I switch Excel to Manual calculation?
Only when you understand the trade-off and can manage recalculation. Manual mode can prevent automatic formula updates, leaving results stale until you recalculate. It does not make a slow Power Query source or large file smaller.

Will saving as XLSB make Excel load faster?
It may help some large workbooks, but the result varies. Test a copy and check compatibility with the people and systems that use the file. XLSB is not a substitute for removing unneeded data or fixing an inefficient workbook design.

Why does Excel still load slowly after I delete rows?
The query may retrieve those rows again on refresh, or the workbook may still have formulas, formatting, links, or a used range extending beyond the visible data. Check the query definition and the workbook structure, then save, close, and reopen a test copy.

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.