Meet the Excel data model - Microsoft Excel Tutorial

- [Narrator] Alright, time to officially meet Excel's Data Model. I know we've started to talk about it a bit during the Power Query section, but basically the Data Model gives you that user friendly interface and those intuitive tools that we're going to use to actually build a relational database directly in Excel. Which something we really haven't been able to do without these BI tools. So with the Data Model, we'll be able to load those massive datasets that we can't fit into normal worksheets. We'll be able to create table relationships that blend data across multiple sources. And we'll be able to define custom fields like hierarchies and perspectives to do some really cool things. So two different ways to access the Data Model, first, from the Data tab. Second from the Power Pivot tab. Now, if you've got a compatible version of Excel and you don't see the Power Pivot tab, you may need to enable that tab. So you can go in to File, Options, Add-Ins, Manage COM Add-Ins and you should see a check box there. And I'll give you a quick demo when we jump into Excel. So regardless of which button you press, you're going to land in the same place which is the Data Model window. And it looks something like this. Now, we've already gotten a taste of this as we loaded those files in the Power Query section, but just remember that the Data Model opens in a separate Excel window, but it's part of the same file. So in that new window, you can view your data tables, calculate measures, define relationships. We're going to do all that in this section but note that closing that Data Model window doesn't close your Excel workbook. You can jump back and forth between them, you can open and close this Data Model window and you won't be losing any information. The two are kind of joined at the hip. So let's jump into Excel and actually see this for ourselves. Okay, so here I am back in my FoodMart Data Model workbook. If you've completed the homework from the Power Query section, you should have this new region lookup tab and those updated product and customer lookups based on the changes that you made in the homework. If not, I'd recommend going back and completing that homework because we will be using some of these tables throughout the course. Now from here, I can either go into the Data tab and click the Manage Data Model button or the Power Pivot tab and click Manage all the way in the left. Either way, it's going to popup the Data Model window just like this. And again, this window opens separately from my workbook. You can see I've got two open right now. And when I close this, it doesn't have any impact, I don't lose any information. The two are kind of joined together. The Data Model just gives me some additional modeling specific tools. And there's a little shortcut. This Excel icon at the top will just bounce me right back. So generally speaking, I like to kind of keep both the Data Model and the regular spreadsheet version open at the same time so that I can just jump back and forth. Now that one note about the Power Pivot tab, if you don't see the Power Pivot tab, try going into File, Options, Add-Ins here near the bottom, and then down at the bottom where it says Manage, you've got this dropdown list where you can manage your COM Add-Ins and press Go. And then in here, you'll see a bunch of options. If you've got access to these BI tools, you should see Power Pivot for Excel, just check that box and press OK. And the tab should popup. So there you have it. That's our basic quick tour of the Data Model. Next up, we're going to take a deeper dive into the Data versus Diagram view.