Excel Formulas: Every Function with a Live Sheet
Excel formulas and functions explained on sheets you can edit: VLOOKUP, XLOOKUP, IF, SUMIF, COUNTIF, dates, text and dynamic arrays. Change a number or a formula and the sheet recalculates in your browser.
Start a guided Excel journeyFormula Basics
- SUMType =SUM(B2:B6) under a column of numbers to add them up, or press Alt+= to let AutoSum write the formula. Sum rows, separate cells and other sheets on live sheets you can edit.
- SubtractExcel has no SUBTRACT function: type =B2-C2 to subtract one cell from another. Subtract a whole column, several cells at once, a percentage or a date, on live sheets you can edit.
- Multiply and DivideMultiply in Excel with an asterisk, =B2*C2, and divide with a slash, =B2/C2. Multiply a column by one number, use PRODUCT, and stop #DIV/0! errors, on live sheets you can edit.
- AVERAGE=AVERAGE(B2:B7) adds the numbers in B2:B7 and divides by how many there are. Learn how blanks and zeros change the result, how to ignore zeros, and how to average the top 3.
- COUNT and COUNTA=COUNT(B2:B8) counts the cells that hold numbers, =COUNTA(B2:B8) counts every cell that is not empty, and =COUNTBLANK(B2:B8) counts the empty ones. See all three on a sheet you can edit.
- Absolute ReferenceAn absolute reference like $E$1 stays the same when you copy a formula, while a relative reference like E1 moves with it. Press F4 to add the dollar signs. See the difference on sheets you can edit.
- PercentageThe Excel percentage formula is =part/total, for example =B2/C2, with the cell formatted as a percentage. Percentage of a total, percentage of a number, adding or taking off a percent, on live sheets.
- Percent ChangeThe percent change formula in Excel is =(new-old)/old, for example =(C2-B2)/B2, formatted as a percentage. A negative result is a decrease. Live sheets cover month over month change, a zero start and percentage points.
Logic
- IF=IF(B2>=50,"Pass","Fail") checks whether B2 is 50 or more and returns Pass if it is and Fail if it is not. Learn the IF syntax, IF with text, IF with a calculation, IF a cell is blank, and the mistakes that make IF return the wrong result.
- Nested IF=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) puts one IF inside another to choose between more than two results. Learn how nested IF is read, why the order of the conditions matters, and when IFS or a lookup table is the better choice.
- IFS=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") tests each condition in order and returns the value paired with the first one that is TRUE. Learn the IFS syntax, the TRUE default, why IFS returns #N/A, and how it compares with nested IF.
- AND, OR, NOT=AND(B2>=10,B2<=20) returns TRUE only when every condition is true, and =OR(B2="North",B2="South") returns TRUE when at least one is. Learn AND, OR, NOT and XOR on their own and inside IF, how to test whether a number is between two values, and how to write AND and OR in array formulas.
- IFERROR=IFERROR(B2/C2,0) returns B2/C2, or 0 when the division gives an error. Learn IFERROR with VLOOKUP, returning a blank instead of an error, why IFNA is the better choice for lookups, and why hiding every error can hide real mistakes.
- SWITCH=SWITCH(B2,"N","North","S","South","Unknown") compares B2 with each value in turn and returns the result paired with the first exact match, or Unknown when nothing matches. Learn the SWITCH syntax, the default value, the SWITCH(TRUE,...) pattern and when to use IFS or nested IF instead.
- ISBLANK, ISNUMBER=ISBLANK(B2) returns TRUE when B2 is empty, and =ISNUMBER(B2) returns TRUE when B2 holds a number. Learn ISBLANK, ISNUMBER, ISTEXT, ISERROR, ISNA, ISEVEN and ISODD, why a formula returning "" is not blank, and how ISNUMBER(SEARCH()) checks whether a cell contains text.
Lookup
- VLOOKUP=VLOOKUP(F2,A2:D6,3,FALSE) looks for F2 in the first column of A2:D6 and returns the value from the third column of the same row. Exact and approximate match, #N/A fixes, another sheet, two criteria.
- XLOOKUP=XLOOKUP(F2,A2:A6,C2:C6) looks for F2 in A2:A6 and returns the value in the same row of C2:C6. Not found text, several columns at once, lookups to the left, last match, approximate and wildcard match.
- INDEX MATCH=INDEX(C2:C6,MATCH(F2,A2:A6,0)) finds the row of F2 in column A and returns the value from that row of column C. It looks left, does two-way lookups and works in every Excel version.
- INDEX=INDEX(A2:C6,3,2) returns the value in the third row and second column of A2:C6. Use it for the nth item of a list, a whole row or column, and the value at a position MATCH found.
- MATCH=MATCH(E2,A2:A6,0) returns the position of E2 in A2:A6: 4 if it is the fourth item. Match types 0, 1 and -1, wildcards, case-sensitive matching and checking whether a value is in a list.
- HLOOKUP=HLOOKUP("Mar",A1:E3,2,FALSE) looks for Mar in the first row of A1:E3 and returns the value from the second row of the same column. Exact and approximate match, and when XLOOKUP is the better choice.
- XMATCH=XMATCH(E2,A2:A6) returns the position of E2 in A2:A6, with an exact match by default. It can also find the next smaller or larger value without sorting, search from the bottom, and use wildcards.
- Multiple Criteria Lookup=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) returns the value from the row where column A matches E2 and column B matches F2. The INDEX MATCH version, a helper column for VLOOKUP, and FILTER for every match.
- VLOOKUP vs XLOOKUPXLOOKUP does everything VLOOKUP does with an exact match by default, no column number, lookups to the left and a not-found argument. VLOOKUP is still the one to use when a file must open in Excel 2019 or older.
- INDIRECT=INDIRECT("C"&E2) reads the cell whose address is built as text: column C, row E2. Use it to pick a sheet by name from a cell, build ranges from numbers and make dependent drop-down lists.
- OFFSET=OFFSET(A1,3,2) returns the cell 3 rows down and 2 columns across from A1. With a height it returns a whole range, which is how you total the last N rows or build a rolling average.
- CHOOSE=CHOOSE(B2,"Low","Medium","High") returns Low when B2 is 1, Medium when it is 2 and High when it is 3. Map numbers to names, pick a range to total, replace a nested IF, and pick columns with CHOOSECOLS.
Count and Sum by Condition
- COUNTIF=COUNTIF(B2:B7,"North") counts the cells in B2:B7 that hold North. Count by text, numbers, wildcards, blanks and dates, and find duplicates, on live sheets you can edit.
- COUNTIFS=COUNTIFS(A2:A7,"North",C2:C7,">50") counts the rows where the region is North and the sales are over 50. Count between two numbers or dates, with OR logic and with blanks, on live sheets.
- SUMIF=SUMIF(A2:A7,"North",C2:C7) adds the values in C2:C7 on the rows where column A is North. Sum if greater than, if text contains, by date and from another sheet, on live sheets you can edit.
- SUMIFS=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") adds the sales in C2:C7 where the region is North and the product is Apple. Date ranges, OR logic and optional filters, on live sheets.
- AVERAGEIF=AVERAGEIF(A2:A7,"North",C2:C7) averages the values in C2:C7 on the rows where column A is North. AVERAGEIFS for several conditions, averages that ignore zeros, the #DIV/0! fix, and MAXIFS and MINIFS.
- Count Cells with Text=COUNTIF(A2:A8,"*") counts the cells in A2:A8 that hold text, skipping numbers, dates and empty cells. Count cells that contain a specific word, and return a value if a cell contains text.
- COUNTIF Not Blank=COUNTIF(B2:B8,"<>") counts the cells in B2:B8 that are not empty, the same as COUNTA. Add other conditions with COUNTIFS, and handle cells that only look empty.
- Count Unique Values=COUNTA(UNIQUE(A2:A9)) counts how many different values are in A2:A9. For older Excel use =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)). Count values that appear once, count with a condition, and skip blanks.
- SUMPRODUCT=SUMPRODUCT(B2:B6,C2:C6) multiplies each quantity by its price and adds the results. With conditions like (A2:A7="North")*C2:C7 it sums and counts where SUMIFS cannot: by month, column against column, with OR.
- SUBTOTAL=SUBTOTAL(9,C2:C8) adds C2:C8 like SUM but ignores other SUBTOTAL rows inside the range and rows hidden by a filter. Function numbers 9 and 109, counting visible rows, and AGGREGATE for errors.
- Weighted Average=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) is a weighted average: each value is multiplied by its weight, the products are added, and the total is divided by the sum of the weights. Grades, GPA by credits and prices by quantity.
Text
- Concatenate`=A2&" "&B2` joins the text in A2 and B2 with a space between them. CONCATENATE and CONCAT do the same job; TEXT keeps numbers and dates readable when you join them.
- TEXTJOIN`=TEXTJOIN(", ",TRUE,A2:A6)` joins every cell of A2:A6 into one text, with a comma and a space between items and empty cells skipped. Add FILTER to join only the rows that match a condition.
- Split Text`=TEXTBEFORE(A2," ")` returns the first name from `Ana Silva` and `=TEXTAFTER(A2," ")` the last name. TEXTSPLIT splits a cell into several columns at once; LEFT, MID and FIND do the same in older Excel.
- LEFT, RIGHT, MID`=LEFT(A2,3)` returns the first 3 characters of A2, `=RIGHT(A2,2)` the last 2, and `=MID(A2,5,4)` 4 characters starting at the 5th. Combine them with FIND and LEN when the length varies.
- FIND and SEARCH`=SEARCH("apple",A2)` returns the position where `apple` starts in A2, ignoring case. FIND does the same but is case-sensitive. Both return #VALUE! when the text is missing, which ISNUMBER turns into an "if cell contains" test.
- SUBSTITUTE, REPLACE`=SUBSTITUTE(A2,"-","")` removes every dash from A2: SUBSTITUTE swaps text by matching it. REPLACE swaps by position: `=REPLACE(A2,1,3,"XYZ")` overwrites the first 3 characters.
- TRIM`=TRIM(A2)` removes the spaces before and after the text in A2 and turns runs of spaces between words into one. SUBSTITUTE removes every space or the non-breaking spaces TRIM misses.
- UPPER, LOWER, PROPER`=UPPER(A2)` capitalizes all letters in A2, `=LOWER(A2)` makes them all lowercase, and `=PROPER(A2)` capitalizes the first letter of each word. For only the first letter of the text, combine UPPER, LEFT and MID.
- LEN`=LEN(A2)` returns the number of characters in A2, spaces and punctuation included. With TRIM and SUBSTITUTE it also counts words, and with SUM it counts the characters in a whole range.
- TEXT`=TEXT(A2,"mmm d, yyyy")` turns the date in A2 into text such as `Mar 15, 2026`, and `=TEXT(B2,"$#,##0.00")` turns 1250.5 into `$1,250.50`. The result is text, so use it for labels, not for further math.
- Text to Number`=VALUE(A2)` turns a number stored as text, such as `'120`, into the number 120. Two minus signs, `=--A2`, do the same, NUMBERVALUE handles commas as decimal separators, and Convert to Number fixes cells in place.
- Line Break in a CellPress Alt+Enter while typing in a cell to start a new line in it (Control+Option+Return on a Mac). In a formula, `CHAR(10)` is the line break: `=A2&CHAR(10)&B2` puts B2 on a second line, shown once Wrap Text is on.
- Leading ZerosExcel drops leading zeros because `00742` is the number 742. Keep them with a custom number format such as `00000`, an apostrophe (`'00742`) or the Text format, or add them with `=TEXT(A2,"00000")`.
- WildcardsIn Excel criteria, `*` stands for any number of characters and `?` for exactly one: `=COUNTIF(A2:A7,"*apple*")` counts the cells that contain `apple`. `~` turns a wildcard back into a plain character.
Dates and Times
- Calculate Age=DATEDIF(B2,TODAY(),"Y") returns the age in whole years of someone born on the date in B2. Calculate age at a specific date, in years, months and days, and without DATEDIF.
- DATEDIF=DATEDIF(A2,B2,"M") counts the complete months between the start date in A2 and the end date in B2. The units Y, M, D, YM, MD and YD, why DATEDIF is missing from the function list, and the #NUM! error.
- Days Between Dates=B2-A2 returns the number of days between the date in A2 and the later date in B2. Count days with DAYS, include both dates, and get weeks, months, years or working days instead.
- Day of the Week=TEXT(A2,"dddd") returns the day name of the date in A2, such as Monday, and =WEEKDAY(A2) returns it as a number. Short names, WEEKDAY return types and weekend checks.
- TODAY and NOW=TODAY() returns today's date and =NOW() the current date and time, and both update every time the sheet recalculates. Count days until a date, and insert a date that never changes with Ctrl+;.
- Add Days and Months=A2+30 returns the date 30 days after A2. To add months use =EDATE(A2,3), for the end of a month =EOMONTH(A2,0), and for years EDATE with 12 months per year.
- NETWORKDAYS and WORKDAY=NETWORKDAYS(A2,B2) counts the working days (Monday to Friday) from A2 to B2, both dates included. =WORKDAY(A2,10) returns the date 10 working days after A2. Both can skip a list of holidays.
- DATE, YEAR, MONTH, DAY=DATE(2026,3,15) returns the date March 15, 2026, from a year, a month and a day. YEAR, MONTH and DAY take a date apart, and DATE rolls month 13 into the next year.
- Time Calculations=B2-A2 returns the time between a start time in A2 and an end time in B2: format it as h:mm to see 8:30, or multiply by 24 for 8.5 hours. Shifts over midnight, totals over 24 hours and pay from hours worked.
- Week Number=WEEKNUM(A2) returns the week number of the date in A2, with weeks starting on Sunday. =ISOWEEKNUM(A2) returns the ISO week used in Europe, where weeks start on Monday. The start date of a week and a date from a week number.
Math and Statistics
- ROUND=ROUND(A2,2) rounds the number in A2 to two decimal places, and =ROUND(A2,0) to the nearest whole number. Negative digits round to tens, hundreds and thousands; MROUND rounds to any multiple.
- ROUNDUP / ROUNDDOWN=ROUNDUP(A2,0) always rounds away from zero, so 2.1 becomes 3, and =ROUNDDOWN(A2,0) always rounds toward zero, so 2.9 becomes 2. CEILING and FLOOR round up or down to a multiple, and INT and TRUNC drop decimals.
- Standard Deviation=STDEV.S(B2:B9) gives the standard deviation of a sample and =STDEV.P(B2:B9) of a whole population. Use STDEV.S unless your data is every value there is. VAR.S and VAR.P give the variance.
- RANK=RANK.EQ(B2,$B$2:$B$7) gives the position of B2 among the values in B2:B7, with the largest ranked 1. Add 1 as a third argument to rank the smallest first. Ties share a rank; COUNTIFS ranks within a group.
- Random Numbers=RANDBETWEEN(1,100) returns a random whole number from 1 to 100, and =RAND() a random decimal from 0 up to 1. RANDARRAY fills a whole range, INDEX with RANDBETWEEN picks a random item, and Paste Special > Values freezes the results.
- MOD and ABS=MOD(A2,B2) returns the remainder after dividing A2 by B2, so =MOD(17,5) is 2. =ABS(A2) returns a number without its sign, so =ABS(B2-C2) is the difference between two values whichever is larger.
- PMT=PMT(B2/12,B3*12,-B1) returns the monthly payment on a loan of B1 at the annual rate in B2 over B3 years. Divide the rate by 12, multiply the years by 12, and put a minus before the loan amount to get a positive payment.
- NPV and IRR=NPV(E2,B3:B5)+B2 discounts the future cash flows at the rate in E2 and adds the initial investment in B2, which NPV must not discount. =IRR(B2:B5) returns the rate at which that NPV is zero. XNPV and XIRR take real dates.
- CAGR=(B2/A2)^(1/C2)-1 gives the compound annual growth rate from a start value in A2 to an end value in B2 over C2 years. =RRI(C2,A2,B2) returns the same rate. Format the cell as a percentage.
Dynamic Arrays
- FILTER=FILTER(A2:C7,B2:B7="North") returns every row of A2:C7 whose region is North, and the result updates when the data changes. Learn multiple criteria with * and +, if_empty, #CALC! and sorting the result.
- UNIQUE=UNIQUE(B2:B8) returns each value of B2:B8 once, in the order it first appears, and updates when the list changes. Learn unique rows, exactly_once, a sorted unique list, counting unique values and using the result as a drop-down source.
- SORT and SORTBY=SORT(A2:C7,3,-1) returns the table A2:C7 sorted by its third column, largest first, and keeps re-sorting as the data changes. SORTBY sorts by any range, including several columns and a custom order.
- SEQUENCE=SEQUENCE(5) returns the numbers 1 to 5 down a column, and =SEQUENCE(3,4) fills 3 rows by 4 columns. Add a start and a step for any series, including dates, row numbers that grow with a list, and a monthly calendar.
- TRANSPOSE=TRANSPOSE(A1:D3) turns the rows of A1:D3 into columns and stays linked to the source. For a one-off copy, use Paste Special > Transpose. TOCOL stacks a whole grid into one column.
- LET=LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) calculates the sum once, names it total and uses the name twice. LET makes long formulas shorter, easier to read and faster, because each named part is calculated only once.
- LAMBDA=LAMBDA(price,price*1.2)(B2) defines a small function with one input, price, and calls it on B2. Save a LAMBDA in Name Manager to use it like a built-in function, or pass it to MAP, BYROW, SCAN and REDUCE.
Errors and Troubleshooting
- #SPILL! Error#SPILL! means a formula that returns several values has no room to put them: a cell in its spill range is not empty. Clear the cells in the way and the result appears.
- #VALUE! Error#VALUE! means a formula got the wrong kind of value, most often text where it needs a number: =B2+C2 fails when C2 holds "n/a" or a space. SUM ignores text, so =SUM(B2:C2) works.
- #NAME? Error#NAME? means Excel does not recognise a word in the formula: a misspelled function such as =SUMM(B2:B6), text without quotes, a missing colon in a range, an undefined name, or a function your Excel version does not have.
- #REF! Error#REF! means a formula refers to a cell that no longer exists, usually because a row, column or sheet it used was deleted: =B2*C2 becomes =B2*#REF!. It also appears when VLOOKUP or INDEX asks for a column or row outside its range.
- #N/A Error#N/A means a lookup did not find the value it was looking for. Check for typos, extra spaces and a table range that moved when the formula was filled down, then use IFNA to show a message for values that are really missing.
- #DIV/0! Error#DIV/0! appears when a formula divides by zero or by an empty cell, as in =B2/C2 with C2 empty. =IF(C2=0,"",B2/C2) shows a blank cell instead, and AVERAGE of a range with no numbers returns it too.
- Circular ReferenceA circular reference is a formula that refers to its own cell, directly or through other formulas, such as =SUM(B2:B7) typed in B7. Excel warns, shows 0, and lists the cell under Formulas > Error Checking > Circular References.
- Formula Not CalculatingIf Excel shows the formula instead of the result, the cell is formatted as Text, the formula starts with an apostrophe or a space, or Show Formulas is on. If results do not update, calculation is set to Manual: Formulas > Calculation Options > Automatic.
Data Tools
- Remove DuplicatesSelect the data and click Data > Remove Duplicates to delete repeated rows in place, or use =UNIQUE(A2:A9) to get a clean copy and keep the original. Find, flag and count duplicates, and remove them based on two columns.
- Highlight DuplicatesSelect the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. For whole rows, only the second copy, or matches across two columns, use a formula rule such as =COUNTIF($A$2:$A$9,A2)>1.
- Conditional FormattingConditional formatting colours a cell when a condition is true. Use Home > Conditional Formatting for preset rules, or New Rule > Use a formula with a rule like =$C2>100 to colour whole rows, overdue dates and text matches.
- Drop Down ListSelect the cells, go to Data > Data Validation, choose List, and type the items (North,South,East) or select a range as the source. Then make the list dynamic with UNIQUE, dependent on another list, and look up the chosen item.
- Compare Two ColumnsTo compare two columns row by row, use =A2=B2 (or EXACT for case). To find values in one column that are missing from the other, use COUNTIF, MATCH or XLOOKUP, and highlight the differences with conditional formatting.
- Pivot TableA pivot table groups the rows of a table by a category and totals a number for each one, without formulas: Insert > PivotTable, then drag fields to Rows and Values. Here are the steps, the four areas explained, and the same summary built with formulas.
