Home › Excel functions
Excel functions explained
Practical guides to the Excel functions you'll actually use at work, grouped by what they do. Each guide covers the syntax, worked examples and how to fix common errors.
Most popular
VLOOKUPFind a value in a table and return a matching value from the same row.
XLOOKUPThe modern lookup: search any column and return a match, left or right.
IFReturn one value if a condition is true and another if it's false.
SUMIFAdd up only the values that meet a condition, like sales per region.
COUNTIFCount how many cells meet a condition, such as orders per status.
INDEX/MATCHThe flexible lookup combo that works in every Excel version.
Lookup functions
Find a value in a table and return related data.
- VLOOKUPFind a value in a table and return a matching value from the same row.
- XLOOKUPThe modern lookup: search any column and return a match, left or right.
- INDEX/MATCHThe flexible lookup combo that works in every Excel version.
- HLOOKUPLook up a value in the top row of a table and return a value below it.
- INDIRECTTurn text into a cell reference: switch sheets or ranges with one cell.
Dynamic array functions
Formulas that return whole lists that update automatically.
Logical functions
Make decisions in formulas and handle errors.
- IFReturn one value if a condition is true and another if it's false.
- AND, OR & NOTCombine several conditions: all true, at least one true, or not true.
- IFSTest several conditions in order, without nested IFs.
- SWITCHTurn codes or numbers into labels by matching fixed values.
- IFERRORReplace errors like #N/A or #DIV/0! with a value or message of your choice.
Math functions
Add up, round and calculate with numbers.
- SUMAdd up numbers, ranges and whole columns, with AutoSum shortcuts.
- SUMIFAdd up only the values that meet a condition, like sales per region.
- SUMIFSAdd up values that meet several conditions, like region and date range.
- SUMPRODUCTMultiply columns and add up the results, or sum with any conditions.
- ROUNDRound to decimals, whole numbers or tens, up, down or to a multiple.
- MODGet the remainder after division: even/odd, every nth row, time past midnight.
- SUBTOTALSum, count or average only the rows that are visible after filtering.
Statistical functions
Count, average and analyse values.
- COUNT & COUNTACount numbers, filled cells or empty cells in a range.
- COUNTIFCount how many cells meet a condition, such as orders per status.
- COUNTIFSCount rows that meet several conditions at once.
- AVERAGECalculate the average of a range, with or without zeros and conditions.
- STDEVMeasure how spread out your numbers are around the average.
- MIN & MAXFind the lowest and highest value, also with conditions.
- LARGE & SMALLReturn the 2nd, 3rd or nth highest or lowest value.
Text functions
Combine, split and clean up text.
- CONCATENATEJoin text from several cells, with spaces, commas or line breaks.
- LEFT, RIGHT & MIDExtract characters from the start, end or middle of a text.
- TEXTBEFORE & TEXTAFTERGet the text before or after a character, without FIND.
- TRIMRemove extra spaces that break lookups and comparisons.
- SUBSTITUTEReplace or remove specific text inside a cell.
- UPPER, LOWER & PROPERChange text to capitals, lowercase or Title Case.
- LENCount characters in a cell, spot hidden spaces and count words.
- FIND & SEARCHFind where text appears in a cell, or check whether it's there at all.
Date & time functions
Work with dates, times and durations.
- TODAY & NOWInsert today's date or the current time, updating automatically.
- DATEBuild a real date from year, month and day, and fix text dates.
- DATEDIFCalculate age or the years, months and days between two dates.
- NETWORKDAYSCount working days between two dates, excluding weekends and holidays.
- EOMONTHGet the last (or first) day of a month, a number of months away.
Nothing matches your search. Try another word.