Excel FormulasHow the categories are arranged
Lookup functions fetch a value from a table: the price for a code, the name for an ID. VLOOKUP is the classic, XLOOKUP the modern replacement, and INDEX with MATCH the pair that works in every version.
Logical functions make decisions. IF chooses between two results. IFS and SWITCH handle several. AND, OR and NOT combine conditions, and IFERROR deals with formulas that fail.
Math functions add, round and divide. The conditional adders SUMIF and SUMIFS are in this group, as is SUBTOTAL, which respects filters.
Statistical functions summarise a set of numbers: averages, counts, largest and smallest, and ranks. The COUNT family is here.
Text functions cut text into pieces, join pieces together, change case, remove spaces and convert between text and numbers. They are the tools for cleaning imported data.
Date and time functions work with dates: today's date, the parts of a date, the gap between two dates, month ends and working days.
Dynamic arrays are the functions introduced with Excel 2021 that return a whole list from one formula: FILTER, SORT, UNIQUE and SEQUENCE.
Financial and Information hold PMT, for loan instalments, and the IS functions that test what a cell contains.
Related functions share a page
Functions that are always learnt together are explained together. LEFT and RIGHT are one page, as are ROUND with ROUNDUP and ROUNDDOWN, and COUNT with COUNTA and COUNTBLANK. Comparing them side by side on the same sample shows the difference faster than separate pages could.
Not sure which function you need?
Describe the job in the search box. If that does not find it, these pointers may help.
| I want to | Look at |
|---|---|
| add up rows that meet a condition | SUMIF, SUMIFS |
| count rows that meet a condition | COUNTIF, COUNTIFS |
| bring a value from another table | XLOOKUP, VLOOKUP |
| show one thing or another depending on a value | IF, IFS |
| split a name or a code | LEFT, RIGHT, MID, FIND |
| join cells together | CONCAT, TEXTJOIN |
| remove extra spaces | TRIM |
| work out an age or the days between dates | DATEDIF |
| find a due date or month end | EDATE, EOMONTH, WORKDAY |
| list the rows that match | FILTER |
| list each value once | UNIQUE |
| find the top or bottom values | LARGE, SMALL, RANK |
| total only what a filter shows | SUBTOTAL |
| work out a loan instalment | PMT |
For ideas that span several functions, see the guides.
This is an independent help site. It is not connected with or approved by Microsoft. Excel and Microsoft 365 are trademarks of Microsoft Corporation, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.