Its what happens when you have to write a lot of vlookup formulas before you can start analyzing your data. Every day, millions of analysts and managers enter VLOOKUP hell and suffer. They connect table 1 with table 2 so that all the data needed for making that pivot report is on one place. If you are one of those, then you are going to love Excel’s data model & relationships feature.
In simple words, this feature helps you connect one set of data with another set of data so that you can create combined pivot reports.
Practical Example – V(X)LOOKUP hell vs. Data Model heaven
Lets say you are looking sales data for your company. You have transaction data like below.
And you want to find out how many units you are selling by product category and customer’s gender.
Unfortunately, you only have product ID & customer ID.
With VLOOKUP Hell,
You first fetch all the customer and product data and place them in separate ranges.
Then write a vlookup formula to fetch product category, another to fetch customer gender.
Then fill down the formulas for entire list of transactions.
Now make a pivot table.
Assuming you have 30,000 transactions, you have to write 60,000 VLOOKUP formulas to create this one report!!!
With Data Model heaven,
Create relationships between Sales, Products & Customer tables
Create a pivot table
Creating a relationship in Excel – Step by Step tutorial
First set up your data as tables. To create a table, select any cell in range and press CTRL+T. Specify a name for your table from design tab. Read introduction to Excel tables to understand more.
Now, go to data ribbon & click on relationships button.
Click New to create a new relationship.
Select Source table & column name. Map it to target table & column name. It does not matter which order you use here. Excel is smart enough to adjust the relationship.
Add more relationships as needed.
Using relationships in Pivot reports & analysis
Select any table and insert a pivot table (Insert > Pivot table, more on Pivot tables).
Make sure you check the “Add this data to data model” check box.
In your pivot table field list, check “ALL” instead of “ACTIVE” to see all table names.
Select fields from various tables to create a combined pivot report or pivot chart
Example: Category & Gender Sales Report
Add category to row labels
Add gender to column labels
Add quantity to values
and your report is ready!
Things to keep in mind when you using relationships
Same data types in both columns: Columns that you are connecting in both tables should have same data type (ie both numbers or dates or text etc.)
One to one or One to many relationships only: Excel 2013 supports only one to many or one to one relationships. That means one of the tables must have no duplicate values on the column you are linking to. (for example products table should not have duplicate product IDs).
You can add slicers too: You can slice these pivot tables on any field you want (just like normal pivot tables). For example, you can further slice the above report on customer’s profession or product’s SKU size.
Benefits of Data Model based Pivot Tables
Once you have a data model in spreadsheet, you will enjoy several benefits (apart from multi-table pivots that is). They are,
Distinct counts: This simple but often tricky to calculate number is easy to get once you have data model based pivot. Just go to value field settings and change the summary type to “Distinct count”. Here is a tip explaining how to get distinct counts in Excel pivots.
Measures & DAX: Once you have a Data Model, you can unleash the full Power Pivot features on your workbook. You can create measures (using DAX language) and calculate things that are otherwise impossible with regular Excel. Here is an example of percentage of something calculation with DAX & Data Model, to get started.
Pivots from data in other files & databases: You can combine data model with the abilities of Power Query to create pivots from data in other places. For example, you can make a pivot from sales data in SAP with customer data in CRM system. Here is an overview of what is Power Query?
Convert Pivot Tables to formulas: Once you have a data model based pivot table, you can turn it in to a set of formulas. You can access this feature from “Analyze” ribbon. This will replace your pivot with a bunch of CUBE formulas. Here is an overview of CUBE formulas.
Drawbacks of Data Model:
Of course, its not all cup cakes and coffee with Data Model. There are a few drawbacks of data model based pivot tables.
Compatibility: Data model & relationship feature is available only in Excel 2013 or above. This means, you cannot create or share such pivot reports with people using older versions of Excel.
Not able to group data: In regular Pivot Tables, you can group numeric, data or text fields. But with data model pivot tables, you can no longer group data. You must create another table with the group mapping and use it as a relationship.
Ever since discovering PowerPivot, I kind of stopped using VLOOKUP (or XLOOKUP) for most of my own analysis. Now that relationships are part of main Excel functionality, I am using them even more.
What about you? Are you using relationships & data model in Excel? What cool things are you doing with it? Share your tips with us using comments.
Want even more? Try PowerPivot
If you want even more out of your reports, then try PowerPivot. It is a new feature in Excel 2013 (available as add-in in Excel 2010) that can let you do lots of powerful analysis on massive amounts of data. Here is an introduction to PowerPivot.
Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.
Read my story • FREE Excel tips book
Here is a fabulous New Year gift to you. A free 2025 Calendar Excel Template with built-in Activity planner. This is a fully dynamic and 100% customizable Excel calendar for 2025.
Great tips from you all! Thanks a lot for sharing! bsamson, particularly you helped me on a terribly annoying task. 🙂
(BTW, Chandoo, it's not exactly "Find if a range is normally distributed" what my suggestion does. It checks if two proportions are statistically different. I probably gave you a bad explanation on twitter, but it'd be probably better if you fix it here... 🙂 )
That p-value formula is really great for a statistics person like me.
What a p-value essentially is, is the probability that the results obtained from a statistical test aren't valid. So for example, if my p value is .05, there's a 5% probability that my results are wrong.
You can play with this if you install the Data Analysis Toolpak (which will perform some statistical tests for you AND provide the P Value.)
Let's say for example I've got two weeks of data (separated into columns) with the number of hours worked per day. I want to find out if the total number of hours I worked in week two were really all the different than week one.
Week1 Week2
10 11
12 9
9 10
7 8
5 8
Go to Data > Data Analysis > T-Test Assuming Unequal Variances > OK
In the Variable 1 Box, select the range of data for week 1.
In the Variable 2 Box, select the range of data for week 2.
Check "Labels"
In the Alpha box, select a value (in percentage terms) for how tolerant you are of error.
.05 is the general standard; that is to say I am willing to accept a 95% level of confidence that my result is accuarate.
Select a range output.
Excel calculates a number of results: Average (mean) for each week's data, etc.
You'll notice however that there are two P Values; one-tail and two-tail. (one tail tests are for > or .05), the number of hours I worked in week two is statistically equivalent to the number of hours I worked in week one.
So here’s a way you might want to use this. You put up a new entry on your blog. You think it’s the best entry ever! So you pull your webstats for this week and compare it to last week. You gather data for each week on the length of time a visitor spends on your website. The question you’re trying to prove statistically is whether there’s an average increase in the amount of time spent on your website this week as compared to last week (as a result of your fancy new blog post). You can run the same statistical test I illustrated above to find out. Incidentally, it matters very little to the stat test whether the quantity of visitors differs or not.
Anyhow, the Data Analysis toolpack doesn't perform a lot of stat tests that folks like me would like to have access to. In those cases I have to either use different software, or write some very complicated mathematical formulas. Having this p-value formula makes my life a LOT easier!
Take FirstName, MI, LastName in access (you can fix it to work in excel) capitalize first letter of each and lowercase the rest and add ". " if MI exists then same for last name:
Full Name: Format(Left([FirstName],1),">") & Format(Right([FirstName]),Len([FirstName])-1),"") & ". ","") & Format(Left([LastName],1),">") & Format(Right([LastName],Len([LastName])-1),"<")
I teach excel, access, etc etc for a living and i have my access students build this formula one step at a time from the inside out to show how formulas can be made even if it looks complicated. Yes I know I could just do IsNull([MI]) and reverse the order in the Iif() function but the point here is to nest as many functions as possible one by one (also I illustrate how it will fail without the Not() as it is)
if in a column we write the value of total person for eg. 10 if we spent 1.33 paise each person then how we get total amount in next column and the result will in round form plzzzzz solve my problem sir................... thank u
12 Responses to “29 Excel Formula Tips for all Occasions [and proof that PHD readers truly rock]”
Some great contributions here.
Gotta love the Friday 13th formula 😀
Great tips from you all! Thanks a lot for sharing! bsamson, particularly you helped me on a terribly annoying task. 🙂
(BTW, Chandoo, it's not exactly "Find if a range is normally distributed" what my suggestion does. It checks if two proportions are statistically different. I probably gave you a bad explanation on twitter, but it'd be probably better if you fix it here... 🙂 )
Great compilation Chandoo
For the "Clean your text before you lookup"
=VLOOKUP(CLEAN(TRIM(E20)),F5:G18,2,0)
I would like to share a method to convert a number-stored-as-text before you lookup:
=VLOOKUP(E20+0,F5:G18,2,0)
@Peder, yeah, I loved that formula
@Aires: Sorry, I misunderstood your formula. Corrected the heading now.
@John.. that is a cool tip.
Hey Chandoo,
That p-value formula is really great for a statistics person like me.
What a p-value essentially is, is the probability that the results obtained from a statistical test aren't valid. So for example, if my p value is .05, there's a 5% probability that my results are wrong.
You can play with this if you install the Data Analysis Toolpak (which will perform some statistical tests for you AND provide the P Value.)
Let's say for example I've got two weeks of data (separated into columns) with the number of hours worked per day. I want to find out if the total number of hours I worked in week two were really all the different than week one.
Week1 Week2
10 11
12 9
9 10
7 8
5 8
Go to Data > Data Analysis > T-Test Assuming Unequal Variances > OK
In the Variable 1 Box, select the range of data for week 1.
In the Variable 2 Box, select the range of data for week 2.
Check "Labels"
In the Alpha box, select a value (in percentage terms) for how tolerant you are of error.
.05 is the general standard; that is to say I am willing to accept a 95% level of confidence that my result is accuarate.
Select a range output.
Excel calculates a number of results: Average (mean) for each week's data, etc.
You'll notice however that there are two P Values; one-tail and two-tail. (one tail tests are for > or .05), the number of hours I worked in week two is statistically equivalent to the number of hours I worked in week one.
So here’s a way you might want to use this. You put up a new entry on your blog. You think it’s the best entry ever! So you pull your webstats for this week and compare it to last week. You gather data for each week on the length of time a visitor spends on your website. The question you’re trying to prove statistically is whether there’s an average increase in the amount of time spent on your website this week as compared to last week (as a result of your fancy new blog post). You can run the same statistical test I illustrated above to find out. Incidentally, it matters very little to the stat test whether the quantity of visitors differs or not.
Anyhow, the Data Analysis toolpack doesn't perform a lot of stat tests that folks like me would like to have access to. In those cases I have to either use different software, or write some very complicated mathematical formulas. Having this p-value formula makes my life a LOT easier!
Thanks!
Eric~
Fantastic stuf..One line explanation is cool.
Thanks to all the contributors
OS
Take FirstName, MI, LastName in access (you can fix it to work in excel) capitalize first letter of each and lowercase the rest and add ". " if MI exists then same for last name:
Full Name: Format(Left([FirstName],1),">") & Format(Right([FirstName]),Len([FirstName])-1),"") & ". ","") & Format(Left([LastName],1),">") & Format(Right([LastName],Len([LastName])-1),"<")
I teach excel, access, etc etc for a living and i have my access students build this formula one step at a time from the inside out to show how formulas can be made even if it looks complicated. Yes I know I could just do IsNull([MI]) and reverse the order in the Iif() function but the point here is to nest as many functions as possible one by one (also I illustrate how it will fail without the Not() as it is)
Extract the month from a date
The easiest formula for this is =MONTH(a1)
It will return a 1 for January, 2 for February etc.
if in a column we write the value of total person for eg. 10 if we spent 1.33 paise each person then how we get total amount in next column and the result will in round form plzzzzz solve my problem sir................... thank u
@Anjali
If the value 10 is in B2 and 1.33 paise is in C2 the formula in D2 could be =B2*C2
If the values are a column of values you can copy the formula down by copy/paste or drag the small black handle at the bottom right corner of cell D2
kindly share with me new forumulas.
How to convert a figure like 870.70 into 870 but 871.70 into 880 using excel formula ? Please help.