Skip to main content

Data Model an easy substitute for Vlookup

 you have a data set with product, date, customer, and sales information.

A data set wth Product, Date, Customer, and Quantity.

The IT department forgot to put sector in there. Here is a lookup table that maps customer to sector. Time for a VLOOKUP, right?

A second data set is a lookup table, mapping Customer to Industry Sector. Both data sets should be formatted as a table using Ctrl+T. Use the Table Name box on the Table Tools tab of the Ribbon to assign a name such as Sectors to this table.

There is no need to do VLOOKUPs to join these data sets if you have Excel 2013 or newer. These versions of Excel have incorporated the Power Pivot engine into the core Excel. (You could also do this by using the Power Pivot add-in for Excel 2010, but there are a few extra steps.)

In both the original data set and the lookup table, use Home, Format as Table. On the Table Tools tab, rename the table from Table1 to something meaningful. I’ve used Data and Sectors.

Select one cell in the data table. Choose Insert, Pivot Table. Starting in Excel 2013, there is an extra box, Add This Data to the Data Model, that you should select before clicking OK.

As you create the pivot table, the last choice in the Create PivotTable dialog is Add This Data To The Data Model.

The Pivot Table Fields list appears, with the fields from the Data table. Choose Revenue. Because you are using the Data Model, a new line appears at the top of the list, offering Active or All. Click All.

Surprisingly, the PivotTable Fields list offers all the other tables in the workbook. This is ground-breaking. You haven’t done a VLOOKUP yet. Expand the Sectors table and choose Sector. Two things happen to warn you that there is a problem.

The PivotTable Fields pane now offers a choice at the top for Active, or All.

First, the pivot table appears with the same number in all the cells.

Something is wrong. Every industry sold exactly $6.7 Million.

Perhaps the more subtle warning is a yellow box that appears at the top of the PivotTable Fields list, indicating that you need to create a relationship. Choose Create. (If you are in Excel 2010 or 2016, try your luck with Auto-Detect - it often succeeds.)

A yellow warning at the top of the PivotTable Fields says "Relationship Between Tables May Be Needed". Choose Auto-Detect or Create.

In the Create Relationship dialog, you have four dropdown menus. Choose Data under Table, Customer under Column (Foreign), and Sectors under Related Table. Power Pivot will automatically fill in the matching column under Related Column (Primary). Click OK.

There are four settings to define a relationship. The table called Data has a field called Customer. The Related Table called Sectors has a field called Customer.

The resulting pivot table is a mash up of the original data and the data in the lookup table. No VLOOKUPs required.

The pivot table is now reporting Sector from Sheet2 and Revenue from Sheet1, thanks to the Data Model and the Relationship.
Labels:

Comments

Post a Comment

Popular posts from this blog

Recover Unsaved Excel File

We normally avoid these settings but in the hour of need they save us rework time. So if your working on a excel file and you forget to save it or click on don't save by mistake, follow below steps  If the workbook was open for at least 10 minutes and created an AutoRecover version, Excel kept a copy for you. Follow these steps to get it back: Open Excel. In the left panel, choose Open Other Workbooks. In the center panel, scroll all the way to the bottom of the recent files. At the very end, click Recover Unsaved Workbooks. Excel shows you all the unsaved workbooks that it has saved for you recently. Click a workbook and choose Open. If it is the wrong one, go back to File, Open and scroll to the bottom of the list. When you find the right file, click the Save As button to save the workbook. Unsaved workbooks are saved for four days before they are automatically deleted. Use AutoRecover Versions to Recover Files Previously Saved Recover Unsaved Workbooks applies only to files that...
Post a Comment
Read more

Send Bulk Email from Excel for Outlook

  Download the File from below Link https://drive.google.com/file/d/1tcb4lzNFgEfDKsvQqCW05sgoiGFEhqcK/view?usp=sharing Instructions are given in the image below. Save the File as Excel Macro - Enabled workbook (.xlsm) Use this file to send bulk emails at a time i personally have sent more than 2000 bulk emails at a time. Error may occur if email id typed contains space etc. Only one email id one cell.
Post a Comment
Read more

Formatting Tips

1. Use tables to format data quickly Excel Tables  are an incredibly powerful way to handle a bunch of related data. Just select any cell with in the data and press CTRL+T and then Enter. And bingo, your data looks slick in no time. This has to be the best and easiest formatting tip. 2. Change colors in a snap So you have made a spreadsheet model or dashboard. And you want to change colors to something fresh. Just go to Page Layout ribbon and choose a color scheme from Colors box on top left. Microsoft has defined some great color schemes. These are well contrasted and look great on your screen. You can also define your own color schemes (to match corporate style). What more, you can even define schemes for fonts or combine both and create a new theme. 3. Use cell styles Consistency is an important aspect of formatting. By using cell styles, you can ensure that all similar information in your workbook is formatted in the same way. For example, you can color all input cells in orang...
Post a Comment
Read more