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 →Excel
Excel formulas, unusual formula combinations, VBA, Power Query and dashboards — from basics to clever tricks.
PERCENTOF Function in Excel: Share of the Total
PERCENTOF Function in Excel: Share of the Total
PERCENTOF returns a subset’s share of a total — mostly used inside GROUPBY and PIVOTBY to show % of total.
Read article →TEXTBEFORE Function in Excel: Text Before a Delimiter
TEXTBEFORE Function in Excel: Text Before a Delimiter
TEXTBEFORE returns everything before a delimiter; TEXTAFTER everything after. They replace most LEFT/FIND and MID/FIND combinations.
Read article →FILTERXML Function in Excel: Pick Values Out of XML
FILTERXML Function in Excel: Pick Values Out of XML
FILTERXML extracts values from XML with an XPath. Its famous side use: splitting text in versions without TEXTSPLIT.
Read article →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 →RAND Function in Excel: Random Decimal Between 0 and 1
RAND Function in Excel: Random Decimal Between 0 and 1
RAND returns a new random number between 0 and 1 on every recalculation — useful for random samples and shuffling.
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 →Top N With Criteria in Excel: Biggest Orders by Region, Month or Customer
Top N With Criteria in Excel: Biggest Orders by Region, Month or Customer
Get the top 5 values that meet a condition — and the rows behind them — with LARGE+IF, AGGREGATE, FILTER+SORT+TAKE and SORTBY, in old…
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 →TOCOL Function in Excel: Turn a Grid Into One Column
TOCOL Function in Excel: Turn a Grid Into One Column
TOCOL flattens a 2-D range into a single column, optionally skipping blanks and errors. TOROW does the same into a row.
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 →