For many analysts, being able to use the powerful built-in functions in Excel is essential to maximize efficiency – particularly when working with large data ranges or undertaking complicated calculations. There are many functions to choose from, ranging from simple SUM formulas and date options to more specific searches within data ranges. Individual projects may require a particular type of data analysis but inevitably all analysts will have a short list of favorite Excel functions that are used on a daily basis to save time and effort. Here is our list of functions that every finance professional should know.
Lookup Functions
Excel’s lookup functions are helpful as they search a row or column for a particular value or can locate a value in a specific position within the data.
INDEX
The INDEX function returns a value or the reference to a value from within a table or range. Useful for retrieving individual data points.
=INDEX(array,row_num,[column_num])
=INDEX(reference,row_num,[column_num],[area_num])
In this example we are being asked to find the third product in the products list. The formula highlights the data range and then specifies 3 as the data point to retrieve, which in this case is lipstick.
MATCH
The MATCH function searches for a specified item in a range of cells and returns the relative position of that item within the range.
=MATCH(lookup_value, lookup_array, [match_type])
This example asks us to find the position of Zaret in the list. Zaret is 12th in the list of names so the match function returns 12 as the answer.
VLOOKUP
The VLOOKUP function is a vertical search function within an array. It looks for a value in the leftmost column of a table, and then returns a value in the same row from a specified column.
=VLOOKUP(lookup_value,lookup_array,return_array,[if_not_found],[match_model],[search model])
This example asks us to find out what product has Zaret sold. The formula highlights that it is searching for Zaret within the data range, and then requires an output from the 3rd column along in that particular row.
An HLOOKUP works in the same way but is a horizontal rather than vertical search so will move along the rows rather than columns to find the output required. XLOOKUP is a newer Excel function and will work in any direction and return exact matches.
OFFSET
The OFFSET function returns a cell or an array a set distance away from a starting point.
=OFFSET(reference, rows, cols, [height], [width])
This example asks us to find the cell 2 rows (down) and 3 columns (right) from B14.
CHOOSE
The CHOOSE function returns a value from a list and is part of a broader set of Microsoft Excel functions that allows analysts to run different scenarios in the model.
=CHOOSE(index_num, value1, [value2],…)
In this example, revenue growth and EBITDA margin are the assumptions used to calculate the projections. The analyst has created three scenarios: worst, base and best case for both these assumptions. Using the CHOOSE function, it is possible to create a dynamic model which switches between these assumptions, by changing the scenario number in cell C6.
By toggling the value in cell C6 from 1 an to 3, analyst will be able to present either the worst, base or best case scenario for both revenue growth and EBITDA margin. The assumptions used for each scenario are presented in the tables above.
ISBLANK
The ISBLANK function is used to check whether a cell is blank and returns TRUE if it is or FALSE if it is not.
=ISBLANK(value)
Below is a list of cells that contains a different value. In this example, if the cell contains nothing at all, the ISBLANK function will return TRUE. If it contains any value, including any space, text, number, date, time, month, year, percentage, logical value, formula or even error value, ISBLANK will return FALSE.
ISERROR
The ISERROR function is a logical function that is used to identify whether cells contain an error value or not. If there is any type of error in the cell it will return TRUE as result, and if not, it returns FALSE.
=ISERROR(value)
In the below example we are asked to check for errors in the value column.
Financial Formulas
Financial formulas can significantly reduce the amount of time and effort required to complete more complete calculations.
NPV
The NPV function calculates the net present value of an investment by using a discount rate and a series of future cash flows.
=NPV(rate_value1,[value2],…)
In this example we are asked to calculate the NPV for each of the cash flows using the IRR as the discount rate.
XNP
The XNPV function calculates the net present value of a series of cash flows that don’t occur at regular intervals, using specified dates and a discount rate.
=XNPV(rate, values, dates)
In this example we are asked to calculate the NPV using the XNPV function to take into account the non-periodic dates.
IRR
The IRR function calculates the internal rate of return for a series of periodic cash flows.
=IRR(values,[guess])
In this example we are asked to calculate the annual return for the below cash streams. Note that a negative value denotes a cash outflow for the investor.
XIRR
The XIRR function calculates the internal rate of return with specified dates.
In this example we have already calculated the XNPV and we are being asked to calculate the IRR using the XIRR function to take into account the non-periodic dates.
Date and Time Functions
TODAY
The TODAY function in Excel returns the current date. The format of the date can be amended later using the format cells, date function to customize it.
=TODAY()
EOMONTH
The EOMONTH function returns the number for the last day of the month that is the indicated number of months before or after a specified date.
=EOMONTH(start_date,months)
In this example we are asked what the date (end of the month) six months from the start date is. This is useful if we are looking to establish the potential completion date which will be six months later.
YEARFRAC
The YEARFRAC function calculates as a fraction of the year, the number of whole days between two dates. This establishes the exact number of days between two transactions and can be helpful for calculations looking at accrued interest rate payments etc. The ‘basis’ element of the formula specifies which type of day count to use (such as 360 or 365 day convention).
=YEARFRAC(start_date,end_date,[basis])
The example asks us, as a fraction of the year, how many days are there from the start and end dates?
Day Count Basis
This details the convention for the months and years calculation within the YEARFRAC calculation and is useful for various financial markets.
- 0 = US (NASD) 30/360 (assumes there are 30 days in each month and 360 days in the year)
- 1 = Actual/Actual
- 2 = Actual/360
- 3 = Actual/365
- 4 = European 30/360
Concatenate Functions
The CONCATENATE functions join and combine various pieces of text within Excel. This can be helpful when editing names or codes within databases.
CONCATENATE
The CONCATENATE function joins several text items into one text item. Note that in newer versions of Excel, this function is replaced by =CONCAT.
=CONCAT(text1,…)
Note in the formula the deliberate space “ “ left between the two cell locations so that the two words have a space between them in the resulting cell.
TEXTJOIN
The TEXTJOIN function allows strings of text data to be concatenated into longer text strings. TEXTJOIN function is similar to CONCATENATE, however when we use the CONCATENATE function the cells must be selected individually.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
The delimiter is put within quote marks is the character that separates the text string (in our example, it is a comma). Adding “ignore empty” signals that empty cells are to be ignored.
Math Functions
Math functions are unsurprisingly some of the most used Excel formulas for financial analysts. Once learned they can be combined to build more complex calculations within Excel.
SUM
The SUM function adds all the numbers in a range of cells.
=SUM(number1,[number2],…)
In this example we are asked what the total units of products sold is.
MEDIAN
The MEDIAN function returns the median, or the middle number in a set of figures. If there is an even number of figures, the median is the average of the two middle figures .
=MEDIAN(number1,[number2],…)
In this example we are asked for the median sale amount per transaction.
MIN
The MIN function returns the smallest value in a set of values.
=MIN(number1,[number2],…)
In this example we are asked for the lowest amount sale amount per transaction.
MAX
The MAX function returns the largest value in a set of values.
=MAX(number1,[number2,…)
In this example we are asked for the highest number of units sold in a transaction.
AVERAGE
The AVERAGE function returns the average (arithmetic mean) of the arguments.
=AVERAGE(number1,[number2],…)
In this example we are asked for the average sale amount of the products sold.
D Functions
The D functions search through multiple data aggregation within a table (with the D standing for Database) and can be used for more detailed criteria searches.
DSUM
The DSUM function adds the numbers in a field/column of records in a list or database that match conditions specified.
In this example we are asked how many units of lip gloss Ashley has sold. The formula firstly highlights the data range, then specifies that “unit” is found, and adds the conditions that it must match Ashley and lip gloss.
DMAX
The DMAX function finds the maximum value in a field/column of records in a list or database for selected records that match conditions specified.
In this example we are asked what Ashley’s biggest sale in 2013 is. The DMAX formula is similar format to the DSUM formula above, but this time specifying the sales amount as the output.
DAVERAGE
The DAVERAGE function averages the values in a field of records in a list/database that match conditions specified.
= DAVERAGE(database, field, criteria)
In this example we are asked what the average units sold by Ashley in the south is.
Conditional Functions
The conditional functions are extremely useful and can be used in formulas with the math functions to create more explicit calculations within Excel.
IF
The IF function checks whether a condition is met, returns one value if true, another if false.
=IF(logical_test,[value_if_true],[value_if_false])
The below example asks us to identify sales of over 40 units. Analysts could use True or False rather than the Yes/No format shown below. The IF function could also return a value or 1 or 0 which could then be used for further calculations if needed.
SUMIF
The SUMIF function adds the cells specified by a given condition or criteria.
=SUMIF(range,criteria,[sum_range])
In this example we are asked what Betsy’s total sales are. The formula allows us to highlight the data range and then specify to sum the figures if they belong to Betty.
SUMIFS
The SUMIFS function adds cells in a range that meet multiple criteria.
=SUMIFS(sum_range,criteria_range1,criteria1,…)
In this example we are asked how many units of lip gloss Betsy has sold.
IFS
The IFS function evaluates multiple conditions and returns a value corresponding to the first true condition.
=IFS(logical_test1,value_if_true1,…)
The example uses the IFS statement to identify units sold as low or high depending on whether the units sold are over 40 and if the sale amount is less than 60.
IFERROR
The IFERROR function returns a specified value if a formula results in an error, otherwise returns the result of the formula.
=IFERROR(value, value_if_error).
The formula entered here provides and “Error in Calculation” output if there are any errors within the data range. It could also provide a numerical output (e.g. 1 or 0) if error data was producing #REF or similar error message which were disrupting larger calculations using the data range.
COUNTIF
The COUNTIF function counts the number of cells within a range that meet the given condition.
=COUNTIF(range,criteria)
In this example we are asked how many transactions Betsy has made. The answer is 3.
AVERAGEIF
The AVERAGEIF function returns the average (arithmetic mean) for the cells specified by a given condition or criteria.
=AVERAGEIF(range,criteria,[average_range])
In this example we are asked what is the average sale amount for sales over 30 units?
Conclusion
Using functions can significantly improve data analysis in Excel, particularly when dealing with large data ranges. By learning a few of Excel’s built-in functions it can save a lot of time and allow for deeper analysis of the underlying data. Always make sure you check the functions once inserted and ensure that the data range is correct to avoid any errors.