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

How to create a Waterfall Chart

Download Example Waterfall chart file from below link https://drive.google.com/file/d/17OKYxHKzT8NxWM0FuPqEb26ntzQqa29_/view?usp=sharing How to Create a Waterfall Chart in Excel If you want to build a waterfall chart of your own, we’ve got the step-by-step instructions for you. Although Excel 2016 includes a waterfall chart type within the chart options, if you’re working with any version older than that, you will need to construct the waterfall chart from scratch.  Step 1: Create a data table Let’s start with a simple table like annual sales numbers for the current year. You will see in the table below that the sales amounts vary for each month. Some months will have positive sales growth, while others will be negative.     Insert three additional columns to your Excel table to represent the movement of the columns on the waterfall chart. The base column will represent the starting point for the fall and rise of the chart. You will input all the negative numbers fro...
Post a Comment
Read more

See All Formulas in a Excel Sheet at Once

You have a spreadsheet from a former co-worker and you need to figure out how the calculations work. You could visit each cell, one at a time, and look at the formula in the formula bar. Or you could quickly toggle between pressing  F2  and  Esc  to see the formula right in the cell. But there is a faster way. On most U.S. keyboards, just below the  Esc  key is a key with two accent characters: the tilde from Spanish and the grave accent from French. It is an odd key. I don’t know how I would ever use this key to actually type piñata or frère . If you hold down  Ctrl  and this key, you toggle into something called Show Formulas mode. Each column gets wider, and you see all of the formulas. This gives you a view of all the formulas at once. It is great for spotting “plug” numbers (B9) or when someone added the totals with a calculator and typed the number instead of using  =SUM() . You can see that the co-worker left RANDBETWEEN functions in t...
Post a Comment
Read more

Introduction to Power BI and Power BI Desktop

  Introduction to Power BI and Power BI Desktop 1. What is Power BI 2. Power BI Desktop Installation 3. Power BI Desktop User Interface Power BI Desktop Installation Link https://www.microsoft.com/en-us/download/details.aspx?id=58494 Please Subscribe the Channel for Updates on new Tutorial videos https://www.youtube.com/channel/UCW_euuHC79CPXuwUDoqT5Rg Books https://www.amazon.in/Punit-Prabhu/e/ ... Business/Consulting/Corporate /Individual Training solutionsformso@gmail.com
Post a Comment
Read more