THE PRACTICAL EXCEL GUIDEExcel 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.تعلّم دوال إكسل خطوة بخطوة3 LEVELS · 12 LESSONS · 95 FUNCTIONS Browse lessons & functions ☰ On this page×YOUR GUIDEOverviewPractice dataBEGINNER 34 FUNCTIONSCell referencesFirst functionsText & datesINTERMEDIATE 36 FUNCTIONSMultiple criteriaLookupsDynamic listsADVANCED 25 FUNCTIONSReusable modelsArray toolsFix errorsPracticeFUNCTION LIBRARY 95 FUNCTIONSFind a functionBeginner 34ƒSUMƒAVERAGEƒMINƒMAXƒCOUNTƒCOUNTAƒCOUNTBLANKƒROUNDƒROUNDUPƒROUNDDOWNƒABSƒIFƒANDƒORƒNOTƒSUMIFƒCOUNTIFƒAVERAGEIFƒTODAYƒNOWƒDATEƒYEARƒMONTHƒDAYƒLEFTƒRIGHTƒMIDƒLENƒTRIMƒUPPERƒLOWERƒPROPERƒCONCATƒTEXTJOINIntermediate 36ƒSUMIFSƒCOUNTIFSƒAVERAGEIFSƒIFERRORƒIFNAƒIFSƒSWITCHƒXLOOKUPƒVLOOKUPƒINDEXƒMATCHƒXMATCHƒFILTERƒUNIQUEƒSORTƒSORTBYƒTEXTƒVALUEƒSUBSTITUTEƒREPLACEƒFINDƒSEARCHƒTEXTBEFOREƒTEXTAFTERƒTEXTSPLITƒEDATEƒEOMONTHƒNETWORKDAYSƒWORKDAYƒSUBTOTALƒAGGREGATEƒMEDIANƒRANK.EQƒSTDEV.SƒISBLANKƒISNUMBERAdvanced 25ƒSUMPRODUCTƒLETƒLAMBDAƒBYROWƒBYCOLƒMAPƒREDUCEƒSCANƒMAKEARRAYƒSEQUENCEƒRANDARRAYƒTAKEƒDROPƒCHOOSECOLSƒCHOOSEROWSƒVSTACKƒHSTACKƒTOCOLƒTOROWƒWRAPROWSƒEXPANDƒGROUPBYƒPIVOTBYƒINDIRECTƒOFFSETNo functions found. Try another word. BeginnerLearn the pattern, then choose the functionYour route through ExcelA 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.BeginnerWrite formulas with confidenceExplore →IntermediateAnalyze data and find answersExplore →AdvancedBuild reusable, modern modelsExplore →WORK ALONGFive-row practice sheetRowA · DateB · CourseC · TypeD · CityE · QtyF · PriceG · Revenue205 Jan 2026Excel BasicsCourseCairo3120360306 Jan 2026Power BICourseGiza2240480408 Jan 2026ConsultingServiceCairo1210210510 Jan 2026SQL IntroCourseAlexandria2150300612 Jan 2026Excel BasicsCourseCairo4100400In G2 enter =E2*F2, then fill down. Revenue values in the table show the expected results.0101 — Try the practice sheetUse 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*F2Copy formulaThe first row returns 360. Your five revenues are 360, 480, 210, 300, and 400.0202 — Read a formulaThe 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)Copy formulaThe five revenue values add up to 1,750.0303 — Pick your levelIf 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")Copy formulaFor G2 = 360, the formula returns Standard. BeginnerCopy the example into ExcelOne small sheet for every lessonThis 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.WORK ALONGFive-row practice sheetRowA · DateB · CourseC · TypeD · CityE · QtyF · PriceG · Revenue205 Jan 2026Excel BasicsCourseCairo3120360306 Jan 2026Power BICourseGiza2240480408 Jan 2026ConsultingServiceCairo1210210510 Jan 2026SQL IntroCourseAlexandria2150300612 Jan 2026Excel BasicsCourseCairo4100400In G2 enter =E2*F2, then fill down. Revenue values in the table show the expected results.A note about versionsSome 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. BeginnerRelative, absolute, and mixed referencesMake formulas you can safely fill downA 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.01Relative: move with the rowIn G2, E2 and F2 refer to cells on the same row. Fill the formula down: G3 becomes =E3*F3 automatically.=E2*F2Copy formulaUse this for row-by-row calculations.02Absolute: keep a rate fixedPut 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)Copy formulaA 360 sale including 14% tax becomes 410.40.03Mixed: lock only one partUse $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$1Copy formulaUse F4 to cycle reference types while editing a reference in many Excel versions. BeginnerTotals, averages, counts, and simple decisionsTurn raw rows into answersStart with a question. How much revenue did we make? How many orders are there? Is this order high value? Each question suggests a function.01Total revenueSUM adds the five calculated revenues in column G.=SUM(G2:G6)Copy formulaResult: 1,750.02Count ordersCOUNT counts numeric cells. COUNTA counts nonblank entries. Since order names in B2:B6 are text, COUNTA is the right tool here.=COUNTA(B2:B6)Copy formulaResult: 5.03Mark high-value salesIF tests a condition, then returns one result when TRUE and another when FALSE.=IF(G2>=400,"High","Standard")Copy formulaThe first row is Standard; rows with 480 and 400 are High. BeginnerSmall functions that prevent messy reportsClean labels and understand timeText and dates often look simple but can hide extra spaces or formatting problems. Clean the data before you summarize it.01Remove extra spacesTRIM removes repeated ordinary spaces from imported text. It does not remove every kind of nonbreaking space.=TRIM(" Excel Basics ")Copy formulaResult: Excel Basics.02Build a real dateDATE produces a date value Excel can use in calculations. Use YEAR, MONTH, and DAY to extract parts of a date.=DATE(2026,1,5)Copy formulaFormat the result as a date if Excel displays a serial number.03Join a readable labelThe & operator joins text. TEXT lets you control how a number or date appears inside a sentence.=B2&" — "&TEXT(A2,"dd mmm yyyy")Copy formulaA clear label is easier to read than a raw date serial. IntermediateSummarize by city, product, or bothAsk more precise questionsSUMIF 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.01One conditionAdd revenue only for rows whose Type is Course.=SUMIF(C2:C6,"Course",G2:G6)Copy formulaResult: 1,540.02Two conditionsAdd revenue where City is Cairo AND Type is Course.=SUMIFS(G2:G6,D2:D6,"Cairo",C2:C6,"Course")Copy formulaResult: 760.03Use a cell as your criterionPut Cairo in J2 so a reader can change the city without editing the formula.=SUMIFS(G2:G6,D2:D6,J2)Copy formulaThis is the start of a reusable dashboard. IntermediateXLOOKUP, INDEX/MATCH, and missing matchesBring the right answer to the right rowA 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.01Find a course priceXLOOKUP 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")Copy formulaResult: 150. XLOOKUP requires a recent Excel version.02Use an older-workbook alternativeMATCH 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))Copy formulaResult: 150.03Handle a missing keyIFNA 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")Copy formulaResult: Check course name. IntermediateFILTER, UNIQUE, SORT, and spill rangesLet your result grow automaticallyDynamic-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.01Return Cairo rowsFILTER keeps only records whose City is Cairo.=FILTER(A2:G6,D2:D6="Cairo","No rows")Copy formulaThe result spills into several rows and columns.02Create a unique city listUNIQUE returns one instance of each city. Wrap it in SORT for an alphabetic list.=SORT(UNIQUE(D2:D6))Copy formulaUse the list for a simple report selector.03Sort by revenueSORTBY sorts the entire data range using the Revenue range in descending order.=SORTBY(A2:G6,G2:G6,-1)Copy formulaIf you see #SPILL!, clear cells blocking the result. AdvancedLET, LAMBDA, and array helpersBuild formulas that explain themselvesAdvanced functions are most useful when they reduce repeated logic or make the calculation easier to audit. These examples need recent Excel versions.01Name an intermediate resultLET 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))Copy formulaResult: 1,750.02Create a small custom functionLAMBDA 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)Copy formulaResult: 360.03Make a running totalSCAN passes each revenue into a named accumulator and returns every step of the total.=SCAN(0,G2:G6,LAMBDA(total,x,total+x))Copy formulaResults: 360, 840, 1,050, 1,350, 1,750. AdvancedTAKE, CHOOSECOLS, VSTACK, and moreReshape information without helper columnsModern array tools let you select, combine, and reshape records. The result may spill; keep the output area empty.01Show the first three rowsTAKE returns the first three records from the source range.=TAKE(A2:G6,3)Copy formulaUseful for a compact preview.02Keep only useful fieldsCHOOSECOLS selects non-adjacent columns. Here it returns Course, City, and Revenue.=CHOOSECOLS(A2:G6,2,4,7)Copy formulaA tidy report without deleting source columns.03Group revenue by cityGROUPBY combines city labels and revenue values into a summary. Availability varies across Microsoft 365 update channels.=GROUPBY(D2:D6,G2:G6,SUM)Copy formulaCheck your Excel version if this function is unavailable. AdvancedA quick troubleshooting routeDiagnose the message, not just the cellErrors 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.01#N/AA 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")Copy formulaUse a fallback only when a missing match is expected.02#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)Copy formulaThe formula should spill into open cells.03#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)Copy formulaTest whether a supposed number is actually numeric.Quick checks#DIV/0! — check the divisor. #NAME? — check function spelling or version. #REF! — repair deleted references. #SPILL! — clear the output area. AdvancedThree short challenges with answersTest yourself on the same five rowsTry each question before opening the answer. Doing the work yourself is how the formulas become useful.01Beginner challengeHow many orders have the course name Excel Basics?=COUNTIF(B2:B6,"Excel Basics")Copy formulaAnswer: 2.02Intermediate challengeWhat is total revenue for Cairo orders that are courses?=SUMIFS(G2:G6,D2:D6,"Cairo",C2:C6,"Course")Copy formulaAnswer: 760.03Advanced challengeMake a sorted list of distinct cities without typing each city.=SORT(UNIQUE(D2:D6))Copy formulaThe result spills a distinct, alphabetically sorted city list. Beginner functionFUNCTION REFERENCE SUMAdd numbers. TRY IT IN EXCELA formula you can copy=SUM(G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringAVERAGE →MIN →MAX →Open beginner lesson → Beginner functionFUNCTION REFERENCE AVERAGEFind the arithmetic mean. TRY IT IN EXCELA formula you can copy=AVERAGE(G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →MIN →MAX →Open beginner lesson → Beginner functionFUNCTION REFERENCE MINFind the smallest value. TRY IT IN EXCELA formula you can copy=MIN(G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MAX →Open beginner lesson → Beginner functionFUNCTION REFERENCE MAXFind the largest value. TRY IT IN EXCELA formula you can copy=MAX(G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE COUNTCount cells containing numbers. TRY IT IN EXCELA formula you can copy=COUNT(E2:E6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE COUNTACount nonempty cells. TRY IT IN EXCELA formula you can copy=COUNTA(B2:B6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE COUNTBLANKCount empty cells. TRY IT IN EXCELA formula you can copy=COUNTBLANK(B2:B6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE ROUNDRound to a chosen decimal place. TRY IT IN EXCELA formula you can copy=ROUND(12.345,2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE ROUNDUPAlways round away from zero. TRY IT IN EXCELA formula you can copy=ROUNDUP(12.341,2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE ROUNDDOWNAlways round toward zero. TRY IT IN EXCELA formula you can copy=ROUNDDOWN(12.349,2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE ABSReturn a number without its sign. TRY IT IN EXCELA formula you can copy=ABS(-25)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE IFChoose a result from a TRUE/FALSE test. TRY IT IN EXCELA formula you can copy=IF(G2>=400,"High","Standard")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE ANDRequire every test to be TRUE. TRY IT IN EXCELA formula you can copy=AND(D2="Cairo",E2>=3)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE ORRequire at least one test to be TRUE. TRY IT IN EXCELA formula you can copy=OR(D2="Cairo",D2="Giza")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE NOTReverse a TRUE/FALSE result. TRY IT IN EXCELA formula you can copy=NOT(C2="Service")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE SUMIFAdd values meeting one condition. TRY IT IN EXCELA formula you can copy=SUMIF(C2:C6,"Course",G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE COUNTIFCount values meeting one condition. TRY IT IN EXCELA formula you can copy=COUNTIF(B2:B6,"Excel Basics")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE AVERAGEIFAverage values meeting one condition. TRY IT IN EXCELA formula you can copy=AVERAGEIF(D2:D6,"Cairo",G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE TODAYReturn today's date. TRY IT IN EXCELA formula you can copy=TODAY()Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE NOWReturn the current date and time. TRY IT IN EXCELA formula you can copy=NOW()Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE DATEBuild a real Excel date. TRY IT IN EXCELA formula you can copy=DATE(2026,1,5)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE YEARExtract the year from a date. TRY IT IN EXCELA formula you can copy=YEAR(A2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE MONTHExtract the month from a date. TRY IT IN EXCELA formula you can copy=MONTH(A2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE DAYExtract the day from a date. TRY IT IN EXCELA formula you can copy=DAY(A2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE LEFTTake characters from the left. TRY IT IN EXCELA formula you can copy=LEFT(B2,5)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE RIGHTTake characters from the right. TRY IT IN EXCELA formula you can copy=RIGHT(B2,6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE MIDTake characters from the middle. TRY IT IN EXCELA formula you can copy=MID(B2,7,6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE LENCount characters, including spaces. TRY IT IN EXCELA formula you can copy=LEN(B2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE TRIMRemove repeated ordinary spaces. TRY IT IN EXCELA formula you can copy=TRIM(" Excel Basics ")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE UPPERConvert text to uppercase. TRY IT IN EXCELA formula you can copy=UPPER(B2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE LOWERConvert text to lowercase. TRY IT IN EXCELA formula you can copy=LOWER(B2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE PROPERCapitalize each word. TRY IT IN EXCELA formula you can copy=PROPER("excel basics")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE CONCATJoin text values. TRY IT IN EXCELA formula you can copy=CONCAT(B2," — ",D2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2019+. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Beginner functionFUNCTION REFERENCE TEXTJOINJoin text with a delimiter. TRY IT IN EXCELA formula you can copy=TEXTJOIN(" / ",TRUE,B2,D2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2019+. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUM →AVERAGE →MIN →Open beginner lesson → Intermediate functionFUNCTION REFERENCE SUMIFSSum with several conditions. TRY IT IN EXCELA formula you can copy=SUMIFS(G2:G6,D2:D6,"Cairo",C2:C6,"Course")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringCOUNTIFS →AVERAGEIFS →IFERROR →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE COUNTIFSCount rows meeting several conditions. TRY IT IN EXCELA formula you can copy=COUNTIFS(D2:D6,"Cairo",C2:C6,"Course")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →AVERAGEIFS →IFERROR →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE AVERAGEIFSAverage with several conditions. TRY IT IN EXCELA formula you can copy=AVERAGEIFS(G2:G6,D2:D6,"Cairo")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →IFERROR →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE IFERRORShow a fallback when any error occurs. TRY IT IN EXCELA formula you can copy=IFERROR(E2/F2,0)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE IFNAShow a fallback only for #N/A. TRY IT IN EXCELA formula you can copy=IFNA(XLOOKUP("SQL Intro",B2:B6,F2:F6),"Missing")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteXLOOKUP: recent Excel. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE IFSTest several conditions in order. TRY IT IN EXCELA formula you can copy=IFS(G2>=400,"High",G2>=200,"Medium",TRUE,"Low")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2019+. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE SWITCHMap one value to several possible outputs. TRY IT IN EXCELA formula you can copy=SWITCH(D2,"Cairo","CAI","Giza","GIZ","Other")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2019+. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE XLOOKUPFind a match and return a related value. TRY IT IN EXCELA formula you can copy=XLOOKUP("SQL Intro",B2:B6,F2:F6,"Not found")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2021+ / Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE VLOOKUPLook rightward in a table. TRY IT IN EXCELA formula you can copy=VLOOKUP("SQL Intro",B2:F6,5,FALSE)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE INDEXReturn a value by position. TRY IT IN EXCELA formula you can copy=INDEX(F2:F6,4)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE MATCHFind the position of a value. TRY IT IN EXCELA formula you can copy=MATCH("SQL Intro",B2:B6,0)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE XMATCHModern exact-match position lookup. TRY IT IN EXCELA formula you can copy=XMATCH("SQL Intro",B2:B6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2021+ / Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE FILTERReturn only rows that meet a test. TRY IT IN EXCELA formula you can copy=FILTER(A2:G6,D2:D6="Cairo","No rows")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteDynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE UNIQUEList distinct values. TRY IT IN EXCELA formula you can copy=UNIQUE(D2:D6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteDynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE SORTSort a range by a column. TRY IT IN EXCELA formula you can copy=SORT(A2:G6,7,-1)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteDynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE SORTBYSort one range using another. TRY IT IN EXCELA formula you can copy=SORTBY(A2:G6,G2:G6,-1)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteDynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE TEXTDisplay a number/date in a chosen format. TRY IT IN EXCELA formula you can copy=TEXT(DATE(2026,1,5),"dd mmm yyyy")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE VALUETurn numeric text into a number. TRY IT IN EXCELA formula you can copy=VALUE("125")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE SUBSTITUTEReplace matching text. TRY IT IN EXCELA formula you can copy=SUBSTITUTE("Excel-Basics","-"," ")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE REPLACEReplace text by character position. TRY IT IN EXCELA formula you can copy=REPLACE("ABC123",4,3,"999")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE FINDFind text position, case-sensitive. TRY IT IN EXCELA formula you can copy=FIND("B","Excel Basics")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE SEARCHFind text position, case-insensitive. TRY IT IN EXCELA formula you can copy=SEARCH("b","Excel Basics")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE TEXTBEFORETake text before a delimiter. TRY IT IN EXCELA formula you can copy=TEXTBEFORE("Excel-Basics","-")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE TEXTAFTERTake text after a delimiter. TRY IT IN EXCELA formula you can copy=TEXTAFTER("Excel-Basics","-")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE TEXTSPLITSplit text into cells. TRY IT IN EXCELA formula you can copy=TEXTSPLIT("Excel,SQL,Power BI",",")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE EDATEMove a date by whole months. TRY IT IN EXCELA formula you can copy=EDATE(DATE(2026,1,5),3)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE EOMONTHFind the last day of a month. TRY IT IN EXCELA formula you can copy=EOMONTH(DATE(2026,1,5),0)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE NETWORKDAYSCount workdays between dates. TRY IT IN EXCELA formula you can copy=NETWORKDAYS(DATE(2026,1,5),DATE(2026,1,16))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE WORKDAYAdd workdays to a date. TRY IT IN EXCELA formula you can copy=WORKDAY(DATE(2026,1,5),10)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE SUBTOTALAggregate visible filtered rows. TRY IT IN EXCELA formula you can copy=SUBTOTAL(109,G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE AGGREGATEAggregate while ignoring errors/hidden rows. TRY IT IN EXCELA formula you can copy=AGGREGATE(9,6,G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE MEDIANFind the middle value. TRY IT IN EXCELA formula you can copy=MEDIAN(G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE RANK.EQRank a value among numbers. TRY IT IN EXCELA formula you can copy=RANK.EQ(G2,G2:G6,0)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE STDEV.SEstimate sample standard deviation. TRY IT IN EXCELA formula you can copy=STDEV.S(G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE ISBLANKCheck if a cell is truly empty. TRY IT IN EXCELA formula you can copy=ISBLANK(B2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Intermediate functionFUNCTION REFERENCE ISNUMBERCheck if a value is numeric. TRY IT IN EXCELA formula you can copy=ISNUMBER(E2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMIFS →COUNTIFS →AVERAGEIFS →Open intermediate lesson → Advanced functionFUNCTION REFERENCE SUMPRODUCTMultiply aligned arrays and add the results. TRY IT IN EXCELA formula you can copy=SUMPRODUCT(E2:E6,F2:F6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringLET →LAMBDA →BYROW →Open advanced lesson → Advanced functionFUNCTION REFERENCE LETName parts of a long calculation. TRY IT IN EXCELA formula you can copy=LET(rev,E2:E6*F2:F6,SUM(rev))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteExcel 2021+ / Microsoft 365. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LAMBDA →BYROW →Open advanced lesson → Advanced functionFUNCTION REFERENCE LAMBDACreate a reusable custom calculation. TRY IT IN EXCELA formula you can copy=LAMBDA(u,p,u*p)(E2,F2)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →BYROW →Open advanced lesson → Advanced functionFUNCTION REFERENCE BYROWApply a LAMBDA to each row. TRY IT IN EXCELA formula you can copy=BYROW(E2:F6,LAMBDA(r,PRODUCT(r)))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE BYCOLApply a LAMBDA to each column. TRY IT IN EXCELA formula you can copy=BYCOL(E2:F6,LAMBDA(c,SUM(c)))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE MAPTransform corresponding array values. TRY IT IN EXCELA formula you can copy=MAP(E2:E6,F2:F6,LAMBDA(u,p,u*p))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE REDUCEAccumulate an array into one result. TRY IT IN EXCELA formula you can copy=REDUCE(0,G2:G6,LAMBDA(total,x,total+x))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE SCANReturn every running total. TRY IT IN EXCELA formula you can copy=SCAN(0,G2:G6,LAMBDA(total,x,total+x))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE MAKEARRAYGenerate values by row and column. TRY IT IN EXCELA formula you can copy=MAKEARRAY(3,3,LAMBDA(r,c,r*c))Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE SEQUENCEGenerate a numbered array. TRY IT IN EXCELA formula you can copy=SEQUENCE(5)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteDynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE RANDARRAYGenerate random numbers as an array. TRY IT IN EXCELA formula you can copy=RANDARRAY(5,1,1,100,TRUE)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteDynamic arrays. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE TAKEKeep rows or columns from an array edge. TRY IT IN EXCELA formula you can copy=TAKE(A2:G6,3)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE DROPRemove rows or columns from an array edge. TRY IT IN EXCELA formula you can copy=DROP(A2:G6,1)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE CHOOSECOLSSelect non-adjacent columns. TRY IT IN EXCELA formula you can copy=CHOOSECOLS(A2:G6,2,4,7)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE CHOOSEROWSSelect rows by position. TRY IT IN EXCELA formula you can copy=CHOOSEROWS(A2:G6,1,3,5)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE VSTACKStack arrays vertically. TRY IT IN EXCELA formula you can copy=VSTACK(A2:G3,A4:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE HSTACKStack arrays horizontally. TRY IT IN EXCELA formula you can copy=HSTACK(B2:B6,G2:G6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE TOCOLFlatten an array into one column. TRY IT IN EXCELA formula you can copy=TOCOL(E2:F6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE TOROWFlatten an array into one row. TRY IT IN EXCELA formula you can copy=TOROW(E2:F6)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE WRAPROWSWrap a vector into rows. TRY IT IN EXCELA formula you can copy=WRAPROWS(SEQUENCE(6),3)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE EXPANDPad an array to chosen dimensions. TRY IT IN EXCELA formula you can copy=EXPAND(B2:B6,7,1,"-")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365 / Excel 2024. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE GROUPBYGroup and summarize values. TRY IT IN EXCELA formula you can copy=GROUPBY(D2:D6,G2:G6,SUM)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE PIVOTBYSummarize by row and column fields. TRY IT IN EXCELA formula you can copy=PIVOTBY(D2:D6,C2:C6,G2:G6,SUM)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Version noteMicrosoft 365. If Excel reports #NAME?, check whether this function is available in your version.Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE INDIRECTTurn text into a reference; use sparingly. TRY IT IN EXCELA formula you can copy=INDIRECT("G2")Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Advanced functionFUNCTION REFERENCE OFFSETReturn a shifted reference; use sparingly. TRY IT IN EXCELA formula you can copy=OFFSET(G2,2,0)Copy formula 01How to use itClick an empty cell, paste the formula, and press Enter. The example uses the five-row practice sheet shown under Practice data, 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. Continue exploringSUMPRODUCT →LET →LAMBDA →Open advanced lesson → Microsoft’s full Excel function list ↗Back to overview ↑