Breaking news from around the world
Get the Bing + MSN extension
Now Available in Community - MBAS 2019 Presentation Videos
Catch the most popular sessions on demand and learn how Dynamics 365, Power BI, PowerApps, Microsoft Flow, and Excel are powering major transformations around the globe. | View Gallery
2019 release wave 2 Discover the latest updates to Dynamics 365Release overview guides and videos Release Plan | Early Access Availability
Ace your Dynamics 365 deployment with packaged services delivered by expert consultants. | Explore service offerings
Connect with the ISV success team on the latest roadmap, developer tool for AppSource certification, and ISV community engagements | ISV self-service portal
The FastTrack program is designed to help you accelerate your Dynamics 365 deployment with confidence.
FastTrack Program | Finance TechTalks | Customer Engagement TechTalks | Talent TechTalks | Upcoming TechTalks
Today’s #TipTuesday is yet another Excel tip. Can you tell I’ve been working with spreadsheets a lot lately? My blogs tend to follow my work and these last 3 weeks have been no exception. As I run into things or use something I haven’t touched in a while, I tend to blog about it!
This tip is about what I believe to be a hidden gem in Excel and that is the IFERROR formula. Over the years, I have seen many people do many funky workarounds to “pre-check” if they will get an error when there is a formula that will help you out much more easily!
The scenario I find it most useful is in conjunction with something like a VLOOKUP formula. In my simple example, I am using the data from my blog last week with a few minor tweaks to introduce gaps in the years. The data is listing returns by year. In another area of my spreadsheet, I have a sequential list of years and I want to find out the returns by year, and don’t know which years have data and which don’t.
In my first attempt, in range E5 to F10, I have a basic VLOOKUP formula. What happens with VLOOKUP if there is no corresponding match to what you want to look up? It returns the #N/A error of course! If you have a subtotal at the bottom of this column of data, then suddenly one little error throws all of those formulas off too.
I like to describe IFERROR as a “wrapper” formula, as in, it is wrapped around the outside of anything else that could return an error you want to avoid. In my case, I want to put IFERROR and the opening bracket before my VLOOKUP formula, and then the first element is the VLOOKUP formula itself, then a comma, then the 2nd element which is “what do you want to show instead of the error?” (and then the closing bracket). In this example above, I chose an empty string, two double quotes to be specific, so that it will show a blank in the cell if there is no data. The result is blanks for 2016 and 2018 where there were no returns.
In the real world, this example might be more appropriate to have a numeric replacement, like a zero. Or, you might want a message there for a user, in the case of you expecting a value to be there.
Using the same example from above, I had some fun with some alternate 2nd elements of the IFERROR formula to show several different options. Other than the last portion of the formula, the rest is identical and the VLOOKUP is just your normal syntax.
This is an enormously helpful yet simple formula that I use ALL THE TIME. It’s fantastic for VLOOKUPS but useful for other types of formulas too. I hope you find this useful!
Business Applications communities