Excel FormulasHow to use this site
If you know the name of the function, type it in the box above, or pick it from the list. Each function has its own page.
If you only know the job, type that instead: "lookup", "count", "date", "join text", "remove spaces". The box searches the descriptions as well as the names.
If you are new to formulas, start with the guide How to write a formula in Excel and then the dollar sign in cell references. Those two explain what every other page takes for granted.
If a formula is showing an error, go to Excel formula errors and how to fix them.
What is on each function page
Every page follows the same plan, so that you can find what you need quickly.
- One sentence saying what the function does.
- The syntax, with each argument explained in plain words.
- A small sample sheet and several formulas that use it, each with its result. You can copy any formula with one tap.
- How it works, the details that the short description leaves out.
- Common mistakes and the errors they cause.
- Which versions of Excel have the function, and how Google Sheets differs.
- Related functions.
The results are real
Explanations of spreadsheet functions on the web often contain results worked out in someone's head, and some are wrong. Here, a script places each sample sheet and each formula into Microsoft Excel and records what Excel displays. If a formula on this site says the answer is 8750, Excel said so. The date of the check and the version of Excel are given at the foot of each page.
Which version of Excel is this for?
The pages cover Excel 2016, 2019, 2021 and Microsoft 365 on Windows and Mac, and Excel on the web. Most functions here have existed for decades and work everywhere. A few are newer, and each page says so:
- Excel 2019 and later: IFS, SWITCH, TEXTJOIN, CONCAT, MAXIFS, MINIFS.
- Excel 2021 and Microsoft 365: XLOOKUP, XMATCH, FILTER, SORT, SORTBY, UNIQUE, SEQUENCE.
If one of these shows #NAME? on your computer, your version does not have it, and the page suggests an older function that does the same job.
The ten functions worth learning first
If you learn these, you can do most everyday spreadsheet work.
- SUM and AVERAGE, for totals and means.
- IF, for decisions.
- COUNTIF and SUMIF, for totals and counts with a condition.
- VLOOKUP or XLOOKUP, to fetch a value from another table.
- IFERROR, to handle a lookup that finds nothing.
- ROUND, for money.
- TRIM, to clean text.
- TEXT, to put dates and numbers into sentences.
- TODAY and DATEDIF, for ages and deadlines.
- SUMIFS and COUNTIFS, for reports with several conditions.
Questions people ask
What is the difference between a formula and a function?
A formula is anything you type that begins with an equals sign and works something out, such as =A1+A2. A function is a named, ready-made calculation that you can use inside a formula, such as SUM. =SUM(A1:A10)*2 is a formula that uses one function.
Why does my formula show as text and not calculate?
The cell was formatted as Text before you typed, or there is a space before the equals sign, or Show Formulas is switched on. The guide to writing formulas explains the cure.
Do these formulas work in Google Sheets?
Nearly all of them do, unchanged. Each function page has a note on Google Sheets, and the guide Excel and Google Sheets: where formulas differ lists the differences.
My formulas use semicolons, not commas. Why?
The character between arguments follows your computer's regional settings. In countries where a comma is the decimal mark, Excel uses a semicolon between arguments. Replace the commas in our formulas with semicolons.
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.