How to View Data Model in Excel

You've probably heard references to the mysterious "Data Model" in Excel, especially when dealing with complex PivotTables or Power Pivot. It's one of those features that sounds intimidating but is actually the key to unlocking a much more powerful level of analysis in your spreadsheets. This guide will show you exactly what the Excel Data Model is and how to view it so you can finally manage your data connections like a pro.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Even Is the Excel Data Model? (And Why You Should Care)

Think of the traditional Excel experience as working with individual, flat sheets of paper. You can use formulas like VLOOKUP or INDEX/MATCH to connect information between them, but it can get slow, messy, and complicated fast.

The Excel Data Model is an integrated database that lives inside your Excel workbook. Instead of disconnected sheets, it allows you to load multiple tables of data and, most importantly, create direct relationships between them. Imagine connecting a sheet of sales transactions to another sheet with customer information and a third sheet with product details, without a single VLOOKUP formula in sight.

Here’s why it’s a total game-changer:

• It handles massive datasets. A standard Excel sheet tops out at 1,048,576 rows. The Data Model can handle millions of rows of data without crashing your computer.
• It creates clean relationships. Connect tables using a shared column (like CustomerID or ProductID), and all your PivotTables will instantly understand how the data is linked.
• It makes reporting faster. PivotTables and charts based on the Data Model are often significantly faster and more efficient because the heavy lifting is done in the background engine, not on the worksheet.
• It centralizes your data. All your source data is in one managed place, making your workbook cleaner and easier to understand.

But before you can view it, you first need to add some data to it.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Getting Your Data into the Model: The First Step

You can’t see the stage if there are no actors on it. Likewise, you can't view the Data Model until you've loaded at least one table into it. The most common way to do this is with Power Pivot, an Excel add-in that serves as your gateway to the Data Model. If you don't see a Power Pivot tab in your ribbon, you may need to enable it first by going to File > Options > Add-ins.

Here’s the simplest way to add a table:

1. First, make sure your data is formatted as an official Excel Table by selecting a cell within your data range and pressing Ctrl + T. Give it a descriptive name in the Table Design tab (e.g., "Sales_Data" instead of the default "Table1").
2. Click anywhere inside your new table.
3. Go to the Power Pivot tab in the Excel ribbon.
4. Click the Add to Data Model button.

That's it! Your table is now part of the Data Model. For more advanced workflows, you can also add data to the model directly through Power Query (Data > Get Data) by selecting "Add this data to the Data Model" in the load options.

3 Ways to View and Manage Your Excel Data Model

Once you have data loaded, you have several ways to peek behind the curtain and see what’s going on. Some are comprehensive dashboards, while others offer a quick glance.

Method 1: Using the Power Pivot Window (The Main Hub)

This is the primary command center for the Data Model. Almost everything you need to do - from creating relationships to writing calculations - happens here. It operates in its own separate window, giving you a dedicated space to work with your data architecture.

How to open it: Go to the Power Pivot tab and click the Manage button.

The Power Pivot window will open, and it has two essential views you can switch between using the icons at the bottom of the window:

Data View

This is the default view. It looks and feels a lot like a regular Excel spreadsheet, showing your data in a grid. Each table in your Data Model appears as a separate tab at the bottom of a window, much like worksheets in a workbook. You can’t edit individual cells of data here (that has to be done in the source), but you can use this view to:

• See the raw data of each table in your model.
• Sort and filter columns to better understand the data structure.
• Create Calculated Columns using DAX (Data Analysis Expressions) formulas.

Data View is excellent for inspecting your information and adding row-by-row calculations.

Diagram View

This is arguably the most powerful and intuitive way to see your Data Model. Diagram View abandons the grid format and instead displays each table as a distinct box, with a list of its columns. It provides a high-level, visual map of your entire data structure.

The true power of this view is in creating and managing relationships. To link two tables, you simply find the common column in each box (like ProductID in a Sales table and ProductID in a Products table) and drag one field directly on top of the other. Excel will draw a line between them, solidifying the relationship.

Use Diagram View when you want to:

• Understand how all your tables are connected at a glance.
• Create new relationships by dragging-and-dropping fields.
• Delete or edit existing relationships (just right-click the line).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 2: From the PivotTable Fields Pane

You don't always need to open the full Power Pivot window to see your Data Model's structure. If you create a PivotTable from the Data Model (by clicking Insert > PivotTable > From Data Model), the PivotTable Fields pane on the right side of your screen provides a compact view.

You'll notice two tabs at the top of the Fields list: "Active" and "All". Click on All. Here, you will see a list of every table currently loaded into your Data Model. Each one has a small triangle next to it, which you can click to expand and see a list of its fields.

This view confirms which tables your PivotTable can access and makes it easy to pull fields from multiple related tables into a single report, which is the entire point of the Data Model!

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 3: Through the Queries & Connections Pane

This method doesn't show you the relationships like Diagram View, but it's crucial for understanding how your data got into the model.

How to open it: Go to the Data tab and click the Queries & Connections button.

A pane will appear on the right side of your screen listing every Power Query connection and direct link to the Data Model in your workbook. This is the best place to go if you need to refresh your data sources, edit a query transformation, or see a quick summary of everything feeding into your analysis.

Practical Tips for Working with the Data Model

Viewing the model is the first step. Managing it effectively is what comes next. Here are a few quick tips to make your life easier:

• Name Everything: Before adding tables, give them clear names (e.g., SalesTransactions, CustomerList). Working with Table1 and Table2 in the Diagram View is a recipe for confusion.
• Understand the "One-to-Many" Relationship: The most common business relationship is "one-to-many." You have one product in your Products table that can appear in a Sales table many times. The key to making this work is ensuring the column on the "one" side of the relationship has unique values.
• Use Measures, Not Just Calculated Columns: While Data View is great for adding columns, the real power of analytics in the Data Model comes from DAX Measures - calculations like Total Sales or Average Order Value - that you define once and can use in any PivotTable.

Final Thoughts

The Excel Data Model transforms what you can accomplish in a spreadsheet, turning it into a lightweight business intelligence tool. Viewing and managing this model is straightforward once you know where to look, with the Power Pivot window's Diagram View serving as the clearest visual map of how all your data connects.

While mastering Excel's Data Model is fantastic for in-depth analysis within a single workbook, the real world of marketing and sales data is often scattered across a dozen different platforms like Google Analytics, Shopify, Facebook Ads, and Salesforce. Manually downloading and loading all that data into Excel every week is a major source of friction. To solve this, we created Graphed. We link to all your data sources directly, so instead of manually prepping tables, you just ask for what you need in plain English - like "create a dashboard comparing Facebook spend to Shopify sales by campaign" - and get a live, automated dashboard in seconds.