Load Query Data into an Excel Table: A Practical Guide

How-to basics · Updated November 21, 2026

To load Power Query data into an Excel table in Microsoft 365 for Windows, open the query in Power Query Editor, select Home > Close & Load > Close & Load To…, choose Table, select a new or existing worksheet, and click OK. If the query is already loaded, use Data > Queries & Connections, right-click the query, and choose Load To….

What loading a query into a table means

A Power Query is a saved set of steps for connecting to data and preparing it—for example, importing a CSV file, removing blank rows, splitting a column, or combining monthly files. Loading sends the result of those steps to a place Excel can use. Choosing Table places the query result in a worksheet as an Excel table, with column headers, filter buttons, and a table name.

The source and the loaded result are distinct. The query reads from its source and applies its transformation steps; the worksheet table displays the resulting data. If the source changes, refresh the query to update the output. Editing a value directly in the output table does not normally change the source or the query steps, and a later refresh can replace that edit.

This distinction is useful when deciding where to make a correction. If a date needs a different format, you might change the type or transformation in the query. If a source value is wrong, correct it in the source when possible. Treat the loaded table as a refreshable result rather than as the authoritative copy of the data.

Load a query as a table step by step

  1. In Excel, create or open the query. To start from a file, use Data > Get Data and choose an available source, such as a workbook or text/CSV file. Follow the prompts to open the data in Power Query Editor.
  2. In Power Query Editor, check the preview and the steps listed in Query Settings. Confirm that the column headers and data types look reasonable before loading.
  3. Select the arrow beside Home > Close & Load, then choose Close & Load To…. If you select the main Close & Load button, Excel uses its default load behavior, which may not give you the destination choice you want.
  4. In the Import Data dialog, select Table. Choose New worksheet for a separate output sheet, or choose Existing worksheet and specify the top-left cell where the table should begin.
  5. Click OK and wait for Excel to load the rows. For a large source, loading can take time. Once complete, the results appear as a formatted table with filter controls in the header row.

Before choosing an existing worksheet, check that the destination area is clear. The output needs room to expand when refreshed; nearby notes, formulas, or another table can make a future refresh fail or produce an unexpected layout. A new worksheet is often the simplest choice for a first load.

Choose the right destination and load options

In the Import Data dialog, Table is the choice for ordinary worksheet rows and columns. PivotTable Report creates a PivotTable from the query result instead, while Only Create Connection saves the query without putting its rows directly on a sheet. The dialog can also offer Add this data to the Data Model, which is useful for certain analysis and relationships between tables.

  • New worksheet: Keeps the query output separate from other content and is a good default for a standalone result.
  • Existing worksheet: Places the table at a chosen starting cell. Use this when a report layout requires a specific location, and leave sufficient clear space for later refreshes.
  • Only Create Connection: Avoids a visible worksheet copy. You can use the connection in other queries or analysis features, depending on the workbook setup.
  • Data Model: Stores data in the workbook’s model for analysis rather than relying only on a visible worksheet table. A worksheet has a limit of 1,048,576 rows, so a very large result may require a different approach.

To change the load choice later, open Data > Queries & Connections, right-click the query, and select Load To…. Review the destination before confirming. If you are reorganizing a workbook, check any formulas, charts, or reports that refer to the existing output so you do not disrupt them inadvertently.

Refresh the table when the source changes

Loading is not the same as refreshing. Loading creates the worksheet output; refreshing reruns the query’s connection and transformation steps to retrieve current source data. To refresh one query, select a cell in its result table and use Data > Refresh, or right-click the query in the Queries & Connections pane and choose Refresh. To update multiple connections, use Data > Refresh All.

After a successful refresh, Excel updates the table with the query result. If the source has more or fewer rows, the table can grow or shrink accordingly. Any information placed directly below or beside the output should be kept clear of the area it may occupy. Formulas in separate report areas may need to be checked if the output size changes.

For recurring work, right-click a query in Queries & Connections and choose Properties to review the available refresh settings. Depending on the connection, options can include refreshing when the workbook opens or refreshing at a set interval. These settings do not make every source continuously current: the workbook still needs access to the source, and some connections require credentials or user interaction.

Check headers, data types, and table formatting

Review the query preview before loading, especially when the source has inconsistent headings or mixed values. Power Query can assign types such as text, whole number, decimal number, date, and date/time. A column with codes such as 00127 may need to remain text; treating it as a number can remove leading zeros. A date stored as text may also sort differently from a true date column.

In Power Query Editor, select a column and use the data type control in the column header or on the ribbon to set the appropriate type. Look for type-conversion errors in the preview before loading, because error values can flow into the result. Excel’s table formatting can be changed after loading from the table design controls, but formatting does not correct an incorrect source type or transformation.

Excel gives a loaded table a name that can be inspected or changed on the Table Design tab in the Table Name box. A clear name helps when building formulas, PivotTables, or other queries that refer to the result. Avoid renaming or moving output tables without checking dependent workbook content.

Common loading problems and how to fix them

  • Close & Load To is unavailable or you cannot find the query: Return to Excel and open Data > Queries & Connections. The query may already exist as a connection. Right-click it and inspect Load To….
  • The refresh reports that the destination is not clear: Look for values, merged cells, another table, or other content in the area where the output needs to expand. Move that content or load the query to a new worksheet.
  • Refresh fails to find the source: The file may have moved or been renamed, a network location may be unavailable, or access may have expired. Check the source path and permissions, then refresh again.
  • Values appear as errors or have the wrong format: Inspect the query steps for changed column names, conversion errors, or a type that does not suit the data. Fix the step in Power Query rather than correcting individual cells in the output.
  • Some records seem missing: Review filters and other transformation steps in the query, and check whether the source itself has changed. A filter applied during preparation can intentionally exclude rows.
  • The output is too large for a worksheet: A worksheet cannot hold more than 1,048,576 rows. Reduce the result with appropriate query filters or load it to the Data Model when that fits the analysis requirements.

When a refresh fails, the existing table may still show the last successful result. Do not assume that it is current simply because rows remain visible. Check the refresh message and confirm the source and query status before using the data in a report.

Table output versus connection-only loading

A worksheet table is easy to inspect, filter, format, and reference in ordinary Excel work. It is often the right choice when people need to read the query results or when the data will feed formulas and charts. Its main trade-offs are the worksheet row limit and the need to keep the output area available for refreshes.

A connection-only query can keep an intermediate result off the sheet. This can help organize a workbook that uses one query to prepare data for another, or when the final analysis is a PivotTable or Data Model report. Connection-only does not mean that the query disappears: it remains listed in the workbook’s query connections. Choose the load destination based on how the result will be used, not simply on whether a table looks tidy.

Windows, Mac, and Excel for the web

The steps above use the Microsoft 365 desktop interface for Windows. Excel for Mac includes Power Query capabilities, but available connectors and parts of the interface can differ by version. Excel for the web also has a different interface and does not offer an identical set of authoring and source options. If a command or connector is missing, check the capabilities for the exact Excel platform and update level rather than assuming the workbook is damaged.

When collaborating across platforms, it is sensible to create and configure a complex query in the desktop version that supports its sources, then confirm that collaborators can refresh it in their own environment. A source that depends on a local file path, a particular account, or an organization’s network may not be accessible to every workbook user.

Frequently Asked Questions

How do I load an existing query into an Excel table?
Open Data > Queries & Connections, right-click the query, choose Load To…, select Table, and choose a worksheet destination. Click OK to apply the load choice.

Does an Excel query table update automatically?
Not simply because the source changes. Refresh the query with Data > Refresh or Data > Refresh All. You can review query properties for options such as refresh on opening, where available for the connection.

Can I edit values in the loaded table?
You can type into worksheet cells, but those edits do not change the query source or its transformation steps. A refresh can replace edited output, so make lasting corrections in the source or in the query steps.

Can I load a query to a specific cell?
Yes. In the Import Data dialog, select Table and Existing worksheet, then choose the top-left destination cell. Leave enough clear space for the result to expand on refresh.

Why is my query not showing as a table?
It may have been loaded as a connection only, as a PivotTable, or to a different worksheet. Check Data > Queries & Connections and use the query’s Load To… option to review its current destination.

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.