Excel Power Query: How to Change Close & Load Settings

How-to basics · Updated November 19, 2026

In Excel for Microsoft 365 on Windows, change a Power Query’s load destination by opening Data > Queries & Connections, right-clicking the query, and choosing Load To…; select the destination you want and confirm. For a new or edited query, use the arrow beside Home > Close & Load in Power Query Editor and choose Close & Load To… to set where its results go.

What Close & Load does

Power Query separates the steps that retrieve and transform data from the place where Excel displays or stores the finished result. In Power Query Editor, Close & Load ends the editing session and applies the query. The ordinary Close & Load command uses the query’s current or default destination. Close & Load To… opens the destination choices instead.

Changing the load setting does not rewrite the query’s transformation steps. For example, removing blank rows or changing a column’s data type happens in the query; choosing a worksheet table or a connection determines what Excel does with the result. You can therefore keep the same cleaned data steps and change how their output is made available.

A query can load its result into a worksheet table, create a PivotTable report, or create a connection without displaying the result in a worksheet. You can also choose to add the result to the workbook’s Data Model when that option is available. These choices serve different purposes: a visible table is easy to inspect, a connection is useful for reuse, and the Data Model supports analysis across related tables.

Change the destination for an existing query

To redirect a query that already exists, use Excel’s Queries & Connections pane rather than changing the transformation steps. The Windows procedure is:

  1. In Excel, select Data > Queries & Connections to open the pane.
  2. In the Queries tab, right-click the query whose output you want to change.
  3. Choose Load To… from the context menu.
  4. In the Import Data dialog, choose a destination, such as Table, PivotTable Report, or Only Create Connection.
  5. If you chose a worksheet output, select whether to use a new worksheet or an existing worksheet. For an existing worksheet, specify the starting cell.
  6. If appropriate, select Add this data to the Data Model, then confirm with OK.

Excel may warn that an existing output will be replaced or ask how to handle the new destination. Read that prompt before confirming, especially if the current output contains material you need. Save the workbook first if you are changing a query used by reports or other people.

Choose Close & Load To for a new query

When you are creating a query, the destination can be selected before its results are first loaded. In Power Query Editor, open the Home tab, select the arrow beside Close & Load, and choose Close & Load To…. Pick the output type in the Import Data dialog, then set the worksheet location or Data Model option if those choices fit your needs.

If you select the main Close & Load button instead, Excel applies its default loading behavior. That is convenient when you simply want to see the result, but it may not be the destination you intended for a staging query or a report. If the result appears on a new worksheet unexpectedly, you can use the query’s Load To… command afterward to change the destination.

Close & Load is not the same as closing the workbook. It closes Power Query Editor and returns you to Excel after applying the query. If you want to leave the editor without applying changes, use its close control and respond to any prompt about unsaved query edits; do not assume that closing the window is equivalent to loading the result.

Which load option should you use?

  • Table: Choose this when you want to inspect, filter, sort, or reference the query output directly on a worksheet. Excel places the results in a structured table, which can expand or contract when refreshed.
  • PivotTable Report: Choose this when the immediate goal is to summarize the query output in a PivotTable rather than display every record as a worksheet table.
  • Only Create Connection: Choose this when the query should remain available to the workbook but its rows do not need to appear in a worksheet. This is common for intermediate queries used by other queries.
  • Add this data to the Data Model: Use this when you need to build analysis from model tables, such as relationships between separate datasets. The Data Model is different from a visible worksheet table, so adding data to it does not mean the query result will automatically be shown on a sheet.

For example, suppose one query imports monthly sales files, another cleans and combines them, and a final query filters the combined data for a report. The first two queries can be connection-only to avoid filling worksheets with intermediate results; the final query can load to a table or PivotTable. This keeps the workbook easier to navigate while leaving the final output visible.

Load destination, refresh, and query settings

The destination controls where query results go; refresh controls when Excel retrieves and applies updated data. A query loaded to a table can be refreshed from Data > Refresh All or by refreshing the query. If the source changes, Excel updates the query output at its existing destination. Changing the destination is not the same as turning on automatic refresh.

For a query with a worksheet table, right-click a cell in the output and choose Table > External Data Properties only if that command is available for the particular connection, or manage refresh options through the query and connection properties exposed by your Excel build. In the Queries & Connections pane, right-clicking a query and choosing Properties opens available refresh-related settings. Options can vary by connection type; examples include refreshing data when the workbook opens or refreshing at an interval.

Do not type permanent notes or formulas inside the area that Power Query owns. A refresh can replace or resize the output table, and content in the way may prevent the result from expanding. Put notes in separate columns or outside the query output range, and keep a copy of important manual calculations before changing a destination.

Common problems when changing Close & Load

  • Load To… is missing or unavailable: Confirm that you are right-clicking a query in the Queries & Connections pane, not a worksheet cell or a connection with a different context menu. You can also open the query in Power Query Editor and check the Close & Load dropdown.
  • The output goes to a new sheet: The query may still be using its default destination. Reopen Load To… and select Existing worksheet, then specify a suitable starting cell.
  • Excel says the destination is occupied: The target range may overlap existing data or another table. Choose an empty location or clear the conflicting content. Avoid deleting data until you know whether another report depends on it.
  • A worksheet table is too large: An Excel worksheet has a limit of 1,048,576 rows. A result larger than the worksheet capacity cannot be displayed in full as a sheet table; consider a Data Model or a narrower query, where supported.
  • Refresh replaces manual edits: Treat the loaded table as query output, not as a hand-maintained data-entry area. Put manual corrections upstream in the source or in a separate table that can be joined or related appropriately.
  • The query loads but looks different than expected: Check the applied steps, column types, filters, and destination. Loading changes where the result appears, while transformation steps determine what the result contains.

A related misconception is that choosing Only Create Connection deletes the query. It does not: the query remains in the workbook and can still be edited or used as a source for another query. The choice simply avoids loading its rows directly to a worksheet. Similarly, deleting a worksheet output is not a reliable way to change the query’s load setting; use Load To… so Excel records the intended behavior.

Windows, Mac, and Excel for the web

The steps above describe Excel for Microsoft 365 on Windows, where Queries & Connections and the Load To… dialog provide the standard controls. Power Query is also available in Microsoft 365 Excel for Mac, but connector support and some query-management or loading options can differ by Excel version and build. If you do not see the same right-click command or dialog on a Mac, check the Power Query controls available in that build rather than assuming the Windows path is identical.

Excel for the web also has a different feature set from desktop Excel. Workbook refresh and query editing capabilities depend on the data source and the feature currently supported in the web app. For a precise change to a query’s load destination, use the desktop version of Excel when the web interface does not expose the required load controls.

Frequently Asked Questions

How do I change Power Query from connection only to a table?

In Excel for Windows, open Data > Queries & Connections, right-click the query, choose Load To…, select Table, and choose a new or existing worksheet. Confirm the dialog to load the query output as a table.

How do I stop Power Query from loading to a worksheet?

Right-click the query in Queries & Connections, choose Load To…, and select Only Create Connection. If the query is also in the Data Model, review that option too; a connection-only query and a model-loaded query are not the same thing.

Does Close & Load refresh the data?

It applies the query and loads its result; retrieving current source data may occur as part of that operation. For later updates, use Refresh or Refresh All, or configure a supported refresh option in the query’s properties.

Can I change where a query loads without rebuilding it?

Yes. Use Load To… from the query’s context menu or the Close & Load dropdown. This changes the output destination without requiring you to recreate the query’s source and transformation steps.

Why does my query keep making a new worksheet?

It is likely using the new-worksheet destination. Open Load To…, select Existing worksheet, and choose a cell that does not overlap other data. If the target is unsuitable, Excel may reject it or ask you to resolve a conflict.

Practical rule of thumb

Use a worksheet table when people need to inspect the records, a PivotTable when they need an immediate summary, and Only Create Connection for intermediate steps that should not clutter the workbook. Use the Data Model when the analysis needs model features such as relationships among tables. Before changing an established workbook, check which reports depend on the query, select an appropriate destination, and refresh once to confirm the output is where you expect.

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.