Microsoft Excel is one of the best spreadsheet tools out there, and it comes packed with an insane amount of hidden or less-known functions. If you want to become a true Excel expert, there are a lot of formulas and features you want to master. In this article, we will explore some of these hidden goodies that will take your expertise to the next level. Let’s have a look!
-
Coupling VLOOKUP with INDEX + MATCH
VLOOKUP is a valuable function that allows you to search for something in the spreadsheet and return it as a value. However, this function has a severe limitation – namely, you can only search for information stored in the leftmost column.
You can work around VLOOKUP’s limitation by combining it with the INDEX and MATCH functions to search the entire spreadsheet for values. The formula should be like this: =INDEX(list of values, MATCH(what you want to lookup, lookup column, sorting identifier)).
-
Random Number Generator
Did you know that Excel comes with an incredibly useful RNG? The RAND function is pretty basic, and only returns a number between 0 and 1. However, the RANDBETWEEN function returns a random integer between two values. You just need to set the low and high end of the range – say 1 and 20. A formula like: =RANDBETWEEN(5, 10) will return you a whole number between 1 and 20. That’s particularly useful during your D&D sessions!
-
Conditional Formatting With Formulas
A relatively less-known tool in Excel is the Conditional Formatting, a fantastic function that can transform a spreadsheet into an automated dashboard. Some of the Conditional Formatting options allow you to change cells highlighting, coloring or shading according to your needs.
For example, using less-than or greater-than formulas you can set rules to turn a cell red whenever a value is below a certain number – say, to warn you that just ran out of budget.
-
Convert to Roman Numerals
Roman numbers are sometimes necessary, but they’re extremely complicated to understand, especially when you go beyond the first ten (or, I should say “X”). Instead of fumbling with a confusing mess of X, V, C, M and L, you can easily type 1987 in Roman numerals with the ROMAN function. Just use a formula like =ROMAN(number to convert, optional style), and Excel will take care of everything!
-
SUMIF and COUNTIF
The SUMIF and COUNTIF are two advanced conditional functions that allow you to sum or count only those cells that meet certain criteria. For example, if you want to count only those candidates who have at least 5 years of experience in a certain role, you can use COUNTIF to skip all cells that return a number of years of experience which is below 5. The syntax is: =COUNTIF(D5:D12,”>=21″).
-
The CHOOSE Function
Despite being quite simple, the CHOOSE function has many practical applications that could save you a lot of time and hassle. The CHOOSE value may be easily applied to the entire table.
For example, it can be used for scenario analysis in financial modeling when you have different assumptions for revenue growth across the year. Using the CHOOSE formula you can return one of these values (say, 6%, 9%, and 15%) by telling Excel which one you like. The basic formula is: =CHOOSE(choice, option1, option2, option3).
Conclusion
It’s hard to imagine how many built-in tools and features you can find inside Microsoft Excel. The more you know about this fantastic software, the more you will understand how impressive it really is.
