10 Key Advanced Microsoft Excel Functions and Formulas
June 9, 2020
Any business in this age needs to use various software and tools to manage and operate efficiently. The tools help to save time and resources in the daily operations of the business by giving instantaneous results from the analysis. There are a lot of tools and software available to businesses today, but the most popular and famous tool is Microsoft Excel.
It is not a fallacy but a stated fact that Excel is helping small, medium, and large-scale companies to store, maintain and analyze data into insightful information with its illustrious set of qualities to execute functions and advanced formulas.
Excel has its vast purposeful applications, 95% of the users apply the basic form. There are functions and advanced excel formulas that can be used for complex calculations. In this blog we will focus on some key Advanced Microsoft Excel functions and formulas.
The functions are used to look up for a piece of information in a large segment of data and pull that data to your newly formed table. The VLOOKUP allows you to search for a value in a column (“V” for vertical) and return another value from that same row. The HLOOKUP allows you to search for a value in a row (“H” for “horizontal”) 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). Often your data will not come in this format.
- In large workbooks, these lookup functions can take a very long time to run.
INDEX & MATCH
The MATCH function looks up a value in an array 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 #.
Together these functions can be used to simulate a VLOOKUP, only with more 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.
Here is an example of the INDEX and MATCH formulas combined. 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!
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 frequently then at some point you have probably had to create a crazy long formula 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 provides the same results as a nested IF formula but with a much simpler and easy to read method.
SUMIF & SUMIFS (Conditional SUM)
Both functions add up the 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. Please pay attention 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 Microsoft Excel functions that handle arrays. It multiplies the supplied array components and returns the sum of the products. It is data analysis gold.
It’s quite a versatile function and can be used to count and sum arrays like COUNTIFS or SUMIFS, but with added flexibility. You can also use other functions within SUMPRODUCT to extend its functionality even further.
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 allows you 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, while avoiding volatile functions like INDIRECT.
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 can select many options that will give outputs if certain things are true.
You can do a lot of this with the formulas mentioned before, but why not let Excel do the hard work.
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.
These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters from the right of the text string, LEFT gives you the number of characters from the left, and MID gives 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.
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. Instead, you must manually select each cell to merge. Microsoft fixed this problem with the new (for Excel 2016) function: TEXTJOIN: