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. |
