What if I told you the single click that says “Load To…” could be the difference between a spreadsheet that runs like a dream and one that chokes on its own data?
You’ve probably clicked “Load” a hundred times, watched the progress bar crawl, and then stared at a massive table that instantly fills every corner of your workbook. It feels fast, it feels simple—until you try to add a pivot, a chart, or another query and Excel starts acting like a toddler who just discovered a new toy.
This changes depending on context. Keep that in mind.
That’s the moment the Load To… option becomes more than a checkbox. It’s a tiny control panel that lets you decide where and how your data lives, and, honestly, that decision can save you hours of frustration. Let’s dig into why picking the right “Load To” destination matters, what most people get wrong, and how you can make the most of it in everyday work.
What Is the “Load To” Option
When you pull data into Excel—whether from a database, a web page, a CSV file, or Power Query—the software doesn’t just dump the rows onto a sheet. It gives you a dialog called Load To… that asks where the result should go And that's really what it comes down to. Turns out it matters..
In plain terms, it’s a menu that lets you choose one of several destinations:
- A new worksheet – a fresh tab that only contains the query output.
- An existing worksheet – you pick a cell reference and the data lands right there.
- Only create a connection – the query runs in the background, but no table appears until you explicitly ask for it.
- Add to the Data Model – the data becomes part of Excel’s internal relational engine, ready for Power Pivot, Power View, or advanced DAX calculations.
Those choices aren’t just cosmetic. Each one tells Excel how to store, cache, and refresh the data, which in turn influences performance, file size, and how you can use the data later.
Why It Matters / Why People Care
Faster refreshes, smaller files
If you’re pulling a 500,000‑row sales table every morning, loading it directly onto a sheet will balloon the workbook size and make every subsequent refresh slower. Selecting Only create a connection (and later loading to the Data Model) keeps the raw rows out of the visible grid, letting Excel keep a leaner file on disk Simple as that..
Cleaner workbooks
Ever opened a client’s workbook and found ten hidden sheets named “Query1”, “Query2”, etc.? On top of that, those are the by‑products of blindly hitting “Load”. Using Load To… to place the output on a dedicated sheet or, better yet, just keep the connection, makes the workbook easier to manage and audit.
Power‑level analytics
The Data Model is Excel’s hidden relational database. When you load a query there, you can join it with other tables, build sophisticated measures with DAX, and create interactive dashboards that would be impossible with flat tables alone.
Better control over refresh behavior
Choosing Only create a connection gives you the chance to set up incremental refreshes or schedule updates without ever touching the sheet. That’s a lifesaver for reports that run overnight on a server That's the part that actually makes a difference..
In short, the “Load To…” option is the gatekeeper between a clunky spreadsheet and a streamlined, analytics‑ready workbook The details matter here..
How It Works
Below is the step‑by‑step flow for Power Query, the most common place you’ll see the “Load To…” dialog. The same concepts apply to older “Get & Transform” tools and even to Power BI Desktop.
1. Start a query
Data → Get Data → From File → From Workbook (or any source you need).
Excel opens the Power Query Editor, where you shape the data—filter rows, rename columns, pivot, etc Nothing fancy..
2. Click “Close & Load”
At the bottom right of the editor you’ll see a drop‑down arrow next to Close & Load. Click the arrow and pick Close & Load To….
A dialog pops up with four radio buttons matching the destinations we listed earlier.
3. Choose the right destination
a. New worksheet
Best for: quick looks, one‑off reports, when you need the data visible right away.
Tip: If you anticipate adding more queries later, give the sheet a descriptive name now. It saves you from hunting down “Sheet3” later.
b. Existing worksheet
Best for: dashboards where the query output must sit next to static tables or charts.
How to use: Click the radio button, then pick a cell reference. Excel will overwrite the range each time you refresh, so make sure there’s enough room for growth.
c. Only create a connection
Best for: large datasets, data‑model‑only workbooks, or when you plan to combine several queries before showing anything.
What happens: The query runs, but no table appears. You’ll see the connection listed under Queries & Connections on the right pane That's the part that actually makes a difference..
d. Add to Data Model
Best for: anything that needs relationships, measures, or Power Pivot.
Extra step: After you load to the Data Model, you can open Power Pivot (or the Manage Data Model button) to define relationships, create calculated columns, and write DAX formulas It's one of those things that adds up..
4. Set refresh options
Right‑click the connection in the Queries & Connections pane → Properties. Here you can:
- Enable background refresh (so Excel stays usable while data updates).
- Set a refresh schedule if you’re using Office 365/OneDrive.
- Choose to refresh on open, on demand, or never.
5. Use the data
Now that the query lives where you told it to, you can:
- Insert a PivotTable directly from the connection.
- Build a chart that references the table.
- Write formulas that point to the loaded range.
- In the Data Model, add relationships and craft DAX measures.
Common Mistakes / What Most People Get Wrong
“I just hit Load and never looked at the dialog.”
That’s the fastest way to create a workbook full of hidden sheets. The habit of skipping the dialog is the biggest time‑waster I see.
Loading massive tables to a sheet
If the source has more than a few hundred thousand rows, the worksheet version will hit Excel’s 1,048,576‑row limit and start truncating data. Even if it fits, scrolling becomes a nightmare.
Forgetting to set refresh behavior
A query that loads to a sheet will refresh every time you open the file, which can stall the workbook for minutes. The fix? Uncheck Refresh data when opening the file and schedule a nightly refresh instead Worth keeping that in mind..
Overusing the Data Model without need
Power Pivot is powerful, but it adds a layer of complexity. If you only need a simple table for a one‑off report, loading to the Data Model is overkill and makes the file harder for non‑technical colleagues to understand.
Assuming “Only create a connection” means “no data at all”
You still have the data in memory; it just isn’t displayed until you pull it into a table, pivot, or the Data Model. Some users think they can skip the connection step entirely and end up with broken formulas later.
Practical Tips / What Actually Works
-
Start with a connection, add later – Create the query, choose Only create a connection, then decide if you need a table, a pivot, or a Data Model entry. This keeps the file light while you experiment.
-
Name your queries descriptively – Instead of “Query1”, use “Sales_2024_Q1”. The name appears in the Queries & Connections pane and in the Data Model, saving you from endless right‑click‑rename sessions No workaround needed..
-
Combine small tables before loading – If you have three regional sales files, import each as a connection, then use Append Queries to merge them, and finally load the combined result to the Data Model. One big table beats three separate sheets.
-
Use the “Load To…” dialog as a checklist – Before you click Close & Load, glance at the four options and ask: “Do I need this data visible now? Will it join other tables? How big is it?” A quick mental audit prevents accidental bloat.
-
use background refresh for dashboards – When a dashboard sits on a shared drive, set the query to refresh in the background. Users can still interact with the workbook while the data updates silently.
-
Document your load choices – Add a small note on the first sheet: “All data loaded via connections; tables are on Sheet2, Data Model holds the master data.” Future collaborators will thank you Took long enough..
FAQ
Q1: Can I change the “Load To” destination after the query is already loaded?
Yes. Right‑click the query in the Queries & Connections pane, pick Load To…, and select a new option. Excel will move the data accordingly, though you may need to adjust any formulas that referenced the old location But it adds up..
Q2: Does loading to the Data Model increase file size?
Slightly, because the data is stored in a compressed columnar format. That said, it’s usually smaller than a flat table on a sheet, especially for large datasets, because Excel can compress repeated values efficiently Simple, but easy to overlook..
Q3: I have a workbook that crashes when I refresh a query. Should I switch to “Only create a connection”?
Often, yes. A crash usually means the sheet version is hitting memory limits. Loading only the connection (or the Data Model) reduces the amount of data rendered on screen, which eases the strain Most people skip this — try not to. Turns out it matters..
Q4: Can I load the same query to multiple places?
You can. After the initial load, right‑click the connection and choose Load To… again. Each time you pick a new destination, Excel creates another table or pivot based on the same underlying query It's one of those things that adds up..
Q5: Is “Load To…” available in older versions of Excel?
The exact dialog appears in Excel 2016 and later (including Office 365). In Excel 2013 you’ll find a similar option under Properties → Load To… after you close the query editor.
Choosing the right “Load To…” destination isn’t a cosmetic tweak—it’s a strategic decision that shapes how your workbook behaves, how fast it runs, and how easy it is for others to understand.
Next time you pull data, pause for a second, open that tiny dialog, and ask yourself where the data really belongs. But you’ll be surprised how much smoother your spreadsheets become. Happy querying!
Navigating the process of loading data between sheets can feel like a puzzle, but with a few mindful steps, you can streamline the workflow and ensure your spreadsheets remain organized. By utilizing the “Load To…” dialog effectively, you not only keep your workspace tidy but also anticipate potential issues before they arise. Remembering to review your destination carefully—especially regarding sheet references and data model integration—can save you from unnecessary complications down the line Most people skip this — try not to..
Understanding the nuances of how Excel stores and refreshes data also empowers you to make informed decisions about when and where to load information. Whether you’re updating dashboards or simply transferring figures, maintaining clarity in your approach ensures consistency across projects Easy to understand, harder to ignore..
All in all, mastering the “Load To…” function is more than a technical task; it’s a habit that enhances efficiency and collaboration. By integrating these practices, you’ll find your workflows more reliable and your results more impactful. Embrace these strategies, and let them guide your next data load with confidence Most people skip this — try not to. Which is the point..
People argue about this. Here's where I land on it And that's really what it comes down to..
