Function library
Every Excel function
All 521 functions from Microsoft’s list, grouped the way Excel groups them. 500 run right here in the practice sheet; the rest come with step-by-step guides for desktop Excel.
Lookup & reference
Find and return data: XLOOKUP, VLOOKUP, INDEX/MATCH, FILTER, SORT, UNIQUE. · 30 exercises
40 functions
ADDRESSA cell address as textAREASNumber of areas in a referenceCHOOSEPicks a value by positionCHOOSECOLSPicks columns from an arrayCHOOSEROWSPicks rows from an arrayCOLUMNColumn number of a referenceCOLUMNSNumber of columnsDROPRemoves rows or columns from the start or endEXPANDGrows an array to a sizeFILTERRows that meet a conditionFORMULATEXTThe formula in a cell, as textGETPIVOTDATAReads one number out of a PivotTable by naming it (“Sales for North”), so the formula keeps working when the PivotTable changes shape.GROUPBYGroups rows and summarises values (like a quick PivotTable)HLOOKUPLooks across the first rowHSTACKJoins arrays side by sideHYPERLINKA clickable link (shows its name)IMAGEPuts a picture from the web inside a cell, where it moves and sorts with the data.INDEXValue at a row and columnINDIRECTThe reference named by some textLOOKUPApproximate lookup in a row or columnMATCHPosition of a value in a listOFFSETA reference moved from a starting pointPIVOTBYSummarises values by rows and columns (like a PivotTable)ROWRow number of a referenceROWSNumber of rowsRTDShows live, real-time data pushed by a program on your computer (for example a trading platform).SORTSorts a rangeSORTBYSorts by other rangesTAKERows or columns from the start or endTOCOLTurns an array into one columnTOROWTurns an array into one rowTRANSPOSESwaps rows and columnsTRIMRANGERemoves empty edge rows and columnsUNIQUERemoves duplicatesVLOOKUPLooks down the first columnVSTACKStacks arrays on top of each otherWRAPCOLSWraps a list into columnsWRAPROWSWraps a list into rowsXLOOKUPFinds a value and returns its matchXMATCHPosition of a value (modern MATCH)