Excel functions, explained with real examples

43 functions plus a keyboard-shortcut cheat sheet, each with syntax, a worked example, the mistakes people actually make, and a free graded exercise.

Each guide below is written for people who use Excel at work or are about to: business school students, analysts, and anyone preparing for an Excel test for a job interview. Every page shows the syntax, walks through a realistic example, and lists the specific mistakes that produce wrong answers without an error message. When you are ready, the button at the bottom of each guide opens a matching practice exercise that checks your formula instantly.

Lookups

Pull a value from another table by matching a key.

• XLOOKUP

  A more flexible lookup than VLOOKUP. It searches one range and returns from another, in either direction.

• VLOOKUP

  Searches the first column of a table for a match, and returns a value from another column in that same row.

• INDEX/MATCH

  Two functions, paired together: MATCH finds the position, INDEX returns the value at that position.

• HLOOKUP

  VLOOKUP's sideways sibling: looks for a match across the top row of a table, then reads down.

Conditional sums and counts

Totals, counts and averages that only include the rows you care about.

• SUMIF

  Adds up only the cells that meet one condition: the single-criterion sibling of SUMIFS.

• SUMIFS

  Sums a range, but only for rows where every listed condition holds.

• COUNTIF

  Counts how many cells in a range meet one condition.

• COUNTIFS

  Counts rows that satisfy every one of several conditions at once.

• AVERAGEIFS

  Averages a range, but only for rows where every listed condition holds.

• SUMPRODUCT

  Multiplies two (or more) ranges element by element, then sums the results, all in one formula.

Logic and error handling

Decisions inside a formula, and what to do when one fails.

• IF

  Tests a condition and returns one value when it's true, another when it's false.

• AND / OR / XOR / NOT

  The boolean building blocks. Combine multiple TRUE/FALSE conditions into one, or flip a condition around.

• IFS / SWITCH

  Two ways to avoid a long chain of nested IFs: IFS checks a list of conditions in order, SWITCH matches one value against a list of possibilities.

• IFERROR

  Catches a formula that would return an error and returns something else instead.

• ISBLANK / ISNUMBER / ISTEXT / ISERROR / COUNTBLANK

  A small toolkit for checking a cell rather than calculating with it: is it empty, is it the right type, did the formula above it fail?

Finance functions

Loans, discounting, returns and depreciation: the functions analysts use daily.

• PMT

  Calculates the fixed periodic payment on a loan (or the fixed periodic contribution to reach a savings goal) at a constant interest rate.

• FV / PV

  FV projects what a series of regular contributions grows into by a future date; PV works backward, finding what a future amount (or payment stream) is worth today.

• RATE / NPER

  Two of the pieces PMT normally takes as known inputs, solved for instead: the interest rate itself, or how many payments it takes to pay something off.

• NPV / IRR

  NPV discounts a series of future cash flows back to today's value at a chosen rate; IRR finds the rate that would make that same series' NPV exactly zero.

• XNPV / XIRR

  NPV and IRR's versions for cash flows that don't arrive on neat, evenly-spaced periods: each one paired with its own actual date.

• SLN / EFFECT / NOMINAL

  SLN spreads an asset's cost evenly over its useful life; EFFECT and NOMINAL convert between a stated annual rate and the real rate once compounding is taken into account.

Math and statistics

Totals, averages, rounding, ranking and spread.

• SUM

  Adds every number you give it: cells, ranges, or typed values, in any mix.

• AVERAGE

  Finds the arithmetic mean of a set of numbers, ignoring text and blanks.

• ROUNDUP / ROUNDDOWN / FLOOR / CEILING

  Four ways to round in a chosen direction, instead of to the nearest value the way plain ROUND does.

• MOD / EXACT

  MOD returns what's left over after division; EXACT checks whether two bits of text match exactly, case included.

• RANK / LARGE / SMALL / MEDIAN / MODE

  Five ways to ask "how does this compare to the rest of the set": a position, a specific-place value, or the group's middle or most common value.

• STDEV / PERCENTILE / CORREL

  Three more statistics functions: how spread out a dataset is, the value at a given percentile, and how strongly two sets of numbers move together.

Text

Clean, split, join and format text.

• Text functions

  LEFT, RIGHT, MID, LEN, TRIM, SEARCH, and SUBSTITUTE: the everyday toolkit for slicing, inspecting, and cleaning up text.

• UPPER / LOWER / PROPER / FIND / TEXTJOIN

  Five more everyday text functions: three for changing letter case, one for finding a case-sensitive position, and one for joining a list with a shared delimiter.

• TEXT

  Turns a number into formatted text using a format code: the way to bake $, %, or padded zeros right into a displayed value.

Dates

Build dates, take them apart, and count working days.

• Date Functions

  DATE, DATEDIF, DAYS, and TODAY: building, measuring, and comparing dates.

• YEAR / MONTH / DAY / WEEKDAY / NETWORKDAYS

  Pull a single piece out of a date, name which day of the week it falls on, or count the actual working days between two dates.

• EDATE / EOMONTH / WORKDAY

  Three ways to move a date forward or back by whole months, or by a number of working days.

Dynamic arrays

Modern formulas that return a whole list at once.

• Array Formulas

  UNIQUE, SORT, and FILTER: functions that return a whole list of values at once, not just one.

• SORTBY / XMATCH / SEQUENCE

  Three modern array functions: sort one range by the values in another, find a position with more control than MATCH, and generate a run of numbers without typing them in.

Data tools and workbook skills

Pivot tables, filters, formatting, dropdowns, names, charts and shortcuts.

• Pivot Tables

  Not a function, but a tool. Summarizes a raw table by grouping rows into categories and aggregating a value, without writing a single formula.

• Filters & Sorting

  Not a function, two tools. Filter hides rows that don't match a condition; Sort reorders every row by a column. Neither one calculates anything.

• Conditional Formatting

  Not a function, but a rule. Automatically highlights any cell in a range that meets a condition, and keeps highlighting correctly as the data changes.

• Data Validation

  Not a function, but a restriction. Limits what a cell will accept, most often shown as a dropdown list of the only values allowed.

• Name Manager (Defined Names)

  Not a function, but a piece of syntax. Gives a cell or range a plain name, so formulas can use that name instead of the raw reference.

• Referencing other sheets

  Not a function, but a piece of syntax. Any formula can reach a cell or range on another sheet in the same workbook.

• Cell Formatting

  Not a function, direct styling. Bold, fill color, and number format change how a value reads, never what it is or what it calculates to.

• Charts & Graphs

  Not a function, a visual. Turns a table into a picture: a bar chart, a line chart, or a pie chart, depending on what question the data is actually answering.

• Excel Keyboard Shortcuts

  Every keyboard shortcut drill in LearnExcel, on one page: Windows and Mac side by side, grouped the same way as the Shortcuts tab.