Excel functions
Each function page shows the syntax in plain words, worked examples in a real grid and the lessons where you can practice it. Start with SUM and IF, then move on to SUMIF, XLOOKUP and the rest.
Math and sums
17- ABSReturns the absolute value of a number, the number without its sign. -25 becomes 25 and 25 stays 25.
- CEILING.MATHRounds a number up to the nearest multiple you choose, such as the next 5, 10 or 0.25. Without a multiple it rounds up to the next whole number.
- INTRounds a number down to the nearest whole number. Negative numbers go further from zero: INT(-7.9) is -8.
- MODReturns the remainder after division. The result has the same sign as the divisor.
- POWERRaises a number to a power. POWER(2,3) is 2×2×2, which is 8, the same as =2^3.
- PRODUCTMultiplies all the numbers you give it, ranges included. Empty cells and text in a range are skipped.
- RANDReturns a random decimal number from 0 up to, but not including, 1. It changes every time the sheet recalculates.
- RANDBETWEENReturns a random whole number between a bottom and a top number, both included. It changes every time the sheet recalculates.
- ROUNDRounds a number to a given number of decimals. Halves round away from zero; negative decimals round to tens, hundreds and so on.
- ROUNDDOWNRounds a number down, toward zero, to a given number of decimals.
- ROUNDUPRounds a number up, away from zero, to a given number of decimals.
- SQRTReturns the square root of a number. A negative number gives
- SUMAdds numbers, cell references and ranges. Text and blank cells in a range are ignored.
- SUMIFAdds the cells that meet one condition. The condition can be checked in the same range or in another column.
- SUMIFSAdds the cells that meet every one of several conditions. The sum range comes first.
- SUMPRODUCTMultiplies ranges row by row and adds the results. With a condition in parentheses it works as a flexible SUMIFS.
- TRUNCCuts off the decimals of a number without rounding. TRUNC(8.9) is 8, and TRUNC(-8.9) is -8.
Logic
8- ANDReturns TRUE only when every condition is TRUE. Usually used as the test inside IF.
- IFReturns one value when a test is TRUE and another when it is FALSE. Put text results in double quotes.
- IFERRORReturns a value you choose when a formula gives any error, and the formula result otherwise.
- IFNAReturns a value you choose when a formula gives #N/A, and the formula result otherwise. Other errors still show.
- IFSChecks several conditions in order and returns the value for the first one that is TRUE. Use TRUE as the last test for "everything else".
- NOTReverses a logical value: TRUE becomes FALSE and FALSE becomes TRUE.
- ORReturns TRUE when at least one condition is TRUE.
- SWITCHCompares one value against a list and returns the result for the first match, with an optional default at the end.
Lookup
12- CHOOSEReturns the value at a numbered position in a list of arguments.
- COLUMNReturns the column number of a cell, 1 for A, 2 for B and 27 for AA. Left empty, it returns the column of the cell the formula is in.
- HLOOKUPLooks for a value in the first row of a table and returns the value from another row in the same column.
- INDEXReturns the value at a given row and column position in a range. Paired with MATCH it becomes a lookup in any direction.
- INDIRECTTurns text into a cell reference, such as "B3" or "Sheet2!A1", and returns what is in that cell. Use it to build a reference from other cells.
- LOOKUPLooks up a value in one row or column and returns the value in the same position from another. It always does an approximate match, so the lookup range must be sorted from smallest to largest.
- MATCHReturns the position of a value in a row or column. Use 0 as match_type for an exact match.
- OFFSETReturns a reference a number of rows and columns away from a starting cell, optionally a whole range of a given height and width. Wrap it in SUM or AVERAGE to work with that range.
- ROWReturns the row number of a cell. Left empty, it returns the row of the cell the formula is in.
- VLOOKUPLooks for a value in the first column of a table and returns the value from another column in the same row.
- XLOOKUPFinds a value in one range and returns the matching item from another. Exact match by default, with an optional value for not found.
- XMATCHReturns the position of a value in a range, like MATCH but with exact match as the default and search from the end.
Text
21- CHARReturns the character for a code number. CHAR(65) is A, and CHAR(10) is a line break, the usual way to put line breaks into a formula.
- CONCATJoins texts and ranges into one text. The & operator does the same for single cells.
- CONCATENATEJoins up to 255 texts into one. In Excel it takes single cells, not ranges; CONCAT and the & operator do the same job in new sheets.
- FINDReturns the position of one text inside another. FIND is case-sensitive and does not allow wildcards; it returns #VALUE! when nothing is found.
- LEFTReturns the first characters of a text. Without num_chars it returns one character.
- LENReturns the number of characters in a text, spaces included.
- LOWERConverts text to small letters.
- MIDReturns a number of characters from the middle of a text, starting at a given position.
- PROPERCapitalizes the first letter of each word and makes the rest small.
- REPLACEReplaces part of a text by position. From a start character, a number of characters is swapped for new text. To replace by content instead, use SUBSTITUTE.
- RIGHTReturns the last characters of a text. The result is text, even when it looks like a number.
- SEARCHReturns the position of one text inside another, ignoring upper and lower case. Wildcards * and ? are allowed.
- SUBSTITUTEReplaces existing text with new text, everywhere or only the n-th time it appears.
- TEXTFormats a number as text with a number format code, for example to put it inside a sentence.
- TEXTAFTERReturns the text after a delimiter, such as the domain after the @ in an email address. You can choose which occurrence of the delimiter to use.
- TEXTBEFOREReturns the text before a delimiter, such as everything before the @ in an email address. You can choose which occurrence of the delimiter to use.
- TEXTJOINJoins texts with a delimiter between them, and can skip empty cells.
- TEXTSPLITSplits text into pieces at a delimiter and spills them across columns, or down rows when you also give a row delimiter.
- TRIMRemoves spaces from the start and end of a text and turns runs of spaces inside it into one.
- UPPERConverts text to capital letters.
- VALUEConverts text that looks like a number, such as "1,250" or "15%", into a real number you can calculate with.
Dates and times
17- DATEBuilds a date from a year, month and day. Months and days outside the normal range roll over: month 14 is February next year.
- DATEDIFCounts the whole years, months or days between two dates, which is how you work out an age or a length of service. Excel leaves it out of the function list, but it works.
- DATEVALUEConverts a date written as text, such as "2024-03-15", into a date serial number Excel can calculate with and format.
- DAYReturns the day of the month of a date, a number from 1 to 31.
- DAYSReturns the number of days between two dates. The end date comes first, and the result is the same as subtracting the start date from the end date.
- EDATEReturns the same day a number of months before or after a date. In a shorter month it stops at the last day, so one month after Jan 31, 2024 is Feb 29.
- EOMONTHReturns the last day of the month a number of months before or after a date. 0 gives the end of the same month.
- HOURReturns the hour of a time as a number from 0 to 23. Times are fractions of a day, so 0.75 is 6:00 PM and HOUR(0.75) is 18.
- MONTHReturns the month of a date as a number from 1 (January) to 12 (December).
- NETWORKDAYSCounts the working days (Monday to Friday) between two dates, both included, minus any holidays you list.
- NOWReturns the current date and time. It updates every time the sheet recalculates, so it is a live clock, not a timestamp.
- TIMEBuilds a time from an hour, a minute and a second. The result is a fraction of a day, so TIME(12,0,0) is 0.5.
- TODAYReturns today's date. It updates every time the sheet recalculates.
- WEEKDAYReturns the day of the week of a date as a number. By default 1 is Sunday and 7 is Saturday; with 2 as the second argument, Monday is 1.
- WEEKNUMReturns the week number of a date. By default weeks start on Sunday and week 1 is the week with Jan 1 in it; return type 21 gives ISO week numbers, as used in Europe.
- WORKDAYReturns the date a number of working days before or after a start date, skipping weekends and any holidays you list.
- YEARReturns the year of a date as a number.
Statistics
19- AVERAGEReturns the mean: the sum of the numbers divided by how many there are. Blank cells and text are skipped.
- AVERAGEIFReturns the average of the cells that meet one condition.
- AVERAGEIFSReturns the average of the cells that meet every one of several conditions. The average range comes first.
- CORRELReturns the correlation coefficient of two ranges, from -1 to 1. Close to 1 means the two rise together, close to -1 that one falls as the other rises, and close to 0 no straight-line link.
- COUNTCounts the cells that hold numbers (dates count too). Text, blanks and errors are not counted.
- COUNTACounts the cells that are not empty: numbers, text, logical values and errors.
- COUNTBLANKCounts the empty cells in a range. A formula that returns "" also counts as empty.
- COUNTIFCounts the cells in a range that meet one condition. Text conditions ignore upper and lower case.
- COUNTIFSCounts the rows that meet every one of several conditions. Use the same range twice to count between two limits.
- LARGEReturns the k-th largest value in a range: k = 1 is the maximum, k = 2 the runner-up.
- MAXReturns the largest number. Text and blank cells in a range are ignored.
- MEDIANReturns the middle value of a set of numbers. With an even count it averages the two in the middle.
- MINReturns the smallest number. Text and blank cells in a range are ignored.
- MODEReturns the number that appears most often in a range. If no number repeats, it returns
- PERCENTILE.INCReturns the value below which a given share of the data falls, such as the 90th percentile. The share k is a fraction from 0 to 1.
- RANKReturns the rank of a number in a list, 1 for the largest by default. Equal numbers share a rank.
- SMALLReturns the k-th smallest value in a range: k = 1 is the minimum.
- STDEV.PReturns the standard deviation of a whole population, for when your data is every value there is and not a sample.
- STDEV.SEstimates the standard deviation of a sample, which is how far the values typically sit from their average. Text and empty cells in a range are ignored.
Dynamic arrays
6- FILTERReturns the rows of a range that meet a condition, as a list that spills into the cells below.
- SEQUENCEReturns a list of numbers in sequence, such as 1, 2, 3, that spills into rows and columns. You choose the size, the start and the step.
- SORTSorts the rows of a range by one of its columns and spills the sorted copy into the cells below. The original data stays as it is.
- SORTBYSorts a range by the values in another range, which need not be part of the result. Add more by-range and order pairs to break ties.
- TRANSPOSETurns rows into columns and columns into rows. The result spills and stays linked to the original cells.
- UNIQUEReturns the distinct values of a range as a list that spills into the cells below.
Information
5- ISBLANKReturns TRUE when a cell is empty. A cell with a formula that returns "" is not empty.
- ISERRORReturns TRUE if a value is any error, such as
- ISNAReturns TRUE if a value is the
- ISNUMBERReturns TRUE if a value is a number and FALSE for anything else, including text that only looks like a number. Dates and times are numbers too.
- ISTEXTReturns TRUE if a value is text and FALSE otherwise. Numbers stored as text count as text; empty cells do not.
Financial
7- FVReturns the future value of an investment with equal regular payments and a fixed interest rate. Money you pay in is negative, so the result is positive.
- IRRReturns the internal rate of return of a series of cash flows, the discount rate at which their net present value is 0.
- NPERReturns how many payment periods it takes to pay off a loan, or to reach a savings goal, with fixed payments and a fixed interest rate.
- NPVReturns the net present value of future cash flows at a discount rate. The first value is discounted one period, so a payment made today is added outside NPV.
- PMTReturns the payment per period on a loan with a fixed interest rate, paid off in equal payments. The result is negative because it is money going out.
- PVReturns the present value, what a series of equal future payments, or one future amount, is worth today at a given interest rate.
- RATEReturns the interest rate per period of a loan or investment, from the number of payments, the payment and the amount. Multiply by 12 to get the annual rate on monthly payments.
Good to know
How each function page works
Every page starts with what the function does in a sentence or two, then its syntax with each argument explained in plain English. Optional arguments are marked, so you can see at a glance what you must type and what Excel fills in for you. Below that are worked examples on a small sheet of realistic data: the formula, the cells it reads and the result it returns.
The examples aren't screenshots. Each one is calculated by the same formula engine that grades the exercises, and every result is checked against a real spreadsheet program before it is published, so what you see is what Excel gives you. Where a function has a well-known trap, such as the last argument of VLOOKUP or the order of the arguments in SUMIFS, the page points it out.
How the list is organized
The functions are grouped by what you use them for: math for adding and rounding, logic for decisions, lookup for finding values in a table, text for cleaning up and splitting text, dates and times for days, months and hours, statistics for averages and spread, dynamic arrays for formulas that return several values at once, information for checking what a cell holds, and financial for loans and savings. Within each group they are listed alphabetically.
Which functions to learn first
If you are new to formulas, start with SUM, AVERAGE, COUNT, MIN and MAX. They take a range and return one number, and almost every other function builds on that pattern. Then learn IF, because it lets a formula make a decision, and ROUND, because amounts and percentages rarely come out even.
Next come the functions employers test most: SUMIF and COUNTIF, their multi-condition versions SUMIFS and COUNTIFS, and a lookup. Learn VLOOKUP because you will meet it in other people's workbooks, then XLOOKUP or INDEX and MATCH for your own. With those, plus IFERROR to catch the errors, you can handle most of what an office job or an Excel test asks of you.
Function names in other languages
Excel translates function names into the language it is installed in. In Danish Excel, SUMIF is SUM.HVIS and VLOOKUP is LOPSLAG, and the arguments are separated by semicolons instead of commas. You don't have to do anything about it: when you open a workbook made in another language, Excel shows the formulas in your own. If a formula copied from a forum gives #NAME?, check whether it was written for another language version.
All function names in Danish and English →Excel and Google Sheets
Most functions work the same way in both programs. Every function that also works in Google Sheets has a Google Sheets version of its page, with a note on anything that works differently there, and the functions that exist only in Sheets, such as QUERY and ARRAYFORMULA, are listed on the Google Sheets page. Newer functions such as XLOOKUP, FILTER and SORT need Excel 2021 or Microsoft 365; older versions show #NAME? instead.
When a formula returns an error
An error tells you what went wrong once you know how to read it. #N/A means a lookup didn't find what it was looking for. #VALUE! means an argument has the wrong type, often text where a number should be. #DIV/0! is a division by zero or by an empty cell. #NAME? means Excel doesn't recognize a name, usually a misspelled function or text without quotes. #REF! means the formula points at cells that have been deleted. The pages for IFERROR and IFNA show how to catch an error you expect.
Reading isn't the same as writing
You learn a function by typing it, not by reading about it. Where a lesson covers the function, the page links to it, and there you write the formula yourself in a live grid, checked the moment you press Enter, with a hint when something is off. If you are preparing for a job, the timed practice tests mix these functions the way employer tests do.