Here you'll find an index of common Excel Functions written in layman's terms and in a language we all understand. No computer speak, just plain English and practical examples of Excel functions used in formulas.
Before we dive into the examples I want to cover some terminology that I’ll be using.
Anatomy of an Excel Function
Excel functions comprise of the function name and arguments, as you can see in the example below:
The arguments are the inputs required by the function and are enclosed in parentheses.
Arguments surrounded by square brackets are optional. For example, the VLOOKUP argument; ‘range_lookup’, is optional.
In some cases, if you omit optional arguments Excel will apply a default. This default will be specific to each function.
Excel Functions Library
Click the links below to jump to a section.
Array Functions
Note : these functions are only available in Excel 2021 or later and Microsoft 365.
FILTER Filter cells based on criteria.
RANDARRAY Returns an array of random numbers between 0 and 1.
SEQUENCE Returns list of sequential numbers that increment as specified.
SORT Sort cells or arrays in ascending or descending order.
SORTBY Sort a range or arrays based on criteria.
UNIQUE Extract a unique or distinct list from a range or array.
Array Shaping Functions
Note : these functions are only available in Excel 2021 or later and Microsoft 365.
EXPAND Expands or pads an array to a specified number of rows and columns.
TOROW Returns the array in a single row. Useful for combining data across multiple columns and rows into a single row.
TOCOL Returns the array in a single column. Useful for combining data across multiple columns and rows into a single column.
TRIMRANGE Returns a reference to your data that automatically detects and adjusts to the range your data occupies. Eliminating the need for complex named ranges or using functions like OFFSET.
WRAPROWS Lets you wrap (reshape) a row or column of values into rows, you specify the number of values in each row.
WRAPCOLS Lets you wrap (reshape) a row or column of values into columns, you specify the number of values in each column.
DROP Remove a specified number of contiguous rows or columns from the start or end of an array.
TAKE Extract a specified number of contiguous rows or columns from the start or end of an array.
CHOOSEROWS Extract rows from the specified column or columns.
CHOOSECOLS Extract columns from the specified rows or rows.
VSTACK Combine arrays arranged vertically into a new single array.
HSTACK Combine arrays arranged horizontally into a new single array.
Database Functions
DSUM Sum a range based on criteria. Alternative to SUMIFS.
Date and Time Functions
DATE Build a date with separate year, month and day values.
DATEDIF Find the difference between two dates in days, months or years.
DATEVALUE Convert dates stored as text to a date Excel can use in formulas etc.
DAY Returns the day of the month between 1 and 31 from a date or text.
DAYS Returns the number of whole days between two date serial numbers. It ignores time portions where included.
DAYS360 Returns the number of days between two date serial numbers based on a 360-day year, or twelve 30-day months.
EDATE Returns the same date of the month, n months before or after the specified date.
EOMONTH Returns the last day of the month, n months before or after the specified date.
HOUR Returns the hour integer ranging from 0 to 24, from a time.
ISOWEEKNUM Returns the week number of a date serial number. The first Monday of the year marks the start of week 1.
MINUTE Returns the minute, ranging from 0 to 59, from a time.
MONTH Returns the month number, between 1 and 12, from a date.
NETWORKDAYS Returns the number of whole working days between two date serial numbers, excluding weekends.
NETWORKDAYS.INTL Returns the number of whole working days between two date serial numbers, excluding weekend days of your choice.
NOW Returns the current date and time from your computer clock.
SECOND Returns the seconds, ranging from 0 to 59, from a time.
TIME Build a time value by entering separate hour, minute and second values.
TIMEVALUE Convert times stored as text to a time Excel can use in formulas etc.
TODAY Returns the current date serial number from your computer clock.
WEEKDAY Returns the day number of the week from a date serial number. The default ranges from 1 for Sunday, through to 7 for Saturday.
WEEKNUM Returns an integer representing the week number (from 1 to 54) of the year from a date serial number.
WORKDAY Returns the day number of the week from a date serial number. The default return type ranges from 1 for Sunday to 7 for Saturday.
WORKDAY.INTL Returns a date serial number that is the specified number of working days before or after the start date.
YEAR Returns the year as an integer between 1900 and 9999, from a date.
YEARFRAC Calculates the fraction of the year represented by the number of whole days between two dates.
Engineering Functions
CONVERT Converts inches to centimetres, pounds to grams etc.
Financial Functions
ACCRINT Returns the accrued interest that gets paid periodically.
EFFECT Returns the effective annual interest rate, from the nominal annual interest rate (APR).
FV Calculate the future value of an investment.
NPER Calculates the number of periods to reach an investment goal.
PMT Returns the regular and constant repayments for a loan or mortgage required to reduce the balance to zero, or an amount you specify.
STOCKHISTORY Get historical price data about financial instruments.
Information Functions
CELL Returns information about the formatting, contents or location of a cell.
N Converts a value to a number, but it's clever uses is where it shines.
SHEET Returns the sheet number of a cell reference.
SHEETS Returns the count of sheets in a given reference.
Logical Functions
IF Test for a condition to be met and return a result if true, or false.
IFS Allows for multiple logical tests without the need for nesting. New in Excel 2019.
IFERROR Hide or handle formula errors.
SWITCH Looks up a value in a list of values, and returns the result corresponding to the first matching value. New in Excel 2019.
XOR Performs an 'exclusive or' test on an array, range or one or more expressions. New in Excel 2013.
BYROW Removes the need to drag your formulas down a column by applying a LAMBDA function to each row in an array, making a single formula automatically spill down a column.
BYCOL Removes the need to drag your formulas across a row by applying a LAMBDA function to each column in an array, making a single formula automatically spill across a row.
Lookup and Reference Functions
ADDRESS Return a text value of a cell address.
CHOOSE Return a value from a list based on the position specified.
FILTER Filter cells based on criteria.
GETPIVOTDATA Get values from a PivotTable.
HLOOKUP Look up a value in the top row of a table.
HYPERLINK Insert links to internal or external locations.
IMAGE insert images into cells with a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP file types.
INDEX lookup a range of cells and return a single value, an array of values, a reference to a cell or range of cells.
INDIRECT Convert text into a cell reference.
MATCH Returns the relative position of a value. The lookup range can take the shape of a row or column.
OFFSET Return a range of cells offset from a starting cell.
SORT Sort cells or arrays in ascending or descending order.
SORTBY Sort cells or arrays based on criteria.
UNIQUE Extract a unique or distinct list from a range or array.
VLOOKUP Look up a value in the first column of a table.
XLOOKUP Like VLOOKUP but much better!
DGET Extracts a single value from a column in a range or table that matches specified criteria.
TRIMRANGE Returns a range that excludes all empty rows and/or columns from the outer edges of a range or array.
Math Functions
AGGREGATE Returns an aggregate (sum, average, min, max, count etc.) of a list or database.
MOD Returns the remainder after a number is divided by a divisor.
MROUND Rounds numbers to the nearest multiple.
RAND Returns an evenly distributed random real number greater than or equal to 0 and less than 1.
RANDARRAY Returns an array of random numbers between 0 and 1.
RANDBETWEEN Returns a random integer number between the numbers you specify.
SEQUENCE Returns list of sequential numbers that increment as specified.
SIGN Returns the sign of a number.
SUBTOTAL Choose the aggregation method e.g. SUM, AVERAGE, COUNT etc. and ignore or include filtered rows.
SUMPRODUCT Much more than just multiplying arrays and returning the sum of the results.
Statistical Functions
FORECAST Forecast future values using linear regression.
FORECAST.ETS Forecast future values using Exponential Triple Smoothing and machine learning. New in Excel 2016
FORECAST.ETS.CONFINT Returns a confidence interval for forecasted values. New in Excel 2016
FORECAST.LINEAR Forecast future values using linear regression. New in Excel 2016, replacing the FORECAST function.
RANK Return a rank for a dataset. Includes RANK.EQ and RANK.AVG
STDEV Measure how widely values are dispersed from the average value
Text Functions
CHAR Returns a character based on its Windows-1252 character number.
CLEAN Removes non-printing characters from text.
CONCATENATE Joins text from separate cells together.
LAMBDA Define custom functions with LAMBDA.
LET Define variables and intermediate calculations to names inside of a formula.
REGEX The new Excel REGEX functions revolutionize text manipulation. Available in Microsoft 365.
SUBSTITUTE Replaces new text for old text in a text string.
T Checks whether a value is text, and returns the text if it is, otherwise returns a blank.
TEXT Converts numbers to text in the format you specify.
TEXTJOIN Joins text together and can ignore empty cells. New in Excel 2019.
TEXTAFTER Extracts text after a specified delimiter.
TEXTBEFORE Extracts text before a specified delimiter.
TEXTSPLIT Splits text based on a specified delimiter.
TRIM Remove the excess spaces from text, except for single spaces between words.
