Advanced Excel Functions Guide
Master high-performance Excel formulas: XLOOKUP, INDEX & MATCH, SUMIFS, TAKE, DROP, CHOOSECOLS, and dynamic arrays for data analysis.
🔎Lookup & Reference Advanced Functions
5 functionsReturns a specified number of contiguous rows or columns from the start or end of an array.
=TAKE(array, rows, [columns])=TAKE(SORT(A2:C50, 3, -1), 5)Excludes a specified number of rows or columns from the start or end of an array.
=DROP(array, rows, [columns])=DROP(A1:C50, 1)Returns specified columns from an array or range.
=CHOOSECOLS(array, col_num1, [col_num2], ...)=CHOOSECOLS(A2:F100, 1, 3, 5)Returns specified rows from an array or range.
=CHOOSEROWS(array, row_num1, [row_num2], ...)=CHOOSEROWS(A2:F100, 1, 5, 10)Creates custom, reusable functions in Excel without writing VBA code.
=LAMBDA([parameter1, ...], calculation)=LAMBDA(x, y, x*1.1 + y)📊Statistical Advanced Functions
2 functionsEstimates standard deviation based on a sample of numbers.
=STDEV.S(number1, [number2], ...)=STDEV.S(C2:C100)Estimates variance based on a sample of numbers.
=VAR.S(number1, [number2], ...)=VAR.S(C2:C100)💰Financial Advanced Functions
3 functionsReturns the present value of an investment (total amount that a series of future payments is worth now).
=PV(rate, nper, pmt, [fv], [type])=PV(0.05/12, 60, -300)Returns the internal rate of return for a series of periodic cash flows.
=IRR(values, [guess])=IRR(B2:B10)Returns the interest rate per period of an annuity or loan.
=RATE(nper, pmt, pv, [fv], [type], [guess])=RATE(36, -500, 15000)Review Essential Basic Excel Functions
Learn SUM, AVERAGE, COUNT, IF, VLOOKUP, CONCATENATE, TODAY, and beginner formulas.