Loading to worksheet or data model in Excel - Step-by-Step Guide

Introduction

When you import data into Excel, you can choose to load it directly into a worksheet or into the data model. Loading to a worksheet shows the data as a table you can see and edit. Loading to the data model stores the data behind the scenes for advanced analysis without cluttering your sheet.

When you want to see and work with imported data directly in your Excel sheet.

When you plan to create PivotTables or reports using multiple tables without showing all data on sheets.

When your data is large and you want to keep your worksheet clean and fast.

When you want to combine data from different sources using relationships in Excel.

When you want to use Power Pivot features for advanced data analysis.

Steps

Step 1: Click

- Data tab on the Ribbon

The Data tab options appear below the Ribbon

Step 2: Click

- Get Data button in the Get & Transform Data group

A menu with data source options appears

Step 3: Select

- Your data source type (e.g., From File > From Excel Workbook)

A file picker dialog opens to choose your data file

Step 4: Choose

- Your file and click Import

The Navigator window opens showing available tables or sheets

Step 5: Select

- The table or sheet you want to load

A preview of the data appears in the Navigator window

Step 6: Click

- Load dropdown button at the bottom right

Load options appear

Step 7: Choose

- Load To...

The Import Data dialog box opens

Step 8: Select

- Either Table to load data into a worksheet or Only Create Connection to load into the data model

Your choice is set for loading the data

Step 9: If loading to worksheet, select

- Existing worksheet or New worksheet option

The location for the data table is set

Step 10: Click

- OK button

Data loads either as a table on the worksheet or into the data model without showing on the sheet

Before vs After

Before

You have selected data to import but not chosen where to load it

After

Data appears as a table on the worksheet or is stored in the data model without showing on the sheet

Settings Reference

Load To

📍 Import Data dialog box after clicking Load To...

Choose where and how to load imported data

Default: Table (worksheet)

Existing worksheet

📍 Import Data dialog box when loading to worksheet

Set the location where the data table will appear

Default: First cell of the worksheet

New worksheet

📍 Import Data dialog box when loading to worksheet

Load data into a new blank worksheet

Default: Not selected

Common Mistakes

Loading large data sets directly into a worksheet

This can slow down Excel and clutter your workbook with too many rows

Load large data into the data model to keep the worksheet clean and improve performance

Choosing Only Create Connection without understanding it

Data will not appear on any worksheet and may confuse users expecting to see it

Use Only Create Connection when you plan to use the data in PivotTables or Power Pivot, not for direct viewing

Summary

You can load imported data either as a visible table on a worksheet or into Excel's data model for behind-the-scenes use.

Loading to the data model helps manage large or multiple data sources without cluttering sheets.

Always choose the loading option that fits your need: viewing data directly or using it for advanced analysis.