How to Add Data to Data Model in Excel

If you reflexively reach for VLOOKUP every time you need to combine two tables in Excel, it’s time to meet the Data Model. It’s one of Excel’s most powerful yet underutilized features, allowing you to build sophisticated reports from multiple data sources without a single lookup formula bogging your workbook down. This article will show you exactly how to add your data to Excel’s Data Model and start analyzing it like never before.

What Is Excel's Data Model, Anyway?

Think of the Data Model as a tiny, high-performance database living inside your Excel workbook. It allows you to import and connect millions of rows of data from multiple sources and build relationships between them. Instead of cramming all your data onto one giant, slow-as-molasses worksheet, you load separate tables into the model and then tell Excel how they relate to each other.

So, a table of sales transactions and a separate table of product details can be linked by a common column, like a 'Product ID.' Once they're connected in the Data Model, you can create a PivotTable that pulls the 'Product Category' from your product table and sums up the 'Sales Amount' from your sales table - instantly.

Working this way offers some huge advantages:

• Better Performance: It’s significantly faster. The Data Model uses a powerful engine that can process millions of rows far more efficiently than standard Excel functions on a worksheet.
• Handles Huge Datasets: A standard worksheet is limited to just over a million rows. The Data Model handles many millions of rows with ease, limited only by your computer’s memory.
• Keeps Files Lean: You can load data as a connection only, meaning the raw data doesn't even have to live in your Excel sheets, keeping your file sizes manageable.
• Clear Relationships: It ends the nightmare of nested lookup formulas. Your analysis logic is neatly contained within the relationships you build, making your workbook easier to understand and maintain.

Your Tool for the Job: Power Query

So, how do you get your information into this amazing Data Model? The primary tool for the job is Power Query. If you haven't used it, Power Query is Excel’s built-in data transformation tool (found under the 'Data' tab) that helps you get, clean, shape, and load data from an almost endless list of sources.

It's the gatekeeper to the Data Model. You don't just copy and paste data in, you use Power Query to create a repeatable process for importing and refreshing it. This ensures your data is clean and properly formatted before it ever reaches your model.

How to Add Data to the Data Model (Step-by-Step)

The best way to learn is by doing. Here are the step-by-step instructions for adding data to the Data Model from a few common sources.

Method 1: Adding a Table Already in Your Excel Workbook

This is perfect for when you already have some data typed out or pasted into a worksheet and want it to be part of a larger analysis.

1. Format Your Data as an Excel Table: Select any cell within your data range and press Ctrl + T. Make sure the "My table has headers" box is checked, and click OK. Excel will format your range as an official Table, which is necessary for Power Query. Give it a descriptive name in the 'Table Design' tab (e.g., "SalesData").
2. Launch Power Query: With a cell in your new table selected, go to the Data tab and click From Table/Range in the 'Get & Transform Data' section.
3. This will open the Power Query Editor, a new window where you can transform your data. For now, we’ll assume the data is already clean.
4. Choose Your Destination: In the Power Query Editor, click the top half of the Close & Load button in the top-left corner. We need more options, so click the dropdown arrow on the button and select Close & Load To….
5. Add to the Data Model: This is the most important step. In the 'Import Data' dialogue box that appears:

You’ll now see a 'Queries & Connections' pane on the right-hand side of your Excel window, showing that your SalesData table is loaded to the model.

Method 2: Adding Data from an External CSV File

Most business data lives outside of Excel. Here’s how to pull in data from a common format like a CSV file.

1. Get Data From Text/CSV: Go to the Data tab, click Get Data > From File > From Text/CSV.
2. Select Your File: Navigate to and select the CSV file you want to import. A preview window will pop up.
3. Transform Data: Power Query is usually smart enough to figure out the file origin, delimiter (like a comma), and that the first row is headers. Instead of 'Load', always choose Transform Data to open the full Power Query Editor and confirm everything looks right. Check that dates are formatted as dates, numbers as numbers, etc.
4. Load to the Data Model: Once again, click the dropdown on the Close & Load button and select Close & Load To…. In the dialog box, check Add this data to the Data Model and click OK. You can choose to create a connection only or load it into a table on a worksheet, but adding it to the model is the key step.

Checking Your Work: Managing the Data Model

Now that you've sent data to the model, where did it go? You can see and manage everything in the Power Pivot window.

Go to the Data tab and look for the green database symbol labeled Go to the Power Pivot Window. If you don't see it, check if you have a 'Power Pivot' tab at the top. Click it!

The Power Pivot for Excel window opens. It looks a bit like Excel, but each tab at the bottom is a different table within your Data Model. Here you can add calculated columns with DAX (Excel’s advanced formula language), create hierarchies, and manage relationships.

Creating Relationships: The Real Superpower

Adding tables is the first step, but connecting them is where the Data Model’s power is truly unlocked. Let’s imagine we’ve loaded two tables:

• A "Sales" table with columns like OrderID, ProductID, and Sale_Amount.
• A "Products" table with columns like ProductID, ProductName, and Category.

The common link is ProductID. We need to tell Excel that these two columns represent the same thing.

1. In the Power Pivot window, go to the Home tab and click Diagram View.
2. You’ll see your tables as separate boxes showing their columns.
3. Find the ProductID column in your Sales table. Click and drag it over to the ProductID column in your Products table.
4. Release the mouse button. Excel will draw a line between the two tables, indicating you’ve created a relationship.

That's it! They are now connected. You can now analyze sales data using attributes from your product list without any lookup formulas.

Using the Data Model in a PivotTable

This is the moment everything comes together. Creating a PivotTable from the Data Model allows you to slice and dice information from all your connected tables seamlessly.

1. Returning to your Excel Sheet, go to the Insert tab and click PivotTable.
2. In the 'Create PivotTable' dialog box that appears, select the option From Data Model. Click OK.
3. Look at the 'PivotTable Fields' list on the right. Instead of a single list of columns, you’ll see separate, expandable listings for each table in your Data Model (e.g., one for "Sales" and one for "Products").
4. Now, you can just drag and drop fields from any table. For example:

Instantly, you have a report summarizing sales by product category - a report that pulls data from two separate tables without any formulas. It just works.

Final Thoughts

Moving beyond simple worksheets to the Excel Data Model is a complete game-changer for anyone who regularly works with data. By using Power Query to ingest data and Power Pivot to create relationships, you can build scalable, high-performance reports that are simply impossible to create with VLOOKUPs alone.

Handling data from multiple sources is the standard for modern marketing and sales analysis, from Google Ads to Shopify to your CRM. We built Graphed to eliminate the manual work of exporting CSVs and stitching them together in tools like Excel. We help you connect your accounts in seconds and then let you build live dashboards and get instant answers by just asking questions in plain English - no wrestling with Power Query required. If you're looking for an easier way to get insights from your data, give Graphed a try.