TAKE Function in Excel: First or Last N Rows of a Range
TAKE returns the first (or last, with a negative number) rows or columns of an array — perfect for top-N reports.
Read article →Lookup & Reference
Excel Lookup & Reference functions explained with syntax, examples and common mistakes.
TRANSPOSE Function in Excel: Flip Rows and Columns
TRANSPOSE Function in Excel: Flip Rows and Columns
TRANSPOSE turns a vertical range horizontal and vice versa, and stays linked to the source.
Read article →OFFSET Function in Excel: A Range That Moves or Resizes
OFFSET Function in Excel: A Range That Moves or Resizes
OFFSET returns a range a set number of rows and columns away, optionally with a new size. Classic for rolling totals and dynamic chart…
Read article →PIVOTBY Function in Excel: A Cross-Tab Without a Pivot Table
PIVOTBY Function in Excel: A Cross-Tab Without a Pivot Table
PIVOTBY groups by rows and columns at once — regions down, months across — as a formula.
Read article →LOOKUP Function in Excel: The Old Lookup With a Famous Trick
LOOKUP Function in Excel: The Old Lookup With a Famous Trick
LOOKUP is an older function that needs sorted data for normal use — but its array form powers the well-known “last match” trick in…
Read article →SORT Function in Excel: Sort Data With a Formula
SORT Function in Excel: Sort Data With a Formula
SORT returns a sorted copy of a range; the original stays untouched and the result re-sorts as data changes.
Read article →AREAS Function in Excel: How Many Blocks in a Reference
AREAS Function in Excel: How Many Blocks in a Reference
AREAS counts separate ranges in a reference — used with INDEX’s area_num argument to switch between tables.
Read article →GETPIVOTDATA Function in Excel: Reliable Numbers From a Pivot Table
GETPIVOTDATA Function in Excel: Reliable Numbers From a Pivot Table
GETPIVOTDATA pulls a value from a pivot by field names, not by cell position — so your report keeps working when the pivot grows…
Read article →TRIMRANGE Function in Excel: Cut Empty Edges Off a Range
TRIMRANGE Function in Excel: Cut Empty Edges Off a Range
TRIMRANGE removes empty rows and columns from the outside of a range, so formulas can point at whole columns without processing a million blanks.
Read article →MATCH Function in Excel: Find the Position of a Value
MATCH Function in Excel: Find the Position of a Value
MATCH returns the position of a value in a list — the “where is it?” half of INDEX/MATCH.
Read article →XMATCH Function in Excel: MATCH With Better Defaults
XMATCH Function in Excel: MATCH With Better Defaults
XMATCH returns the position of an item, defaulting to exact match, and can search from the end or use wildcards.
Read article →GROUPBY Function in Excel: A Pivot-Style Summary in One Formula
GROUPBY Function in Excel: A Pivot-Style Summary in One Formula
GROUPBY groups rows by one or more columns and aggregates values — a live summary table that updates as data changes, without refreshing a…
Read article →