Any business in this age needs to use an array of software and tools to run efficiently. These tools help to save time and resources in the daily operations by giving on demand results from data analysis. Currently, businesses can choose from a wide range of tools, but the most popular and time proven tool is Microsoft Excel.
It is a known fact that Microsoft Excel is helping small, medium, and large-scale businesses to store, manage and make sense of data through analysis. Excel has a unique set of qualities that allow users to execute complex functions and advanced formulas in the easiest way possible.
Excel has vast purposeful applications but 95% of people use the basic form. What many don’t know is how to use excel functions and formulas to solve complex calculations. In this blog we will focus on 10 key Advanced Microsoft Excel functions and formulas.
These two functions aid users to look up a piece or pieces of information in a large set of data. Once applied, they pull the particular data and show it in a newly formed table. The VLOOKUP or Vertical LOOKUP helps users search for a value in a column and return another value from that same row. On the hand, HLOOKUP or Horizontal LOOKUP allows the user to search for a value in a row and return another value from that same column.
The VLOOKUP and HLOOKUP have two big limitations:
- The lookup column must be the left-most column in the selected data (or top-most row for a HLOOKUP). Normally your data will not come in this format.
- In large workbooks, these lookup functions can take a very long time to run.
2. INDEX & MATCH
The MATCH function checks for a value in an group of cells and returns the position # where that value is found.
The INDEX function returns a value from an array of cells based on the provided position #.
When combined, the above functions can be used to execute a VLOOKUP, with better flexibility. By combining INDEX & MATCH you can search a whole spreadsheet for values instead of being forced to only search the left-most column.
INDEX + MATCH is a powerful combination of Excel formulas that will take your financial analysis and financial modeling to the next level.
Below is an example of the INDEX and MATCH formulas when used together. In this example, we look up and return the sales of the selected part # based on the selected month. Since part # and month are both variables in the formula, we can change both!
3. The Nested ‘IF’ Function / IFS
The IF function is the foundation of all logical formulas in Excel which execute different commands depending on whether the given set of conditions are met. If you use Excel for your day to day work functions, then you most likely have had to come up with super long formulas with multiple IF functions where it is difficult to keep track of all the logic and commas. This is the Nested IF.
With the introduction of IFS function you can now simplify the dreaded nested IF. The IFS function gives similar results to nested IF formula but with an easier, easy to read method.
4. SUMIF & SUMIFS (Conditional SUM)
The above functions work the same way; they add up cells in a specified range that meet a certain condition. The difference is that SUMIF can evaluate only a single criterion, while SUMIFS allows for multiple criteria. Ensure that the order of arguments is different in each function:
SUMIF(range, criteria, [sum_range])
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], …)
range / criteria_range – the range of cells to be evaluated by the corresponding criteria.
criteria – the condition that must be met.
sum_range – the cells to sum if the condition is met.
INDIRECT(ref_text, [a1]) – returns a cell or range reference specified by a text string. It is used when one needs to convert a reference assembled as text into a proper reference.
Here is an example of the simplest INDIRECT formula to get the general idea:
INDIRECT formula is often used to dynamically refer to another sheet or workbook, to lock a cell reference or to create dependent drop-down lists.
SUMPRODUCT(array1,array2, …) is one of the few Advanced Microsoft Excel functions that handle arrays. It multiplies the supplied array components and returns the sum of the products. It is data analysis gold.
This function can be used in many different ways. It can be used to count and sum arrays like COUNTIFS or SUMIFS, with more flexibility. Further, SUMPRODUCT allows users to extend functionality when using different excel functions.
In the simple example below, we calculate the average selling price of all our products by using SUMPRODUCT to multiply Price by Quantity and then divide by the total volume sold.
It is an excellent function to calculate average returns, price points, and margins.
The CHOOSE function is one of the most under-used Excel functions. It is easy to understand yet very powerful. CHOOSE enables users to select one of up to 254 values or options based on an index number. The function is great for scenario analysis in financial modeling.
For example, imagine you have three different assumptions for revenue growth next year: 14%, 17%, and 22%. Using the CHOOSE formula you can return 17% if you tell Excel you want choice #2.
You can use the CHOOSE function to select values from different sheets, instead of using functions like INDIRECT.
[Find out how Advanced Microsoft Excel Training will improve your Business]
8. CONDITIONAL FORMATTING
This is not technically a formula, but it is an incredibly useful tool that is built right into Excel. If you go Home –> Styles –> Conditional formatting, you will find different preset options that will help you determine if certain things are true.
Instead of applying some of the formulas stated above, let Microsoft Excel do all the hard work.
9. RIGHT, MID and LEFT
These functions are used to chop up strings of text and extract only the important parts that you are interested in.
As Steve Quartrani rightly put it, “These formulas show a given number of characters from a text string. RIGHT shows you the number of characters from the right of the text string, LEFT shows you the number of characters from the left, and MID shows you the specified number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then it grabs the specified number of characters to the right of the start_number.”
In the simple examples below,
The RIGHT function extracts the 4 extreme rightmost characters of the text string (the characters to the right of the hyphen)
The MID function extracts the two characters in the middle of the text string (the characters in the middle of the hyphen)
The LEFT function grabs the 4 extreme leftmost characters of the text string (the characters to the left of the hyphen)
NOTE: As seen above, TEXT functions return numbers in text format.
10. CONCATENATE / CONCAT & TEXTJOIN
CONCATENATE is one of the easiest to learn but most powerful formulas when conducting data analysis. It merges strings of text together. Combines text, numbers, dates and more from multiple cells into one. The CONCAT function is a newer version that does the same thing except with a shorter name (less typing the better!).
Here is an example of merging text with a hyphen (“-“) in between:
The biggest drawback of CONCATENATE is that you cannot merge an array of cells together. Rather, you have to manually select each cell to merge. A later update by Microsoft solved the problem with the (for Excel 2016) function: TEXTJOIN:
Upcoming Advanced Microsoft Excel Training
If you want to know more about the above Advanced Microsoft Excel functions and Formulas, book our next training set for the 15 to 19 of June 2020.
Written by: Reuben Nzioka