Build Refreshable Excel Reports Without VLOOKUP (Full Transcript)

[00:00:00] Speaker 1: If your Excel reports break every time you add new data, this is probably why. VLOOKUP and XLOOKUP aren't bad formulas, but they're the wrong tool for requiring reports. Every time you copy formulas down, you're duplicating data, slowing your file, and making the report harder to maintain. In this video, I will show you the Excel workflow I use for reports instead, where you connect tables once, build one pivot table, and then everything updates with a single refresh. No more rebuilding reports every month. Hi, I'm Kevin, and let's dive in. Here, I've got sales data for my favorite company, the Kevin Cookie Company. You'll find a link to it right down below in the description. Each row is one transaction, but all I see are product IDs and store IDs. That's not exactly something I can hand off to management. So my first instinct is to start pulling in more information from the other sheets, product names, categories, prices, and regions. And that usually means adding lookup formulas, VLOOKUP or XLOOKUP, and copying them all the way down the table. Now, of course, this works at first, but once the file starts growing, you're duplicating the same data over and over again. The file slows down, and every batch of new data means more copying and fixing. That's the problem that we're going to solve. In reports like this, lookup formulas merge everything into one big table. And once that happens, the report becomes harder to change, and also harder to trust. Instead, we want to keep things separate. Tables should store data. Pivot tables should analyze data. And we just tell Excel how those tables relate to each other. When you do that, Excel handles the connections for you, and the report stays clean as it grows. Let me show you how to set that up. The first thing we need to do is get our data into a structure Excel understands. Right now, these are all just ranges, and I'd like to convert them into a proper Excel table. Let's start with the sales data sheet. Click anywhere in the data, and go up to the top tabs to insert. And here, you'll see the option to insert a table. When I hover over, you'll also notice that the shortcut key is Control-T. Let's try using that. I'll click into the data, let's press Control-T. And here, we have the option to create a table. My table has headers. Let's make sure to check that, and then click on OK. And look at that, we have our first table. In the top left-hand corner, you'll notice that the name is Table 1. Let's give it a name that's more descriptive than that. So over here, I'll highlight that, and type in sales. Naming your tables is important, because this is how Excel recognizes and connects them later. Let's now do the same thing for the other sheets. Let's jump into Products, and here, I'll press Control-T. And let's create another table. My table has headers. Click on OK. And here, too, let's also give it a more descriptive name, Products. Then, let's click into the Stores sheet, and we'll do the exact same thing. Press Control-T, and my table has headers. Click on OK. And here, let's give it a more descriptive name. I'll call this Stores. Once your data is in Tables, and you've also named it clearly, everything else becomes much easier. Now that everything is in proper Excel tables, we can load them into a data model. Now, you might be wondering, what exactly is a data model? It's simply Excel's way of working with multiple tables at once. It lets us keep our data separate. We define the relationships between all those tables, and then we analyze everything together in a single pivot table. That's all it is. To add our data to the data model, let's go back to the Sales sheet. Then, up on top, let's go to the Data tab. And over on the right-hand side, you'll see this green icon. Let's click on that for Data Model, and the first option in this menu is to Manage Data Model. Let's click on that. Here, Excel prompts us to enable the Data Analysis add-in to use this feature. That's exactly what we want, so let's click on Enable. This opens up the Power Pivot window. Let's close this for now. We'll come back to it later. Up here, let's close out of this. Now, on the top ribbon of Excel, you'll notice that there's a new tab called Power Pivot. This is where we manage the data model. So let's click on this tab, and right here, we have the option to Add to Data Model. Well, I want to add all of this sales data in this table to the data model. So right up on top, let's click on Add to Data Model. And there, we've now added the first table. Let's now minimize this window, and let's do the same to the Products table. I'll click on this sheet, and right up on top, let's add this to the data model. And right down below, you'll see that we now have this table included. Let's minimize this window once more, and then let's go to the Storage sheet, and here, we have our last table. Let's also add this to the data model. Up on top, I'll click on Add to Data Model, and we've now included all three tables into the data model. Nice work. In the top right-hand corner, let's now close this window. I'll click on the X, and that closes it out. If you ever need to get back to that view again in the top left-hand corner, under the Power Pivot tab over on the left-hand side, we have the option to manage the data model. And if you click on that, that reopens a window that we just had open. Next, we're going to define the relationships between all of the different tables. On the Home ribbon, over on the right-hand side, you'll see the option for Diagram View. Let's click on this, and here, we'll see a visual of our three different tables. We have Sales, Products, and also Stores. And within each one of these tables, you'll also see all of their fields, or the column headers for those tables. Now, right now, you'll notice that none of these tables are connected with one another. So we need to look for matching fields to make connections. Let's start with the Sales table. Here in the Sales table, I see a field titled Store ID. Now, over on the right-hand side, we have the Stores table. And right here, I also have an ID titled Store ID. So this is something these two tables have in common, and we can establish a relationship between these tables based on this field. So over here, I'll press and hold on Store ID, and we can now drag that over to the Stores table and release it on Store ID. And just like that, we've now established our first relationship between these tables. Next, let's now connect the Sales table to the Products table. And just like we did before, we have to look for a field that's common between these two tables. Right here, I see the Product ID, and down below in the Products table, I also see a Product ID. So here, I'll press and hold on this, and then drag it to Product ID in the Sales table, and that establishes a connection or a relationship between the Sales and the Products tables. Then, that's it. The relationships are now all defined. We didn't have to copy any data. We just told Excel how these tables relate to each other. Now that we have all the relationships in place, building the report's the easy part. To build the report, we're going to insert a pivot table. Here in Power Pivot, I could click on Pivot Table right up above, and that'll insert a pivot table. But let's actually look at how we could do it from the main Excel workbook. Right up on top, I'll minimize this window. Then, let's go to the Insert tab right here, and you'll notice that there's an option to insert a pivot table. Let's click on this dropdown, and at the bottom of this list, we have the option to create a pivot table from a data model. We just set up a data model, and we want to use that in our pivot table. So let's click on this option. Right over here, we have different options. Let's place the pivot table on a new worksheet, and down below, let's click on OK. Once the pivot table opens, look over on the right-hand side where you have all of the different pivot table fields. Instead of just one table, like what you get with a standard pivot table, you can now see Products, Sales, and also Stores. That's the key benefit of the data model. You have one pivot table, and now we can work across multiple tables at the same time. Let's now start by building out the report. Right up on top, I'll expand the Products table, and here I see all the different fields. Let's pull the product name down into rows, and there we see it appear on the pivot table. Let's now minimize this table, and in the Stores table, let's pull in the region into columns, and there we see it appear as the columns across the top. Now let's minimize this table, and let's expand the Sales table, and right down here, let's pull the quantity into the values. Just like that, we have units sold by product and region, and we didn't have to write a single lookup formula. This is the best part. I'm going to add a few new rows to the Sales table. Let's click back into here, and down at the bottom of the table, I've just added some new sales data, and let's jump back into the pivot table. Now, I didn't change anything here. I didn't touch anything, and up on top, let's go to Pivot Table Analyze, and here we have the option to refresh. Let's click on that, and that's it. The report updates automatically. I didn't have to copy any formulas. I didn't have to fix any references. I didn't even have to rebuild the report. This is one of the big benefits of setting things up this way. Up to this point, we've been summarizing existing data in these three different tables, but what if we want to calculate something new? For example, let's say we'd like to see the revenue for each one of these different products. We can calculate the revenue. Over on the right-hand side, within the Sales table, we have the quantity, and if we go up under Products, we have the unit price. We can take the unit price, and we can multiply it by the quantity to get the revenue, and then we can figure out what that is for each individual product. Now, of course, we could add more columns and formulas to all of the source data down below, but that gets messy fast. Instead, I would like for this calculation to live within the pivot table itself. Okay, so how do we do that? Up on top, let's click on Power Pivot, and then within the ribbon over on the left-hand side, let's click on Measures, and then select New Measure. Let's name this measure Revenue, and we'll store it in the Sales table. Now, I'll paste in a formula that calculates revenue by multiplying quantity by the unit price for every single sale, and then we'll sum everything up. So here, I've pasted in the formula. Here's the simple idea of how it works. SUMX goes through row by row through the Sales table, and it'll calculate the quantity times the price, and then it adds all of that up. You'll notice that we use RELATED here. That pulls the unit price from the Products table using the relationship that we already set up. Now, right down here, I'll format this as a currency, and then click on OK. Back here in the pivot table field list, you'll see that revenue now shows up, and here we have it in the pivot table itself. There it is. Instead of thousands of formulas in this sheet, Excel only calculates this where the pivot table needs it. That's why this approach stays fast and easy to maintain as your data grows. That should save you a lot of time. Now, at The Kevin Cookie Company, we can focus on baking cookies instead of fixing Excel reports. Thanks for watching. Please consider subscribing, and I'll see you in the next one.