Use the Excel Data Model to avoid using VLOOKUPs when analyzing data

this video is about how to avoid using the vlookup function when you're doing certain kinds of analysis on corporate data it is also an introduction to the workings of the Excel data model so what are these situations where you might use for you look up when you extract data from a corporate system such as a sales or ERP system you often end up getting codes rather than names of the field so you get as shown here perhaps a country code in a product group code not the name of the country or the product group if you were to create a pivot table from that data it might end up looking something like this and would be pretty meaningless so this is where you might download a country code table and a product group code table that might look something like that then this is where you usually lookups you this time we'll create a pivot table using the names of the country and product group rather than the codes and as you can see that is much more useful but there's a better way using excels data model and the power pivot add-in the first thing I'm going to do is convert all these excel ranges to tables and I know some of you may have already done this if you use structured references while I'm doing that I want to remind you to thoroughly test and check all the work that you produce using these techniques you'll see that I've added the table for months and I've called it time not strictly necessary here but it's important if you're pulling data from several data tables then I'm going to add these tables to the data model I do that by clicking on the power pivot tab add data model so these are the corresponding tables in the data model in this case they're linked tables because they're linked to tables in Excel in reality it's likely that you will import data without putting it into a spreadsheet you will use power query imported directly into the data model and that way you're not limited to about a million rows you can have tens or even hundreds of million rows of data so bit of formatting now we can switch to diagram view and we'll rearrange the tables in what's called a star schema layout with the fact table or data table in the middle and the dimension or look-up tables around the outside then we use drag-and-drop to create relationships between the fact table and the dimension tables and having these relationships in your data model means you don't need to use the vlookup function index-match or the even the new X lookup function back in Excel we can create a pivot table using as the datasource this workbook data model and using the country and product group names from the respective dimension tables one of the benefits of using excels data model is you can take advantage of cube functions click on our lab tools and convert to formulas this converts your pivot table into a cube formula report if you look at one of these cells you'll see it's a cube value function and if we trace precedents you can see how that function is driven by the column header row label report filter with cube functions they are like regular Excel functions and unlike a pivot table you can move parts of the report around wherever you want for example I could insert a column and create a product subtotal just as an example and cube functions are way easier to work with than those awful get pivot data functions managing your tables in the data model allows you to have more complex schemas such as in this workbook where I have two fact tables one for actuals and one for budget I can report on actual and budget in the same report because both those tables are connected to all of the dimension tables but that's a topic for another day you can find more videos like this on YouTube in the Excel craft comm Channel and on the Excel craft comm website thank you for watching

Channel: ExcelcraftDotCom

Views: 16,275

Rating: 4.9710145 out of 5

Keywords: Excel, PowerPivot, Power Pivot, PivotTable, cube function, cube formula report

Id: yRxnVbKvGiU

Length: 6min 14sec (374 seconds)

Published: Sun Mar 29 2020