IF is the most used Excel function out there. Here are 10 advanced IF tricks to take your formulas to next-level 🚀
In this article, you will learn:
- Only one of, two out of three type rules
- Between condition check with MEDIAN
- Replacing Nested IF with shorter function
- Using boolean logic to replace IF formulas
- Arrays with IF function
- Wildcard checks with IF function
- How to use IF formula in other places
- Conditional formatting
- Data validation
- Charts
Sample data for the examples
All examples in this article use below sample data. Assume it is in the range C8:G23
You can download this data alone for practice purpose from here.
Includes sample data for practice, completed Excel workbook
#1 - Only one of condition
Situation
Identify employees who are only one of gender=male or salary under $85,000
Formula
=IF(XOR(D8="Male",G8<85000),"Include", "Exclude")
Explanation
XOR function will return TRUE if an odd number inputs are TRUE, else FALSE.
So, our XOR(D8=”Male”, G8<85000) will be useful for checking only one of condition.
Note: XOR doesn’t work when you want to check only one of when you have more than 2 conditions. For that refer to next trick.
Also read: Either Or formula in Excel
#2 - Two out of Three Check
Situation
Flag employees when they meet any two out of below three conditions.
- Department is Website
- Year of join is 2019
- Salary is above $90,000
Formula
=IF((E8="Website")+(YEAR(F8)=2019)+(G8>90000)>=2,
"Include", "Exclude")
Explanation
The trick is in understanding Excel treats TRUE as 1 and FALSE as 0.
So, the expression (E8=”Website”)+(YEAR(F8)=2019)+(G8>90000)
will be converted a bunch of 1s & 0s and added up, depending on the details of employee.
We can then simply check if such number is >=2 to see if any two out of three conditions are met.
#3 - Using MEDIAN for Between Condition
Situation
Identify employees joined between 1-Jan-2019 and 30-Jun-2019.
Formula
=IF(MEDIAN(F8,DATE(2019,1,1),DATE(2019,6,30))=F8,
"Review","")
Explanation
Normally, we use AND() function to check for between condition. But, you can also use MEDIAN for this.
The pattern goes like,
=MEDIAN(your value, above, below) = your value
The above will be TRUE if your value is between above and below values.
For example, =MEDIAN(7, 3,9) = 7 is TRUE.
Read more: How to write BETWEEN formula in Excel
#4 - Replacing Nested IF functions
Situation
Calculate staff bonus based on below rules:
- 1% for Website staff
- 3 % for Sales staff joined in 2018
- 2% for others
Formula
=IFS(E8="Website",1%,
AND(E8="Sales",YEAR(F8)=2018),3%,
TRUE,2%)
Explanation
Nested IF functions can be hard to write and tricky to maintain. That is why, you should use the newly introduced IFS() function.
The syntax for IFS goes like this:
=IFS(condition1, value1, condition2, value2…)
But, IFS() doesn’t have ELSE option…?
Well, you can use TRUE as last condition to fix this.
In the above formula TRUE, 2% part handles the ELSE case beautifully.
#5 - Boolean Logic to avoid IF formulas
Situation
Calculate staff bonus based on below rules, but don’t use any IF formulas:
- 1% for Website staff
- 3 % for Sales staff joined in 2018
- 2% for others
Formula
=2% - (E8="Website")*1% + AND(E8="Sales",YEAR(F8)=2018)*1%
Explanation
You can use boolean logic checks to altogether avoid IF formulas. This works well when your outputs are numbers.
The above formula calculates staff bonus by using TRUE=1 & FALSE=0 notion.
Let’s test it out for below staff:
For Gigi:
- 2% – (FALSE)*1% + (TRUE)* 1% = 3%
For Curtice:
- 2% – (FALSE)*1% + (FALSE)*1% = 2%
Read more: Daniel Ferry’s excellent I heart IF
#6 - Checking if a value is in another list
Situation
Check if an employee is part of on call support team
(range: C32:C36)
Formula
=IF(COUNTIFS($C$32:$C$36,C8),"On call","Not on call")
Explanation
We can use COUNTIFS or MATCH functions to do this. I prefer COUNTIFS.
Just count if a given data point is in another list.
Why don’t we check >0?
Remember, Excel treats any number other than 0 as TRUE. So we don’t need to write COUNTIFS($C$32:$C$36,C8)>0.
#7 - Arrays with IF formula
Situation
Calculate median salary of website staff
Formula
=MEDIAN(IF(E8:E23="Website",G8:G23))
Explanation
When you use arrays in the IF formula, it will return an array of outcomes too.
So for eg. =IF({TRUE,TRUE,FALSE}, {1, 2, 3}, {“A”,”B”,”C”}) will return {1, 2, “C”}
We can use this powerful idea to calculate median salary of website staff too.
What about ELSE part? It’s missing no?
If you don’t mention the ELSE part of IF formula, it will simply return FALSE for those values.
So, in our case, we get
{FALSE;90700;48950;FALSE;FALSE;107700;…FALSE}
When MEDIAN reads those values, it will ignore the FALSEs and calculate MEDIAN for rest.
Read more: Calculating RANKIFS with Excel
Situation 2
Show all names of “Finance” staff in one cell, comma seperated.
Formula
=TEXTJOIN(",",,IF(E8:E23="Finance",C8:C23,""))
Explanation
This works same as the MEDIAN(IF()) structure. For more applications of this technique, see the Excel Risk Map
#8 - Wildcard based conditions
Situation
Identify if an employee’s name contains letters bo
Formula
=IF(COUNTIFS(C8,"*bo*"),"bo person","not a bo person")
Explanation
IF function is not aware of wildcards. But we can use one of the other wildcard aware functions inside IF to solve the problem. You can use either of XLOOKUP, XMATCH, MATCH, VLOOKUP, COUNTIFS for this.
I prefer COUNTIFS.
The COUNTIFS(C8, “*bo*”) will be 1 if name in C8 has bo in it, else 0.
Rest is self-explanatory.
Read more: Making VLOOKUP formula go wild | Not so wild lookups
#9 - IF formula with Conditional Formatting
Situation
Highlight employees that meet conditions specified in below cells.
Rule
=AND($E8=$J$50,$D8=$J$51)
Explanation
When checking rules in conditional formatting you don’t need to use IF formula. Just use the condition part of the formula alone.
Here is the result of our rule.
#10 - Using IF with Charts
Situation
Make a chart with employee salaries, but highlight staff making above average salary in a different color.
Process
- Add an extra column in your data and use IF formula to check if a person’s salary is above average.
- Make chart with both original salary & the new column.
- Overlap the bars (or columns) 100%
- Color them accordingly.
Formula
=IF(G8>AVERAGE($G$8:$G$23),G8,NA())
Outcome
Resources - File & Video
Includes sample data for practice, completed Excel workbook
Watch the video & learn these techniques
More on IF formula
Resources
Check out below tutorials to master IF formulas & business logic
Homework problems
Use these homework problems to sharpen your if muscle.
What is your favorite IF formula trick?
Share it in the comments. Let’s learn from each other.
61 Responses to “What is Power Pivot – an Introduction ”
I use power pivot as ideal connection between our databases and my reports.
One of the most powerfull add-ins I have seen so far.
YouTube is blocked in Pakistan, Pakistani viewers are unable to watch videos of Youtube. Can you please share some other link ? Or Download link please.
@Rahim... Please see the video here: http://files.chandoo.org/power-pivot/Introduction%20to%20Power%20Pivot/Introduction%20to%20Power%20Pivot.html
Thanks
more thanks from pk : )
Thanks
THANKS A LOT FOR LINK. PLEASE DO THAT IN FUTURE ALSO FOR BENEFIT OF US
Hi Sir, can you please provide the sample excel workbooks for practice.
Very nice tutorial Chandoo. In my company, I am able to bring in important data from the "customer" table and the "product" table using our IBM showcase query tool. The real power will be combining non-traditional data from outside our data warehouse.
Ex: How do weather patterns effect sales?
Another key area where Power Pivots can shine is Digital Marketing. With so many touch points (Cell/Website/In Store/Email) there will be a huge need to link this data. How about a dual post with Avinash? 🙂
Sadly our company is stuck at Excel 2007 - and no plans to upgrade.
Are there any features of PowerPivot that can be used in 2007, or with an optional add-in?
@Gijs
You may want to have a read of: http://www.powerpivotpro.com/2010/12/powerpivot-for-excel-2007/
as well as the comments at the bottom of the post
Thanks for this. It was certainly mind-blowing AND you use an example that I get all the time.
I've heard about PowerPivot since 2010 and made various efforts to find a use for it. Recently someone told me that it's great but it's not worth the trouble if you don't have a massive amount of data.
What you showed isn't massive, it's more like a jigsaw puzzle. All the pieces are there, and it can be assembled with VLOOKUPs and helper columns but PowerPivot is much more elegant.
I see!
This looks awesome, but one question - is there power using this if you can pull all the data into one sheet? That is, what if my database HAS all the information I need, and I can just dump it into one tab?
Thanks!
@Vaslo
If your data is of the size that it can fit in one worksheet and can be processed quickly enough then PowerPivot may not be for you
Where PowerPivot starts to excel is in processing more than 1,048,576 rows of data or doing complex queries which take a while to process normally in Excel.
I dunno hui - I'm still kind of new to powerpivot, but even for small data sets those dax formulas are the truth.
@Dan_l
I did say "or doing complex queries" ?
You're right. You did. srry.
Although you may be tempted to ditch PowerPivot in such cases, I still advice trying it for below reasons:
Hi Chandoo, I have only this week been asked to upgrade my work pc from Windows XP to Windows 7 as well as install SSAS 2012, Office 2013 so that we can start to leverage the capabilities of Excel 2013, Powerpivot, PowerView, Sharepoint.
We have an Enterprise Data Warehouse including many disconnected data marts, sources etc. We will be using these tools for express reporting solutions that with investment can turn into full enterprise reporting solutions. The money saved in liscencing other products i.e Cognos to do similar at twice the development time does not stack up with what can be achieved with Excel PowerPivot.
Hi Chandoo. Love your newsletters, please keep them coming. Yours is one of the services that rank as 'Most Important' along with open source. Thank You.
My question is if I add PowerPivot to my Excel 2010 at work. Can I still create macros to add to reports to send to people that don't have PowerPivot?
My job is to manage data generated at a remote site and create reports that run automatically to present a filtered dashboard.
Peter
The correct way to share powerpivot reports / dashboards with end users is to publish them on a sharepoint server or similar. This lets your users interact with your data model thru slicers, timelines etc. and get the insights they want right from browser.
I am not aware of a power pivot viewer (ala tableau viewer) that lets your end users use powerpivot workbooks with out the add-in (or Excel 2013).
Another option is to use VBA in your computer to turn Power Pivot reports to static ranges or convert all power pivot tables to formulas (using Analyze ribbon > OLAP tools > Convert to formulas)
Hi Chandoo...We usually use an array formula on a large amount of data - since excel hangs we split the data into several files and then run the formula - do you think power pivot can help in this case in any way? All the data is exactly the same and can be easily combined in one file - only deterrant is the fact that excel cannot process it that way
Wow! I love PowerPivot. Thank you
Please add me to the PowerPivot notification list.
Are the 3 tables of data from the YouTube video available?
I was scared or indimidated at least, to learn PowerPivot. Thanks for helping me see the light 🙂
I have started to use PowerPivot very recently, as I currently query our SQL server and store this data in separate 'excel databases' (just tables really) which are used for client reporting & dashboard reporting. However recently, due to the physical size of these files, I have had to look for an alternative (without using Access) - this is where PowerPivot came to the rescue. Love the fact that it recognises relationships between tables - currently I have had to force this with INDEX(MATCH(COLUMN)) or similar within the tables themselves (which doesn't help my file size issue!)
Quite early days but it certainly looks like a vast improvement, and of course will be much quicker as excel will not be working as hard.
Looking forward to learning more about PowerPivot......
Wonderful tool and thanks for publishing it... awaiting for the course
You showed how to link a power pivot file to an excel file. The more data added to the excel file would then be added to the power pivot file because they are linked. What if you imported the excel data into power pivot without linking the files. Is the only way to add more data to the power pivot file, is to import a new sheet tab of data to the power pivot file and create a relationship?
I ask because a linked file I have is 14mb and non linked file is 1.4mb.
Both power pivot files have the same data. I can see the ease of the linked files but the benefit of a smaller file.
[...] week, you saw an Introduction to Power Pivot for Excel. Today, lets talk about DAX formulas & measures for Power [...]
Any chance of being able to download the original data worksheet?
Hi Chandoo,
how we will import data in power pivot 2010
[...] Introduction to Power Pivot [...]
[...] Related: What is PowerPivot & How to use it? [...]
[...] Introduction to PowerPivot [...]
Wow! Our company was considering buying an expensive data analysis suite like Tableau (which would have cost thousands of dollars), but this does everything Tableau does, and it works right inside of Excel.
Thank you! You saved us a lot of headaches and money!
Nice tutorial chandoo,it help me a lot
by the way,can you give me excel file in these tutorial,so I can practice with it,please send to mahdi.haris@outlook.com
Thanks very much
Im in Dubai we use dd/mm/yy format however poverpivot reads some dates as months and mnths as dates. Pls help me to solve this prblm
Thanks.
Iqbal
i am new in Power pivot , but i have many excel book with more than a 1,000,000 records
thanks...
[…] so much more, that that’s a subject for another post. Fortunately Chandoo already wrote it: What is Power Pivot – an Introduction. (Chandoo, that title is way too descriptive. You’ll never make Class 1 Geek unless you learn […]
Sorry for commenting on wrong post , I can't enable powerpivot to excel 2010. Please help me
Thanks in Advance.
Shree
[…] late 2012, I started learning PowerPivot. Although, PowerPivot has been around for a few years, I never used it well until then. I bought a […]
Hi Chandoo, what a terrific feature it is!
I was hoping to update my Excel version here at work and build some awesome dashboards using PowerPivot, but I believe that not just the authors but also the readers of a PowerPivot-based file must have 2010 or above version of Excel, right?
Since I create stuff for multiple persons across the globe, that would be a expansive solution (#sad) that here, in this International Budget Maker (hope you get it), will never be accepted.
Thanks for the tip, I'll definitely try it at home ;D
Is there any way
@Kdu B
PowerPivot is supported just on the latest version of Excel 2010/13.
You must use Excel 2010/13 to get full access to all PowerPivot features.
For more information, please refer to the articles below:
http://office.microsoft.com/en-au/excel/download-powerpivot-HA101959985.aspx
Can you please share the file you use in the demo so that we can try it by ourselves? thank you!
did you get the file ?
[…] Introduction to Power Pivot & how to use it? […]
Hi Chandoo,
I need in depth knowledge about Power Pivot. Can you help pls?
Regards,
Manju
I found your video clip regarding Power Pivot very impressive but I'm confusing how to get this Add-In as I've Excel 2010 in the office and Excel 2013 in the home I've tried but failed kindly guide me.
Please upload the file that you used in the demo so we can practice thanks 🙂
Great insights on PowerPivot!! 🙂
Thanks Chandoo
Hello Chandoo
New to your website and power pivot.
One quick question regarding connecting sheets in power pivot. It turns out, at least in 2010 version, that you connect sheets only with one field.
Example I have two files that have badge #s and date fields. I couldn't set up a relationship using both fields. And because the same badge # appears on different dates I couldn't set up any relationship at all, as badge # by itself wasn't a unique field.
I cheated by concatenating badge and day(date) in a new column. Then it worked 🙂
Is excel 2013 the same?
Just wondering...cheers 🙂
Welcome to our website & thanks for your comments Artieboy.
Even in 2013, you can only set relationships on single column. So you should continue with the concatenate approach.
[…] http://chandoo.org/wp/2013/01/21/introduction-to-power-pivot/ […]
It's worth noting here for the people who are having trouble finding the add-in, that the Power Pivot Add-in is only available in specific versions of Excel/Office.
It is not available in Office Home or Home and Business - if purchasing now you'd need to go for at least Office Professional or Office 365 Small Business Premium.
I believe the standalone version of Excel has it, too.
Sadly I did not find this out until after getting excited by this article! Oh well!
I was using PowerPivot with Excel 2010 extensively until 2015 when we upgraded to Office 2013.
At that point, I was unable to open my source file due to "missing providers". I posted the problem on the Microsoft site and so far no one has provided an meaningful approach to recovering my files.
Hi
Where can we download the demo excel?
Thx
Please send me the sample data workbook used in this video. Thanks.
We have MS Office Standard 2010 [Excel, OneNote, Outlook, PowerPoint, Publisher and Word]. I have two questions. Can we install the add-on from the following page and is it free:
https://www.microsoft.com/en-us/download/details.aspx?id=43348
@Abdul
Yes to both Questions
Dear Sir,
We are using the Office 365 we need to enable the power pivote in msoffice 2016
Please guide us
Regards,
Kailash
Its a wonderful explanation of the powerpivot. Could you share the sample excel file with us so that we can practice the same as you have taught.
Regards,
Sumanta