In Excel for Microsoft 365 on Windows, choose Home > Close & Load in the Power Query Editor to save the query steps and return the results to the workbook. To pick a worksheet, destination, or connection-only option, use the arrow beside Close & Load and select Close & Load To….
What Close & Load means
Power Query is Excel’s tool for connecting to data and preparing it for analysis. You can use it to import a file, workbook, database, folder, or other supported source, then clean and reshape the data in the Power Query Editor. Each change, such as removing a column or filtering rows, is recorded as a query step.
Close & Load ends the editing session and applies the query’s current steps to produce an output in Excel. It does not close Excel or the workbook. The query remains associated with the workbook, so you can open it again, change its steps, or refresh its output when the source data changes.
It helps to distinguish the query from its output. The query is the set of instructions for retrieving and transforming data; the output is the result currently displayed in a worksheet or made available as a connection or Data Model. Loading a query does not normally edit the original source file. It creates or updates an Excel result based on that source.
How to close and load a query
After importing data, the Power Query Editor opens with a preview of the data and a list of applied steps. To send the result to Excel using the default destination:
- Review the preview and the steps listed under Query Settings. Correct any errors before loading.
- On the Power Query Editor ribbon, select Home > Close & Load.
- Wait for Excel to finish loading. The editor closes and Excel displays the result at the destination chosen for that query.
If you want to specify where the result goes, select the arrow beside Close & Load, then choose Close & Load To…. In the Import Data dialog, choose a destination and any available output options, then confirm. The exact choices shown can depend on the type of query and the way it was created.
You can usually reopen a query from the workbook’s Data > Queries & Connections pane by double-clicking it or right-clicking and choosing Edit. To adjust its load destination, right-click the query and choose Load To…, if that option is available for the query.
Where Excel can load the results
The main destination choices serve different purposes. Consider how you intend to use the data before loading a large or frequently refreshed query.
- Table in a worksheet: Loads rows and columns as an Excel table. This is useful for reviewing records, adding formulas beside the results, or making charts and reports from the data. The table has a row limit imposed by the worksheet, so it is not appropriate for every very large dataset.
- PivotTable Report or PivotChart: Creates a summary-oriented view based on the query results. Choose this when the goal is to group, count, or summarize records rather than inspect every row.
- Only Create Connection: Keeps the query available in the workbook without placing its full result in a worksheet. This can be useful when another query uses it, or when you plan to add it to the Data Model.
- Add this data to the Data Model: Stores data in the workbook’s model for analysis with PivotTables and related tools. This can support relationships between tables, but it is not the same as displaying the source rows on a worksheet.
For a table destination, Excel generally lets you choose a new worksheet or an existing worksheet and a starting cell. When loading to an existing sheet, check that the chosen area will not collide with other content. Refreshing a query can change the number of output rows, so leave enough room around the destination.
Choosing the right loading option
For a small list you want to inspect or use directly in formulas, a worksheet table is often the clearest choice. For example, a query that imports monthly order records can load to a table, while a PivotTable summarizes sales by month or product. Loading the detailed records and creating a separate summary gives you both a usable data table and an analysis view.
A connection-only query is often appropriate for an intermediate step. Imagine one query imports several files and another query combines or filters the result. The first query may not need its own visible worksheet output. Keeping it as a connection can avoid clutter, while the final query loads the useful result. If you need to examine the intermediate data, you can change its load setting later.
Use the Data Model when you need to analyze multiple related tables or work with data that is better managed as a model than as worksheet ranges. It is not automatically the best choice for every import: it adds a modeling step, and someone who only needs a simple list may find a worksheet table easier to understand.
Also consider who will use the workbook. A visible table is easy to find and inspect. A connection-only query may be less obvious to a colleague who does not know to open Queries & Connections. Clear query names and a short note on a report sheet can help explain where the results come from and how to update them.
What happens after loading and refreshing
Close & Load applies the saved query steps to the source data available at that time. The worksheet output is a result, not a live window into the source. If the source file later changes, the displayed result does not necessarily change immediately. Use Data > Refresh All to refresh workbook connections, or use the refresh command for an individual query or table when appropriate.
On refresh, Power Query reads the source again and applies the recorded steps in order. If a source column was renamed or removed, a step that refers to its former name may fail. If new rows are added to a source table or file, they may appear after a successful refresh, provided the query’s source and filters include them.
Be careful about typing directly into cells inside a query output table. A refresh can replace or resize the returned data, so manual edits there may be lost or displaced. Put notes or calculations in a separate area, or use a separate table and appropriate formulas or relationships. The query’s transformation steps belong in the Power Query Editor, not in hand-edited output cells.
Refresh settings can be adjusted for some connections through the workbook’s connection or query properties. Options and labels vary with the kind of source and Excel build. Automatic refresh may be useful for regularly updated sources, but it can also make a workbook slower to open or cause unexpected source access. If you need predictable results, refresh deliberately and confirm the date or status of the source data.
Common problems and practical fixes
- The editor closes, but the output is not where expected: Check Data > Queries & Connections and inspect the query’s load settings. Use Load To… to select the intended destination when available.
- Close & Load is unavailable or loading fails: Look for an error in the preview or in the query steps. A missing source file, changed column name, invalid credentials, or unavailable connection can prevent a successful load. Correct the underlying issue, then try again.
- The destination sheet already has data: Choose a clear location or a new worksheet. Avoid placing a query output over unrelated cells, since refreshed output may grow or shrink.
- The new rows do not appear: Refresh the query and confirm that its source points to the correct file, table, folder, or range. Also inspect filters and steps that might exclude the rows.
- The workbook becomes slow or too large: Consider whether every intermediate query needs a worksheet output. Connection-only loading or the Data Model may be more suitable, depending on how the data will be analyzed.
If an error appears, read the full message before changing steps. Removing a step may hide the symptom without fixing the cause. For example, if a file moved, update the source path rather than deleting later transformations that depend on the file’s columns.
Close & Load versus Close
Close & Load returns the query result to the workbook using its load behavior. Close & Load To… lets you choose or revise the output destination. These commands should not be confused with simply closing the workbook window: the query editor is a separate editing environment, and loading is the action that puts the query result into its configured destination.
If your goal is to keep a query without a visible worksheet table, choose Only Create Connection through the load options rather than expecting the ordinary Close & Load command to hide the output. If you already loaded a table and later want a different setup, change the query’s load settings instead of deleting cells by hand. Deleting visible output alone may not remove the query definition or its connection.
Windows, Mac, and Excel for the web
These directions use Microsoft Excel for Microsoft 365 on Windows, where the Power Query Editor uses the Home > Close & Load ribbon command. Excel for Mac has Power Query capabilities in supported Microsoft 365 versions, but available connectors, editing features, and interface details can differ by version. If a command is not visible, check that Excel is updated and consult the options shown in that version rather than assuming every Windows feature is present.
Excel for the web also has a different feature set and interface from the Windows desktop editor. For a workbook that depends on a particular connector or transformation, use the desktop version in which the query was created if the web interface does not offer the needed editing or refresh operation. In all versions, keep a copy of important workbooks before making major changes to query destinations or source settings.
Frequently Asked Questions
Does Close & Load save the Power Query steps?
Yes. It returns the query results to Excel and retains the transformation steps in the workbook query. You can reopen the query later to edit those steps.
Does Close & Load change the original file?
Normally, no. Power Query reads from its source and loads transformed results into the Excel workbook. It does not write those transformations back to the original source file.
How do I load a query into an existing worksheet?
In the Power Query Editor, choose the arrow beside Close & Load, select Close & Load To…, then choose a table destination and an existing worksheet location in the Import Data dialog, if those choices are available.
How do I refresh data after Close & Load?
In Excel, choose Data > Refresh All to refresh workbook connections, or refresh the individual query. A successful refresh retrieves source data again and reapplies the query steps.
Can I use a query without showing its data in a sheet?
Yes. Choose Only Create Connection in the load options. You can also add the query’s data to the Data Model when that fits your analysis needs.
Related
- Can You Password Protect an Excel File? How It Works
- Can You Merge Excel Spreadsheets? Methods That Work
- Can You Merge Two Excel Spreadsheets? Methods Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Data Cleaning Tools in Excel: What to Use and WhenExcel’s main data-cleaning tools are Power Query, Remove Duplicates, Text to Columns, Flash Fill, Find and Replace, and…
- ISNUMBER in Excel: What It Checks and How to Use ItIn Excel, ISNUMBER checks whether a value is stored as a number and returns either TRUE or FALSE.…
- Calendar Layout Template for Excel: How to Make OneA practical calendar layout template for Excel has a year and month selector, seven weekday columns, and a…
- How to Combine Cells in Excel and Keep TextTo combine the text from cells without losing anything, enter a formula such as =A2&" "&B2 in an…
- How to Turn On AutoFit in Excel for Rows and ColumnsTo AutoFit in Excel for Microsoft 365 on Windows, select the rows or columns you want to resize,…
- How to Insert a Check Box in Word and Make It ClickableTo add a clickable check box in Word for Microsoft 365 on Windows, enable the Developer tab, then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.