How to Use Data Model in Excel

If you've ever tried to analyze data from multiple tables in Excel, you’ve probably felt the pain of VLOOKUPs and INDEX/MATCH formulas chaining sheets together. It works for a while, but it quickly becomes slow, complex, and error-prone. Excel’s Data Model is the built-in solution that transforms your spreadsheet program into a surprisingly powerful business intelligence tool, letting you handle millions of rows and connect data tables just like a real database. This article will walk you through exactly what the Data Model is, how to build one, and how to use it to create insightful reports.

What is the Excel Data Model?

Think of the Excel Data Model as a tiny, high-powered database that lives inside your workbook. Instead of putting all your data into a single, massive worksheet - which is limited to about one million rows and gets incredibly slow - the Data Model stores multiple, related tables in a highly compressed format. This allows you to bypass worksheet limits and perform complex analysis across different datasets without crashing your computer.

The main benefits of using the Data Model are:

• Handling massive datasets: Work with millions of rows of data far beyond Excel’s one-million-row worksheet limit. The model compresses the data, making your files smaller and faster.
• Creating relationships: You can connect different tables using a common column (like an OrderID or Email Address). This eliminates the need for messy lookup formulas to pull data together.
• Centralized analysis: Once relationships are built, you can create a single PivotTable or PivotChart that pulls fields from all your connected tables, giving you a unified view of your data.
• Advanced calculations with DAX: The Data Model uses a formula language called Data Analysis Expressions (DAX), a more powerful and flexible alternative to standard Excel formulas for writing sophisticated business logic.

In short, it lets you stop struggling with giant, clunky spreadsheets and start building clean, efficient, and powerful reports.

Step 1: Get Your Data into the Model Using Power Query

The gateway to the Data Model is a tool called Power Query (found under the “Get & Transform Data” section of the Data tab). Power Query is designed to fetch, clean, and shape your data before you analyze it. Let's walk through an example using two common tables: a sales log and a product list.

Imagine you have two separate CSV files (or Excel sheets):

1. Sales.csv: Contains SaleID, ProductID, Quantity, and SaleDate.
2. Products.csv: Contains ProductID, ProductName, Category, and Price.

Our goal is to analyze total sales revenue by product category. Doing this would normally require VLOOKUP to pull the Category and Price into the sales sheet. With the Data Model, we connect them instead.

Loading Your First Table

First, let’s load the Sales.csv file.

1. Navigate to the Data tab in Excel.
2. In the “Get & Transform Data” group, click on From Text/CSV.
3. Locate and select your Sales.csv file and click Import.
4. A preview window will appear. Since our data is clean, click the dropdown on the “Load” button and select Load To.... This is the most important step.
5. In the “Import Data” dialog box, select Only Create Connection and, crucially, check the box at the bottom that says Add this data to the Data Model. Click OK.

You won't see the data dump into a worksheet. Instead, you'll see a “Queries & Connections” pane appear on the right, showing that your Sales data is loaded into the model. Repeat these same steps for your Products.csv file, making sure you select "Only Create Connection" and "Add this data to the Data Model" again.

You now have two tables loaded into your workbook's Data Model, ready to be connected.

Step 2: Manage the Model and Create Relationships

To view and manage the data you’ve loaded and create connections between tables, you need to use the Power Pivot window. Power Pivot is an Excel add-in that serves as the command center for your Data Model.

Enabling the Power Pivot Add-in

If you don't see a “Power Pivot” tab in your Excel ribbon, you'll need to enable it first. It only takes a minute:

1. Go to File > Options.
2. Click on Add-ins on the left.
3. At the bottom, next to “Manage,” make sure COM Add-ins is selected, and click Go....
4. Check the box for Microsoft Power Pivot for Excel and click OK.

A new “Power Pivot” tab will now appear in your ribbon. Click on it, then click the green Manage button to open the Power Pivot window.

Creating a Relationship

The Power Pivot window looks like a separate application. You'll see your Sales and Products tables on different tabs at the bottom. The magic happens in the "Diagram View," where you can visually link your tables.

1. In the Power Pivot window’s Home tab, click on Diagram View.
2. You’ll see your two tables represented as boxes, with all their columns listed.
3. Identify the common column that connects these tables. In our case, it's ProductID.
4. Click and drag the ProductID field from the Products table over to the ProductID field in the Sales table.
5. Release the mouse button. A line will appear connecting the two tables.

This line represents a "one-to-many" relationship. In business terms, this means one product can appear in many sales transactions. With this relationship established, Excel now understands how sales and products are linked.

Step 3: Analyze Your Connected Data with a PivotTable

Now for the payoff. With the relationship built, you can create a PivotTable that seamlessly pulls and summarizes data from both tables at once.

1. From the Power Pivot window, click on the PivotTable icon in the Home tab.
2. In the “Create PivotTable” dialog box, choose “New Worksheet” and click OK.
3. You’ll be taken back to Excel with an empty PivotTable. Look at the “PivotTable Fields” pane on the right. You should now see both the Products and Sales tables listed, with their fields accessible.
4. Now, let's build the report we wanted:

Instantly, you have a report showing the total quantity of items sold for each product category. Excel achieved this by looking at Quantity from the Sales table and using the relationship you built to find the corresponding Category in the Products table. No VLOOKUP needed!

A Quick Introduction to DAX Formulas

Our report shows quantity, but what about total revenue? To get that, we need to multiply Quantity by Price. The Price, however, lives in the Products table. This is where DAX comes in.

DAX is the formula and query language used in the Data Model. We'll use it to create a "Calculated Column" for revenue.

1. Go back to the Power Pivot window and select the Sales table.
2. Find the column on the far right that says Add Column. Click inside its header.
3. In the formula bar at the top, type the following DAX formula:

=[Quantity] * RELATED(Products[Price])

1. Press Enter. A new column will instantly be created and calculate the revenue for every single sale. Let's rename this column Total Revenue by right-clicking its header and selecting "Rename Column."

The key function here is RELATED(). It tells Excel, "For this row in the Sales table, travel along the established relationship, find the matching product in the Products table, and bring back its price."

Now, go back to your PivotTable in Excel. Click inside it and look at the "PivotTable Fields" pane again. Under the Sales table, you’ll see your new Total Revenue field. Drag it into the Values area to instantly summarize revenue by category!

Final Thoughts

Learning how to use the Excel Data Model, Power Query, and Power Pivot is a game-changer. It elevates your skillset from basic spreadsheet analysis to building scalable, interactive reports that can handle huge amounts of data from multiple sources. It’s the hidden BI engine within a tool you already use every day.

When it comes to analyzing marketing and sales data, we know the process of connecting data from different platforms can get really complex. Manually exporting CSVs from Google Analytics, Facebook Ads, Shopify, and your CRM just to load them into a data model is tedious and time-consuming. At Graphed , we automate all of that for you. Our goal is to let you skip the manual data wrangling and a complex setup entirely. By connecting your SaaS tools directly, we let you build dashboards and reports by simply describing what you want in plain English, giving you back hours to focus on strategy, not just data preparation.