Do a case-sensitive lookup with EXACT and INDEX/MATCH.

Return the first or last non-blank value with the LOOKUP trick or XLOOKUP.

Return the most recent (last) match with XLOOKUP search_mode -1 or the LOOKUP trick.

Return several columns from one lookup that spills the whole record.

Return the 2nd, 3rd, or nth match with FILTER+INDEX or INDEX/SMALL.

Look up a value on two or more keys at once by joining them inside XLOOKUP or INDEX/MATCH.

Look up on a fragment using wildcards with VLOOKUP or XLOOKUP.

Land a number in the right tier or tax bracket with XLOOKUP match_mode -1 — no nested IFs.

Pull the value where a row and a column meet, with nested XLOOKUP or INDEX/MATCH/MATCH.

Build a cumulative running total with one anchored, expanding-range SUM (or SCAN).

Total a column on two or more conditions at once with SUMIFS (AND logic).

Add every Nth row with SUMPRODUCT and MOD on the row number.

Sum only filtered/visible rows with SUBTOTAL (or AGGREGATE).

Total values that fall in a given month with SUMIFS and EOMONTH — year-safe, no helper column.

Total amounts for a quarter with SUMIFS and EOMONTH date boundaries.

Total rows whose label contains text using SUMIF with wildcards.

Add the same cell across many sheets with a 3D reference.

Total just the largest few values with LARGE and SUMPRODUCT — top 3, top 5, or N from a cell.

Build a region-by-month matrix report with one SUMIFS filled across and down.

Count rows that meet several conditions at once with COUNTIFS.

Count empty cells in a range with COUNTBLANK.

Count cells containing a word or fragment with COUNTIF and wildcards.

Count text, numbers, non-blank or blank cells with the right COUNT function.

Count dates that fall between two dates with COUNTIFS.

Count how many dates fall on a weekday with SUMPRODUCT and WEEKDAY.

Count how many distinct entries are in a list — COUNTA(UNIQUE()) or the classic SUMPRODUCT trick.

Count distinct values within each group with UNIQUE+FILTER or SUMPRODUCT.

Group numbers into bands and count each with FREQUENCY or COUNTIFS.

Number each occurrence of a value with an expanding COUNTIF.

Average a column that contains errors with AGGREGATE option 6.

Average the values in one category with AVERAGEIF / AVERAGEIFS.

Average only the best few values by nesting LARGE inside AVERAGE.

Smooth a series with a rolling AVERAGE window that slides down the column.

Weight some values more than others with SUMPRODUCT divided by total weight.

Clamp a number between a floor and ceiling with nested MIN and MAX.

Get the 2nd, 3rd, or nth largest/smallest value with LARGE and SMALL.

Find the biggest value in a given month with MAXIFS and date boundaries.

Find the largest value that meets a condition with MAXIFS.

Find the smallest value that meets a condition with MINIFS.

Replace #N/A and #DIV/0! errors with a clean fallback using IFERROR / IFNA.

Turn scores into letter grades with IFS, nested IF, or a lookup table.

Mark or highlight repeated values with COUNTIF — labels and conditional formatting.

Make an IF decision on several conditions at once by nesting AND or OR.

Map a value to a result with SWITCH instead of nested IFs.

Test whether a cell contains text with ISNUMBER and SEARCH.

Test whether a formula errored with ISERROR or ISNA.

Test whether a cell is empty with ISBLANK inside IF.

Tell real numbers from text-numbers with ISNUMBER and ISTEXT.

Capitalize names with PROPER, or force case with UPPER and LOWER.

Strip extra spaces, line breaks and non-breaking spaces with TRIM, CLEAN and SUBSTITUTE.

Count words in a cell by counting spaces with LEN and SUBSTITUTE.

Pull first and last names out of a full-name cell with TEXTBEFORE/AFTER or LEFT/RIGHT.

Pull the number out of mixed text with TEXTAFTER/VALUE or MID/FIND.

Pull text between two characters with TEXTBEFORE/AFTER or MID/FIND.

Swap text inside a formula with SUBSTITUTE or REPLACE.

Combine a range of cells into one delimited string with TEXTJOIN — skips blanks.

Add leading zeros to numbers to a fixed width with TEXT.

Break one cell into columns on a delimiter with TEXTSPLIT (or LEFT/MID/FIND).

Shift a date by whole months or years with EDATE.

Turn a birthdate into an age in whole years with DATEDIF and TODAY — updates itself daily.

Calculate hours between two times by subtracting and multiplying by 24.

Turn text dates into real dates with DATEVALUE or a DATE rebuild.

Count days until or since a date with simple date subtraction and TODAY.

Jump to the next specific weekday from a date with WEEKDAY and MOD.

Get the first or last day of any month with EOMONTH — leap-year safe.

Get the day-of-week name from a date with TEXT (dddd / ddd).

Turn a date into its calendar quarter with MONTH and ROUNDUP.

Get the ISO or US week number of a date with ISOWEEKNUM / WEEKNUM.

Find the nth weekday of a month (3rd Thursday) with DATE and WEEKDAY.

Find how many days are in a month with EOMONTH and DAY (leap-safe).

Split daily hours into regular and overtime with MIN and MAX.

Count business days between dates, skipping weekends and holidays, with NETWORKDAYS.

Aggregate an array to one value with REDUCE and LAMBDA.

Build a reusable custom function with LAMBDA + Name Manager.

Build a calculated grid from row/column positions with MAKEARRAY.

Append ranges into one with VSTACK and HSTACK.

Pull every record matching a condition into a report area with FILTER (or INDEX/SMALL).

Extract every row that meets a condition into a live, self-updating range with FILTER.

Flatten a grid into one column with TOCOL (or TOROW).

Spill a list of numbers, dates, or a grid with SEQUENCE.

Reference a whole spill range with the # operator.

Fold a list into a grid of any width with WRAPROWS / WRAPCOLS.

Make running totals, max, or product with SCAN and LAMBDA.

Sort a table by several keys with SORTBY (live formula).

Summarize each row or column with BYROW / BYCOL.

Transform every value of an array with MAP and LAMBDA.

Turn a column with repeats into a clean, alphabetized list with SORT(UNIQUE()).

Rank a list highest-to-lowest with RANK.EQ, including a no-gaps tiebreaker.

Calculate percent change and percent of total — the right base and formatting.

Always round up or down with ROUNDUP, ROUNDDOWN, CEILING and FLOOR.

Round to N significant figures with ROUND and LOG10.

Round to decimal places with ROUND or to any multiple with MROUND.

Compute the compound annual growth rate from a start and end value.

Calculate a fixed loan payment from rate, term and amount with PMT.

Grow a lump sum with the compound interest formula (1+rate)^periods.

Project what regular savings grow to with FV and compound interest.

Value future cash flows in today's money with NPV (initial added outside).

Add alternating row shading with ISEVEN(ROW()) — zebra stripes.

Turn numbers into a color-gradient heat map with Color Scales.

Highlight rows missing any data with COUNTBLANK in a CF rule.

Highlight whole rows with a formula rule — the $C2 mixed-reference trick.

Shade the top N values with a LARGE-based conditional-formatting rule.

Build a drop-down list with Data Validation so entry is pick-from-a-menu.

Make a cascading drop-down where the second list depends on the first (INDIRECT).

Block duplicate entries with a COUNTIF data-validation rule.

Limit a cell to whole numbers or a value range with Data Validation.

Measure how two columns move together with CORREL (-1 to +1).

Flag outliers with the IQR rule (QUARTILE) or a z-score test.

Project a future value along a trend line with FORECAST or TREND.

Find the median within a group with MEDIAN+FILTER or MEDIAN(IF()).

Compute percentiles and quartiles with PERCENTILE and QUARTILE.

Measure spread with STDEV.S (sample) or STDEV.P (population).