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
- Array Shaping Functions
- Database Functions
- Date and Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Logical Functions
- Lookup and Reference Functions
- Math Functions
- Statistical Functions
- Text Functions

## Array Functions

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 to Microsoft 365 users.

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

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

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