Yes. In Excel for Microsoft 365 on Windows, open the query in Power Query Editor, choose Home > Close & Load > Close & Load To, select Only Create Connection, and click OK. The query remains in the workbook and can be refreshed, but its results are not loaded into a worksheet table.
What a connection-only query does
Power Query is Excel’s tool for connecting to data, cleaning and transforming it, and then making the result available for analysis. A query describes the source and the steps applied to it, such as removing columns, filtering rows, changing data types, or combining files. Loading determines where the result of those steps goes.
With Only Create Connection selected, Excel saves the query and its connection information without placing the query’s output in a sheet. You can still see the query in the Queries & Connections pane, edit its steps, and refresh it. The query is therefore not deleted or merely a temporary preview. Its definition is part of the workbook.
This option is useful when a query is an intermediate step rather than a report you want to display. For example, a workbook might import several monthly files, standardize their column names, and combine them in a staging query. That staging result can stay connection-only while a later query loads the final summary to a worksheet.
How to create one in Excel for Windows
You can set the load destination while finishing a new query. The source might be a workbook, text file, folder, database, or another supported connection; the exact source-selection steps vary, but the load choice is made after Excel has created the query.
- Go to Data > Get Data and choose a source, or select data already in the workbook and use the appropriate command on the Data tab.
- Connect to the source and make any required choices in the Navigator. Select Transform Data to open Power Query Editor if you need to shape the data.
- In Power Query Editor, select Home > Close & Load > Close & Load To. If Close & Load is shown as a split button, use its dropdown to find Close & Load To.
- In the Import Data dialog, select Only Create Connection, then click OK.
If you are already in the editor and choose Close & Load without opening the dropdown, Excel may use its default load behavior, often creating a table in a worksheet. You can change that destination afterward, so an accidental table load does not require rebuilding the query.
Change an existing query to connection-only
For a query that already loads to a sheet, use Excel’s query list to change its destination. Select Data > Queries & Connections to open the pane if it is not visible. In the pane, right-click the query and choose Load To. Select Only Create Connection in the Import Data dialog and click OK.
Excel removes the query’s loaded worksheet output when you change it to connection-only; the query itself remains available in the pane. If you need to preserve the current output for reference, copy it to another location as values before changing the load setting. Be careful with formulas, charts, or other queries that refer to the table being removed, because they may rely on that output.
To reverse the change, right-click the query in Queries & Connections, choose Load To, and select a destination such as Table. You can generally choose a new worksheet or an existing worksheet cell in the dialog. The available destinations depend on the query and workbook context.
When connection-only is the right choice
Connection-only is most useful when the query supports another query or process and its raw output does not need to be visible. Common situations include:
- Staging data: Import and clean source records once, then use the cleaned query as the input to separate reporting queries.
- Combining files: Keep an intermediate folder-import query out of the sheets while loading a grouped or summarized result.
- Reducing worksheet clutter: Avoid adding a large detail table when readers need only a pivot table or a smaller final report.
- Building a query chain: Use one query as the source for another so transformations are easier to separate and maintain.
For example, imagine monthly sales workbooks with inconsistent spacing in product names. One query can import and clean every file, while a second groups sales by product and month. The first query can be connection-only; the second can load to a worksheet as a compact report. That arrangement keeps the raw, combined rows out of sight while preserving the steps that produce the report.
Connection-only versus worksheet and Data Model loads
These destinations answer different questions. A worksheet table makes the query output visible and convenient for ordinary formulas, sorting, and charting. A connection-only query keeps the output from being loaded to a sheet. Loading to the Data Model makes data available for model-based analysis, including relationships and PivotTables that use the model.
In the Import Data dialog, Only Create Connection is distinct from the Add this data to the Data Model checkbox. If your goal is simply to retain a query without loading its result to a sheet or model, choose Only Create Connection and leave the Data Model option unchecked. If you need the data for a model-based PivotTable, a connection-only setting by itself is not a substitute for adding data to the model.
A query can also have a connection while its results are loaded elsewhere. The word connection refers to the saved route and query logic for obtaining data; it does not necessarily mean that the query has no output. When choosing a destination, consider how the data will be used, not just whether you want to avoid a worksheet full of rows.
Refresh behavior and dependent queries
Connection-only queries can be refreshed. In Queries & Connections, right-click a query and select Refresh, or use Data > Refresh All to refresh workbook connections. Refreshing reruns the query against its source and updates any loaded results that depend on it. A connection-only query has no worksheet table to visibly update, but downstream queries may use its refreshed result.
Refresh success depends on the source being available and any required credentials being valid. A file-based query may fail if the file has moved; a database query may need an accessible server and authorized sign-in. Refresh All can take longer than expected if a workbook contains several sources or queries that depend on one another.
For a workbook shared with colleagues, remember that a saved query does not guarantee that every person can access its source. Recipients may need the source file, network access, or their own credentials. Check the workbook’s connections and test a refresh in the environment where it will be used.
Manage and inspect connection-only queries
The Queries & Connections pane is the main place to review queries in Excel for Windows. It lists queries even when they do not appear as worksheet tables. Right-click a query to find commands such as Edit, Refresh, Load To, and Delete; the available commands can vary with the query and Excel’s state.
Select Edit to reopen the query in Power Query Editor and inspect its applied steps. This is useful when a source column changes or a transformation needs adjustment. The preview in the editor helps show how the steps affect the data, but it is not the same as loading the output into a worksheet.
Use clear names that describe a query’s role, such as CleanedTransactions or MonthlySummary. In a chain of queries, names that distinguish source, staging, and output queries make it easier to see which ones should be loaded and which should remain connection-only. Avoid deleting an intermediate query until you know whether another query references it.
Common problems and practical checks
- The output still appears on a sheet: Confirm that you changed the load setting for the intended query, not a similarly named query. Open Load To again and check that Only Create Connection is selected.
- The query seems to have disappeared: Open Data > Queries & Connections. Connection-only means no worksheet output, not no query entry.
- A report stops updating: Check whether the report depends on the connection-only query, then refresh the relevant query or use Refresh All. Also verify source paths, access, and credentials.
- Another query reports an error: An intermediate query may have been renamed, deleted, or changed so that expected columns are missing. Edit the dependent query and inspect its steps for the point where the expected input no longer matches.
- You want a PivotTable but see no worksheet data: A connection-only query has not automatically populated a worksheet table. Choose a suitable load destination or load the data to the Data Model when that is what your analysis requires.
One common misconception is that connection-only makes a workbook smaller in every case. It avoids storing a separate worksheet output for that query, but it does not remove the query definition, eliminate the source, or guarantee a smaller file overall. Workbook size depends on what is loaded, whether data is stored in the Data Model, and other workbook contents.
Excel for Mac and Excel for the web
The instructions above target Excel for Microsoft 365 on Windows, where the Import Data dialog and Queries & Connections pane provide the described workflow. Power Query availability and load options can differ across Excel for Mac versions and Microsoft 365 updates. If using Mac, look for the query’s load or import destination controls in the Power Query interface available in that release; do not assume every Windows command or source connector is present.
Excel for the web also has a different feature set and interface from the Windows desktop application. If you cannot find Close & Load To or Only Create Connection in the web app, open the workbook in desktop Excel and set the query’s load destination there. The exact ability to edit or refresh a query online depends on the source and the features available to the account.
Frequently Asked Questions
Does connection-only still let me refresh a query?
Yes. The query remains in the workbook and can be refreshed from Queries & Connections or with Data > Refresh All, subject to source access and credentials.
Does Only Create Connection delete my query?
No. It prevents the query result from being loaded to a worksheet. The query remains listed in Queries & Connections. Changing an existing query’s load setting can remove its previous worksheet output, however.
Can I use a connection-only query in another query?
Yes. A query can serve as an input to another query without loading its own result to a worksheet. This is a common way to keep intermediate or staging results out of the report sheets.
Can a connection-only query feed a PivotTable?
Not as a worksheet table simply by virtue of being connection-only. For a PivotTable based on the Data Model, load the data to the Data Model as needed; otherwise choose an appropriate table or connection destination for the PivotTable workflow.
How do I stop a query from being connection-only?
In Data > Queries & Connections, right-click the query, choose Load To, and select a worksheet table or another suitable destination. Then confirm the dialog.
Is connection-only the same as disabling refresh?
No. Connection-only controls where query results are loaded, not whether the query can refresh. Refresh behavior depends on the query, source, credentials, and workbook settings.
Related
- Reselling Inventory Spreadsheet: Track Stock and Profit
- PowerPoint Slideshow Continuous Loop: Setup and Fixes
- Retired Budget Spreadsheet: Plan Income and Expenses
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Outlook 2019 Auto Reply Rules: Setup and LimitationsIn Outlook 2019 for Windows, create an automatic reply rule by saving a message as an Outlook template,…
- How to Compare Two Excel SheetsTo compare two Excel sheets with the same layout, put this formula in a blank column beside the…
- How to Create and Print Number Labels in Microsoft WordTo create sequential number labels in Microsoft Word, put one number per row in an Excel list, then…
- Comparison Excel Sheet Template: Build a Clear Side-by-SideA comparison Excel sheet template puts options in columns and the criteria you care about in rows, so…
- Goal Seek in Excel: Where to Find It and How It WorksIn Excel for Microsoft 365 on Windows, find Goal Seek on the Data tab: choose What-If Analysis in…
- Open and Repair in Excel: How to Recover a WorkbookTo use Open and Repair in Excel for Microsoft 365 on Windows, go to File > Open >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.