Excel functions

Every function briefly explained, with the syntax, the pitfalls and an example you can change straight away.

Basics

Creating a formula

A formula always starts with =. Then comes a calculation with cells, such as =B2*C2, or a function, such as =SUM(B2:B6). When a number changes, Excel recalculates straight away.

Full explanation

Filling and copying formulas

Drag the fill handle, the small square at the bottom right of the cell, downwards, and Excel adjusts the formula for each row: =B2*C2 becomes =B3*C3. Double-clicking it is even quicker.

Full explanation

Order of operations

Excel first works out what is in brackets, then powers, then multiplication and division, and only then addition and subtraction. With brackets you set the order yourself.

Full explanation

Absolute references ($)

A $ locks part of a reference, so it doesn't shift when you fill a formula. $A$1 always stays A1. F4 switches between the forms.

Full explanation

Calculating percentages

To Excel, 20% is simply 0.2. A percentage of an amount is =amount*20%, and the amount plus 20% is =amount*(1+20%).

Full explanation

Working with multiple sheets

To use a cell on another sheet, write the sheet name, an exclamation mark and the cell: =Prices!B2. With =SUM(Jan:Mar!B2) you add up the same cell across several sheets.

Full explanation

Formatting numbers

A number format changes how a number looks, for example as currency, a percentage or a date, without changing the number itself. You choose it on the Home tab or with Ctrl + 1.

Full explanation

Calculating

SUM

SUM adds up whatever you select, such as =SUM(C2:C6). Text and empty cells are simply skipped.

Full explanation

Subtract, multiply, divide

To subtract, multiply or divide in Excel, you don't use a function but an operator: =A2-B2, =A2*B2 and =A2/B2. Only adding has its own function, SUM.

Full explanation

AVERAGE

AVERAGE gives you the average of whatever you select. An empty cell isn't counted, but a 0 is.

Full explanation

MIN and MAX

MIN gives the lowest and MAX the highest value in a set of numbers.

Full explanation

ABS, POWER and SQRT

ABS gives a number without its minus sign, POWER raises a number to a power and SQRT gives the square root.

Full explanation

Counting

COUNT and COUNTA

COUNT counts cells that contain a number. COUNTA counts every cell that isn't empty, text included.

Full explanation

COUNTIF

COUNTIF tells you how many cells match what you ask for, such as =COUNTIF(A2:A6,"Leeds").

Full explanation

COUNTIFS

COUNTIFS counts the rows that meet several conditions at once. For each condition you give a range and a value.

Full explanation

SUMIF

SUMIF adds up only the rows you want: first where to look, then what to look for, then what to add up.

Full explanation

SUMIFS

SUMIFS adds up with several conditions. The sum range comes first, followed by the pairs of range and criteria.

Full explanation

AVERAGEIF

AVERAGEIF works out the average of only the rows that meet a condition, such as the average revenue for Leeds.

Full explanation

MAXIFS and MINIFS

MAXIFS gives the highest number from only the rows that meet your conditions, such as the highest revenue for Leeds. MINIFS gives the lowest.

Full explanation

Wildcards

With * you look for zero or more characters and with ? for exactly one. "UK*" finds everything that starts with UK.

Full explanation

Logic

TRUE and FALSE

A comparison such as =B2>100 gives TRUE or FALSE. You use that result in IF, in COUNTIF and when filtering.

Full explanation

IF

With IF you let Excel choose: if the test is true, you get one result, otherwise the other. For example =IF(C2>500,"High","Low").

Full explanation

IFS

IFS checks a series of conditions one after another and gives the result of the first one that's true. That way you avoid nested IF formulas.

Full explanation

AND and OR

AND gives TRUE if all conditions are met. OR gives TRUE if at least one is. You usually use them inside IF.

Full explanation

IFERROR

IFERROR shows a value of your own when a formula gives an error, and otherwise just the result.

Full explanation

ISNUMBER and ISBLANK

ISNUMBER gives TRUE if a cell contains a number, ISBLANK if it's empty. You usually use them inside IF, to check before you calculate.

Full explanation

Looking things up

VLOOKUP

With VLOOKUP, Excel looks something up in a table: it finds your code on the left and reads the price or name next to it. End with FALSE for an exact match.

Full explanation

HLOOKUP

HLOOKUP looks for a value in the top row of a table and gives what is in the same column below it. Handy when your data runs across, such as months.

Full explanation

XLOOKUP

With XLOOKUP you point to where Excel should look and where the answer is. It works in both directions and matches exactly by default. You do need Excel 2021 or 365.

Full explanation

INDEX and MATCH

MATCH tells you the position of something, and INDEX fetches what's at that position. Together they look up in any direction, in every version of Excel.

Full explanation

XMATCH

XMATCH gives the position of a value in a list, like MATCH, but finds an exact match by default and can start at the bottom. Works in Excel 2021 and 365.

Full explanation

Text

LEFT, RIGHT and MID

LEFT and RIGHT take characters from the start or the end of a text. MID takes a piece from the middle.

Full explanation

TRIM

TRIM removes spaces at the start and the end, and turns double spaces in the middle into single ones.

Full explanation

LEN

LEN counts how many characters a cell contains, spaces and punctuation included.

Full explanation

UPPER, LOWER and PROPER

UPPER puts text in capitals, LOWER in lower case, and PROPER gives every word a capital letter at the start.

Full explanation

SEARCH and FIND

Both functions give the position of a text inside another text. SEARCH ignores upper and lower case and knows wildcards, FIND doesn't.

Full explanation

Joining text

Join cells with the & sign, for example =A2&" "&B2. For long lists, use TEXTJOIN.

Full explanation

SUBSTITUTE

SUBSTITUTE replaces a particular piece of text with another, everywhere it appears.

Full explanation

TEXTBEFORE and TEXTAFTER

TEXTBEFORE gives everything before a delimiter and TEXTAFTER everything after it. Available in Excel 365.

Full explanation

TEXTSPLIT

TEXTSPLIT splits text across columns or rows wherever a delimiter appears. One formula, and the pieces spill over automatically. Available in Excel 365 and 2024.

Full explanation

TEXT

With TEXT you decide what a number or date looks like inside a text, such as =TEXT(A1,"dd/mm/yyyy").

Full explanation

VALUE

VALUE turns a number stored as text back into a real number, so you can calculate with it.

Full explanation

Numbers

ROUND

With ROUND you choose how many decimal places remain. Formatting only changes what you see, ROUND changes the value itself.

Full explanation

CEILING and FLOOR

CEILING rounds up to a multiple, such as a quarter of an hour or a box of 6. FLOOR rounds down.

Full explanation

MOD and INT

INT rounds a number down to a whole number, and MOD gives what is left after a division. Together they give a division with a remainder.

Full explanation

LARGE and SMALL

LARGE gives the second, third or nth highest value. SMALL does the same from the bottom. Ideal for a top 3.

Full explanation

MEDIAN and MODE

MEDIAN gives the middle value and MODE the value that appears most often. Both are less sensitive to outliers than the average.

Full explanation

SEQUENCE and RANDBETWEEN

SEQUENCE makes a list of consecutive numbers in one go, such as 1 to 10. RANDBETWEEN gives a random whole number between two limits.

Full explanation

PMT, PV and FV

PMT gives the payment per period on a loan, PV how much you can borrow with a fixed monthly payment, and FV what your savings are worth after a while.

Full explanation

Dates

DATE

DATE builds a real date from a year, a month and a day. The order is always year, month, day.

Full explanation

YEAR, MONTH and DAY

YEAR, MONTH and DAY take the year, the month or the day from a date. With WEEKDAY and ISOWEEKNUM you find the day of the week and the week number.

Full explanation

TODAY

TODAY gives today's date and updates automatically. The brackets stay empty.

Full explanation

DATEDIF

DATEDIF works out the difference between two dates in years, months or days, such as =DATEDIF(A2,B2,"Y") for an age.

Full explanation

NETWORKDAYS

NETWORKDAYS counts the working days between two dates. Weekends don't count and you add the public holidays yourself.

Full explanation

Calculating with time

To Excel, a time is part of a day: 12:00 is 0.5. That's how you work out how long someone worked, with =C2-B2, and with *24 you turn it into hours, for the pay for example.

Full explanation

TIME, HOUR and MINUTE

TIME builds a time from an hour, minutes and seconds. HOUR and MINUTE take those parts out again, such as the hour 17 from 17:45.

Full explanation

Analysis

Sorting and filtering

Sorting puts the whole table in a different order, filtering hides rows for a while. Click in your table and use the buttons on the Data tab.

Full explanation

SUBTOTAL

SUBTOTAL only calculates with the rows that are visible after a filter. Function number 9 adds up, 1 gives the average and 3 counts filled cells.

Full explanation

Making a chart

Select your labels and numbers with their headings, and choose Insert > Charts. A line shows a trend, a column compares, a pie shows parts of a whole.

Full explanation

Making a PivotTable

A PivotTable turns a long list into a summary per group. Click in your data, choose Insert > PivotTable and drag the fields to Rows and Values.

Full explanation

Working with tables

Turn your list into an Excel table with Ctrl + T. The table grows with new rows, gets filter buttons and a total row, and your formulas use column names: =SUM(Sales[Total]).

Full explanation

Conditional formatting

With conditional formatting, Excel colours a cell by itself when it meets a rule, such as red below 0. When the value changes, the colour changes with it. You'll find it on the Home tab.

Full explanation

Worksheet

Named ranges

Give a cell or range a name, such as Rate or Scores, and use that name in your formulas: =SUM(Scores) instead of =SUM(C2:C6). It reads more easily, and a name never shifts.

Full explanation

Creating a drop-down list

With a drop-down list you pick a cell's value from a list instead of typing it. That prevents typos. You create it under Data > Data Validation > List.

Full explanation

Freezing rows and columns

With Freeze Panes, your heading row or first column stays put while you scroll. You'll find it on the View tab. That way you always know what each column holds in a long list.

Full explanation

Paste Special

With Paste Special you paste only what you need: the values without formulas, just the formatting, or rows and columns swapped. Press Ctrl + Alt + V after copying.

Full explanation

Printing a sheet

Check the print preview first under File > Print, or with Ctrl + P. On the Page Layout tab you choose landscape, a print area and a heading row that repeats on every page.

Full explanation

Text to Columns

With Data > Text to Columns you split one column into several, at every space, comma or semicolon. Handy for names or a pasted export.

Full explanation

Flash Fill

Flash Fill completes a column following an example you type yourself, such as the first name from a full name. You'll find it on the Data tab, or press Ctrl + E.

Full explanation

Remove Duplicates

With Data > Remove Duplicates you delete rows that appear more than once in your list. You choose which columns Excel compares.

Full explanation

Find and Replace

With Home > Find & Select > Replace, or Ctrl + H, you change a word or character in every cell at once. To find without replacing, use Ctrl + F.

Full explanation

Modern functions

UNIQUE

UNIQUE gives a list without duplicates. You type the formula in one cell and the result spills over automatically. Available in Excel 2021 and 365.

Full explanation

SORT

SORT gives a sorted copy of your data that updates itself when the source changes. Available in Excel 2021 and 365.

Full explanation

FILTER

FILTER fetches every row you want to see, whereas VLOOKUP stops at the first one. You do need Excel 2021 or 365.

Full explanation

SUMPRODUCT

SUMPRODUCT multiplies columns row by row and adds up the results. Handy for totals without a helper column and for weighted averages.

Full explanation

CHOOSECOLS

CHOOSECOLS shows only the columns of a table that you choose, in the order you want. The result spills over and updates itself. Available in Excel 365 and 2024.

Full explanation

LET

With LET you give an intermediate result a name inside your formula. Excel works it out once, and a long formula becomes easier to read. Works in Excel 2021 and 365.

Full explanation