Excel formulas & functions, explained clearly.
Choose a level, pick a topic, and learn one useful skill at a time. Follow the examples with a small practice sheet, then explore 95 functions from the left menu.
Your route through Excel
A formula is an instruction that starts with =. A function is a ready-made calculation inside a formula. For example, =E2*F2 is a formula; =SUM(G2:G6) is a formula that uses the SUM function.
Five-row practice sheet
| Row | A · Date | B · Course | C · Type | D · City | E · Qty | F · Price | G · Revenue |
|---|---|---|---|---|---|---|---|
| 2 | 05 Jan 2026 | Excel Basics | Course | Cairo | 3 | 120 | 360 |
| 3 | 06 Jan 2026 | Power BI | Course | Giza | 2 | 240 | 480 |
| 4 | 08 Jan 2026 | Consulting | Service | Cairo | 1 | 210 | 210 |
| 5 | 10 Jan 2026 | SQL Intro | Course | Alexandria | 2 | 150 | 300 |
| 6 | 12 Jan 2026 | Excel Basics | Course | Cairo | 4 | 100 | 400 |
In G2 enter =E2*F2, then fill down. Revenue values in the table show the expected results.
01 — Try the practice sheet
Use the sample sales data below throughout this guide. Enter the headings in row 1 and the five records in rows 2–6. Column G is Revenue. In G2 enter =E2*F2, then fill down to G6.
=E2*F202 — Read a formula
The equals sign begins the calculation. E2 and F2 are cell references. The * symbol means multiplication. A colon means a range, so G2:G6 includes every cell from G2 through G6.
=SUM(G2:G6)03 — Pick your level
If you are new, begin with cell references. If you already use SUM and IF, go to multiple criteria and lookups. If you build reusable models, explore LET, LAMBDA, and dynamic arrays.
=IF(G2>=400,"High","Standard")One small sheet for every lesson
This fictional sales sheet makes every example concrete. Dates are real Excel dates, Quantity and Price are numbers, and Revenue is calculated. If your Excel uses semicolons as formula separators, replace commas in examples with semicolons.
Five-row practice sheet
| Row | A · Date | B · Course | C · Type | D · City | E · Qty | F · Price | G · Revenue |
|---|---|---|---|---|---|---|---|
| 2 | 05 Jan 2026 | Excel Basics | Course | Cairo | 3 | 120 | 360 |
| 3 | 06 Jan 2026 | Power BI | Course | Giza | 2 | 240 | 480 |
| 4 | 08 Jan 2026 | Consulting | Service | Cairo | 1 | 210 | 210 |
| 5 | 10 Jan 2026 | SQL Intro | Course | Alexandria | 2 | 150 | 300 |
| 6 | 12 Jan 2026 | Excel Basics | Course | Cairo | 4 | 100 | 400 |
In G2 enter =E2*F2, then fill down. Revenue values in the table show the expected results.
Some modern functions, including dynamic arrays and LAMBDA helpers, require Microsoft 365, Excel 2021, or Excel 2024. Each function page shows a version note where relevant.
Make formulas you can safely fill down
A relative reference changes when you copy a formula. A dollar sign locks a row or column. Understanding this is more valuable than memorizing dozens of functions.
Relative: move with the row
In G2, E2 and F2 refer to cells on the same row. Fill the formula down: G3 becomes =E3*F3 automatically.
=E2*F2Absolute: keep a rate fixed
Put a tax rate such as 0.14 in J1. The reference $J$1 stays fixed while you copy the calculation down.
=G2*(1+$J$1)Mixed: lock only one part
Use $E2 to keep column E while the row changes; use E$2 to keep row 2 while the column changes. This is useful in a two-way calculation grid.
=$E2*F$1Turn raw rows into answers
Start with a question. How much revenue did we make? How many orders are there? Is this order high value? Each question suggests a function.
Total revenue
SUM adds the five calculated revenues in column G.
=SUM(G2:G6)Count orders
COUNT counts numeric cells. COUNTA counts nonblank entries. Since order names in B2:B6 are text, COUNTA is the right tool here.
=COUNTA(B2:B6)Mark high-value sales
IF tests a condition, then returns one result when TRUE and another when FALSE.
=IF(G2>=400,"High","Standard")Clean labels and understand time
Text and dates often look simple but can hide extra spaces or formatting problems. Clean the data before you summarize it.
Remove extra spaces
TRIM removes repeated ordinary spaces from imported text. It does not remove every kind of nonbreaking space.
=TRIM(" Excel Basics ")Build a real date
DATE produces a date value Excel can use in calculations. Use YEAR, MONTH, and DAY to extract parts of a date.
=DATE(2026,1,5)Join a readable label
The & operator joins text. TEXT lets you control how a number or date appears inside a sentence.
=B2&" — "&TEXT(A2,"dd mmm yyyy")Ask more precise questions
SUMIF and COUNTIF use one condition. SUMIFS and COUNTIFS use several conditions. Put the sum range first in SUMIFS, then pairs of criteria range and criterion.
One condition
Add revenue only for rows whose Type is Course.
=SUMIF(C2:C6,"Course",G2:G6)Two conditions
Add revenue where City is Cairo AND Type is Course.
=SUMIFS(G2:G6,D2:D6,"Cairo",C2:C6,"Course")Use a cell as your criterion
Put Cairo in J2 so a reader can change the city without editing the formula.
=SUMIFS(G2:G6,D2:D6,J2)Bring the right answer to the right row
A lookup finds a key in one range and returns a related value from another range. Prefer an exact match for IDs, course names, and other discrete labels.
Find a course price
XLOOKUP searches the Course column and returns a value from Price. The fourth argument supplies a friendly result if nothing matches.
=XLOOKUP("SQL Intro",B2:B6,F2:F6,"Not found")Use an older-workbook alternative
MATCH finds the position of SQL Intro; INDEX returns the price at that position. The zero requests an exact match.
=INDEX(F2:F6,MATCH("SQL Intro",B2:B6,0))Handle a missing key
IFNA handles only the #N/A error, which is common for a missing lookup value. It leaves unrelated errors visible.
=IFNA(XLOOKUP("Unknown",B2:B6,F2:F6),"Check course name")Let your result grow automatically
Dynamic-array formulas can return multiple cells from one formula. Enter the formula in the top-left result cell and leave enough empty cells for the spill.
Return Cairo rows
FILTER keeps only records whose City is Cairo.
=FILTER(A2:G6,D2:D6="Cairo","No rows")Create a unique city list
UNIQUE returns one instance of each city. Wrap it in SORT for an alphabetic list.
=SORT(UNIQUE(D2:D6))Sort by revenue
SORTBY sorts the entire data range using the Revenue range in descending order.
=SORTBY(A2:G6,G2:G6,-1)Build formulas that explain themselves
Advanced functions are most useful when they reduce repeated logic or make the calculation easier to audit. These examples need recent Excel versions.
Name an intermediate result
LET stores the row-by-row revenue array as rev and then sums it. Longer formulas become easier to inspect.
=LET(rev,E2:E6*F2:F6,SUM(rev))Create a small custom function
LAMBDA accepts inputs u and p and returns their product. This example calls it immediately; save a LAMBDA in Name Manager to reuse it by name.
=LAMBDA(u,p,u*p)(E2,F2)Make a running total
SCAN passes each revenue into a named accumulator and returns every step of the total.
=SCAN(0,G2:G6,LAMBDA(total,x,total+x))Reshape information without helper columns
Modern array tools let you select, combine, and reshape records. The result may spill; keep the output area empty.
Show the first three rows
TAKE returns the first three records from the source range.
=TAKE(A2:G6,3)Keep only useful fields
CHOOSECOLS selects non-adjacent columns. Here it returns Course, City, and Revenue.
=CHOOSECOLS(A2:G6,2,4,7)Group revenue by city
GROUPBY combines city labels and revenue values into a summary. Availability varies across Microsoft 365 update channels.
=GROUPBY(D2:D6,G2:G6,SUM)Diagnose the message, not just the cell
Errors are clues. Check the error code first, then inspect the referenced cells, spelling, data types, and available spill space. Do not wrap every formula in IFERROR before you understand what failed.
#N/A
A lookup did not find a match. Check the exact key, leading/trailing spaces, and matching data types.
=IFNA(XLOOKUP(J2,B2:B6,F2:F6),"Not found")#SPILL!
A dynamic-array formula needs empty cells around its output. Clear the obstruction and enter the formula in a normal grid cell, not inside an Excel Table.
=UNIQUE(D2:D6)#VALUE! or #REF!
#VALUE! often means the wrong data type; #REF! means a reference is no longer valid. Check source cells and deleted rows/columns.
=ISNUMBER(E2)#DIV/0! — check the divisor. #NAME? — check function spelling or version. #REF! — repair deleted references. #SPILL! — clear the output area.
Test yourself on the same five rows
Try each question before opening the answer. Doing the work yourself is how the formulas become useful.
Beginner challenge
How many orders have the course name Excel Basics?
=COUNTIF(B2:B6,"Excel Basics")Intermediate challenge
What is total revenue for Cairo orders that are courses?
=SUMIFS(G2:G6,D2:D6,"Cairo",C2:C6,"Course")Advanced challenge
Make a sorted list of distinct cities without typing each city.
=SORT(UNIQUE(D2:D6))SUM
Add numbers.
A formula you can copy
=SUM(G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
AVERAGE
Find the arithmetic mean.
A formula you can copy
=AVERAGE(G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
MIN
Find the smallest value.
A formula you can copy
=MIN(G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
MAX
Find the largest value.
A formula you can copy
=MAX(G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
COUNT
Count cells containing numbers.
A formula you can copy
=COUNT(E2:E6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
COUNTA
Count nonempty cells.
A formula you can copy
=COUNTA(B2:B6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
COUNTBLANK
Count empty cells.
A formula you can copy
=COUNTBLANK(B2:B6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
ROUND
Round to a chosen decimal place.
A formula you can copy
=ROUND(12.345,2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
ROUNDUP
Always round away from zero.
A formula you can copy
=ROUNDUP(12.341,2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
ROUNDDOWN
Always round toward zero.
A formula you can copy
=ROUNDDOWN(12.349,2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
ABS
Return a number without its sign.
A formula you can copy
=ABS(-25)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
IF
Choose a result from a TRUE/FALSE test.
A formula you can copy
=IF(G2>=400,"High","Standard")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
AND
Require every test to be TRUE.
A formula you can copy
=AND(D2="Cairo",E2>=3)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
OR
Require at least one test to be TRUE.
A formula you can copy
=OR(D2="Cairo",D2="Giza")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
NOT
Reverse a TRUE/FALSE result.
A formula you can copy
=NOT(C2="Service")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
SUMIF
Add values meeting one condition.
A formula you can copy
=SUMIF(C2:C6,"Course",G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
COUNTIF
Count values meeting one condition.
A formula you can copy
=COUNTIF(B2:B6,"Excel Basics")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
AVERAGEIF
Average values meeting one condition.
A formula you can copy
=AVERAGEIF(D2:D6,"Cairo",G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
TODAY
Return today's date.
A formula you can copy
=TODAY()How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
NOW
Return the current date and time.
A formula you can copy
=NOW()How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
DATE
Build a real Excel date.
A formula you can copy
=DATE(2026,1,5)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
YEAR
Extract the year from a date.
A formula you can copy
=YEAR(A2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
MONTH
Extract the month from a date.
A formula you can copy
=MONTH(A2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
DAY
Extract the day from a date.
A formula you can copy
=DAY(A2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
LEFT
Take characters from the left.
A formula you can copy
=LEFT(B2,5)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
RIGHT
Take characters from the right.
A formula you can copy
=RIGHT(B2,6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
MID
Take characters from the middle.
A formula you can copy
=MID(B2,7,6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
LEN
Count characters, including spaces.
A formula you can copy
=LEN(B2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
TRIM
Remove repeated ordinary spaces.
A formula you can copy
=TRIM(" Excel Basics ")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
UPPER
Convert text to uppercase.
A formula you can copy
=UPPER(B2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
LOWER
Convert text to lowercase.
A formula you can copy
=LOWER(B2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
PROPER
Capitalize each word.
A formula you can copy
=PROPER("excel basics")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
CONCAT
Join text values.
A formula you can copy
=CONCAT(B2," — ",D2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2019+. If Excel reports #NAME?, check whether this function is available in your version.
TEXTJOIN
Join text with a delimiter.
A formula you can copy
=TEXTJOIN(" / ",TRUE,B2,D2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2019+. If Excel reports #NAME?, check whether this function is available in your version.
SUMIFS
Sum with several conditions.
A formula you can copy
=SUMIFS(G2:G6,D2:D6,"Cairo",C2:C6,"Course")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
COUNTIFS
Count rows meeting several conditions.
A formula you can copy
=COUNTIFS(D2:D6,"Cairo",C2:C6,"Course")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
AVERAGEIFS
Average with several conditions.
A formula you can copy
=AVERAGEIFS(G2:G6,D2:D6,"Cairo")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
IFERROR
Show a fallback when any error occurs.
A formula you can copy
=IFERROR(E2/F2,0)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
IFNA
Show a fallback only for #N/A.
A formula you can copy
=IFNA(XLOOKUP("SQL Intro",B2:B6,F2:F6),"Missing")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
XLOOKUP: recent Excel. If Excel reports #NAME?, check whether this function is available in your version.
IFS
Test several conditions in order.
A formula you can copy
=IFS(G2>=400,"High",G2>=200,"Medium",TRUE,"Low")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2019+. If Excel reports #NAME?, check whether this function is available in your version.
SWITCH
Map one value to several possible outputs.
A formula you can copy
=SWITCH(D2,"Cairo","CAI","Giza","GIZ","Other")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2019+. If Excel reports #NAME?, check whether this function is available in your version.
XLOOKUP
Find a match and return a related value.
A formula you can copy
=XLOOKUP("SQL Intro",B2:B6,F2:F6,"Not found")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2021+ / Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.
VLOOKUP
Look rightward in a table.
A formula you can copy
=VLOOKUP("SQL Intro",B2:F6,5,FALSE)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
INDEX
Return a value by position.
A formula you can copy
=INDEX(F2:F6,4)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
MATCH
Find the position of a value.
A formula you can copy
=MATCH("SQL Intro",B2:B6,0)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
XMATCH
Modern exact-match position lookup.
A formula you can copy
=XMATCH("SQL Intro",B2:B6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2021+ / Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.
FILTER
Return only rows that meet a test.
A formula you can copy
=FILTER(A2:G6,D2:D6="Cairo","No rows")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Dynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.
UNIQUE
List distinct values.
A formula you can copy
=UNIQUE(D2:D6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Dynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.
SORT
Sort a range by a column.
A formula you can copy
=SORT(A2:G6,7,-1)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Dynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.
SORTBY
Sort one range using another.
A formula you can copy
=SORTBY(A2:G6,G2:G6,-1)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Dynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.
TEXT
Display a number/date in a chosen format.
A formula you can copy
=TEXT(DATE(2026,1,5),"dd mmm yyyy")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
VALUE
Turn numeric text into a number.
A formula you can copy
=VALUE("125")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
SUBSTITUTE
Replace matching text.
A formula you can copy
=SUBSTITUTE("Excel-Basics","-"," ")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
REPLACE
Replace text by character position.
A formula you can copy
=REPLACE("ABC123",4,3,"999")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
FIND
Find text position, case-sensitive.
A formula you can copy
=FIND("B","Excel Basics")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
SEARCH
Find text position, case-insensitive.
A formula you can copy
=SEARCH("b","Excel Basics")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
TEXTBEFORE
Take text before a delimiter.
A formula you can copy
=TEXTBEFORE("Excel-Basics","-")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
TEXTAFTER
Take text after a delimiter.
A formula you can copy
=TEXTAFTER("Excel-Basics","-")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
TEXTSPLIT
Split text into cells.
A formula you can copy
=TEXTSPLIT("Excel,SQL,Power BI",",")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
EDATE
Move a date by whole months.
A formula you can copy
=EDATE(DATE(2026,1,5),3)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
EOMONTH
Find the last day of a month.
A formula you can copy
=EOMONTH(DATE(2026,1,5),0)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
NETWORKDAYS
Count workdays between dates.
A formula you can copy
=NETWORKDAYS(DATE(2026,1,5),DATE(2026,1,16))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
WORKDAY
Add workdays to a date.
A formula you can copy
=WORKDAY(DATE(2026,1,5),10)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
SUBTOTAL
Aggregate visible filtered rows.
A formula you can copy
=SUBTOTAL(109,G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
AGGREGATE
Aggregate while ignoring errors/hidden rows.
A formula you can copy
=AGGREGATE(9,6,G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
MEDIAN
Find the middle value.
A formula you can copy
=MEDIAN(G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
RANK.EQ
Rank a value among numbers.
A formula you can copy
=RANK.EQ(G2,G2:G6,0)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
STDEV.S
Estimate sample standard deviation.
A formula you can copy
=STDEV.S(G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
ISBLANK
Check if a cell is truly empty.
A formula you can copy
=ISBLANK(B2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
ISNUMBER
Check if a value is numeric.
A formula you can copy
=ISNUMBER(E2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
SUMPRODUCT
Multiply aligned arrays and add the results.
A formula you can copy
=SUMPRODUCT(E2:E6,F2:F6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
LET
Name parts of a long calculation.
A formula you can copy
=LET(rev,E2:E6*F2:F6,SUM(rev))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Excel 2021+ / Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.
LAMBDA
Create a reusable custom calculation.
A formula you can copy
=LAMBDA(u,p,u*p)(E2,F2)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
BYROW
Apply a LAMBDA to each row.
A formula you can copy
=BYROW(E2:F6,LAMBDA(r,PRODUCT(r)))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
BYCOL
Apply a LAMBDA to each column.
A formula you can copy
=BYCOL(E2:F6,LAMBDA(c,SUM(c)))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
MAP
Transform corresponding array values.
A formula you can copy
=MAP(E2:E6,F2:F6,LAMBDA(u,p,u*p))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
REDUCE
Accumulate an array into one result.
A formula you can copy
=REDUCE(0,G2:G6,LAMBDA(total,x,total+x))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
SCAN
Return every running total.
A formula you can copy
=SCAN(0,G2:G6,LAMBDA(total,x,total+x))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
MAKEARRAY
Generate values by row and column.
A formula you can copy
=MAKEARRAY(3,3,LAMBDA(r,c,r*c))How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
SEQUENCE
Generate a numbered array.
A formula you can copy
=SEQUENCE(5)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Dynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.
RANDARRAY
Generate random numbers as an array.
A formula you can copy
=RANDARRAY(5,1,1,100,TRUE)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Dynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.
TAKE
Keep rows or columns from an array edge.
A formula you can copy
=TAKE(A2:G6,3)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
DROP
Remove rows or columns from an array edge.
A formula you can copy
=DROP(A2:G6,1)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
CHOOSECOLS
Select non-adjacent columns.
A formula you can copy
=CHOOSECOLS(A2:G6,2,4,7)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
CHOOSEROWS
Select rows by position.
A formula you can copy
=CHOOSEROWS(A2:G6,1,3,5)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
VSTACK
Stack arrays vertically.
A formula you can copy
=VSTACK(A2:G3,A4:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
HSTACK
Stack arrays horizontally.
A formula you can copy
=HSTACK(B2:B6,G2:G6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
TOCOL
Flatten an array into one column.
A formula you can copy
=TOCOL(E2:F6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
TOROW
Flatten an array into one row.
A formula you can copy
=TOROW(E2:F6)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
WRAPROWS
Wrap a vector into rows.
A formula you can copy
=WRAPROWS(SEQUENCE(6),3)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
EXPAND
Pad an array to chosen dimensions.
A formula you can copy
=EXPAND(B2:B6,7,1,"-")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.
GROUPBY
Group and summarize values.
A formula you can copy
=GROUPBY(D2:D6,G2:G6,SUM)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.
PIVOTBY
Summarize by row and column fields.
A formula you can copy
=PIVOTBY(D2:D6,C2:C6,G2:G6,SUM)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.
INDIRECT
Turn text into a reference; use sparingly.
A formula you can copy
=INDIRECT("G2")How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.
OFFSET
Return a shifted reference; use sparingly.
A formula you can copy
=OFFSET(G2,2,0)How to use it
Click an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under , unless its values are written directly inside the formula.
Change the cell references or quoted criteria to fit your own workbook. If your Excel locale uses semicolons, replace the formula’s argument commas with semicolons.