Excel Glossary

ABS

Returns the absolute (positive) value of a number.

Ignores the sign - useful when comparing magnitudes regardless of direction.

=ABS(-42) → 42

AND

Returns TRUE only when every argument is TRUE.

Use inside IF for multi-condition gates. For OR-style logic across ranges, SUMPRODUCT is usually shorter.

=IF(AND(A1>0, A1<100), "in range", "")

Array formula

A formula that operates on a range of values, not a single cell.

In modern Excel (365 / 2021), arrays spill automatically. In Excel 2019 and earlier, you needed Ctrl+Shift+Enter. SUMPRODUCT, FILTER, SORT, UNIQUE and XLOOKUP all work in array mode.

AVERAGEIFS

Returns the average of cells that match multiple criteria.

Companion to SUMIFS and COUNTIFS. The criteria are AND-joined; for OR logic, use AVERAGE + IF or SUMPRODUCT.

=AVERAGEIFS(amounts, region, "North", year, 2026)

BETA.INV

Returns the inverse of the cumulative beta probability density function.

Used in Monte Carlo simulation for inputs that are probabilities between 0 and 1 (conversion rates, defect rates, win rates).

=BETA.INV(RAND(), 2, 5)

CHOOSE

Returns a value from a list based on an index number.

The original lookup-by-index function. Use it for small fixed lists; for anything larger, use XLOOKUP against a hidden table.

=CHOOSE(2, "North", "South", "East", "West") → "South"

CONCAT

Modern replacement for CONCATENATE - joins a range of cells.

Accepts ranges. CONCATENATE only accepts individual cells. Use CONCAT for any new work in Excel 365.

=CONCAT(A1:A5)

COUNTIFS

Counts cells that match multiple criteria.

Companion to SUMIFS. For OR logic, use COUNTIF + COUNTIF or SUMPRODUCT.

=COUNTIFS(region, "North", year, 2026)

Conditional formatting

Cell formatting that changes based on a value or formula.

Built-in presets (color scales, data bars, icon sets) plus custom formula rules. The single highest-ROI feature for making tables readable.

Read the deep-dive on Conditional formatting

DAX

Data Analysis Expressions - formula language for Power Pivot and Power BI.

DAX measures, calculated columns and calculated tables. Different mental model from Excel formulas - works on tables, not cells. Five DAX functions cover 90% of dashboards: CALCULATE, FILTER, ALL, SUMX, RELATED.

Read the deep-dive on DAX

Data table

A one-input or two-input sensitivity table that recalculates a formula across a range of inputs.

The pattern for break-even analysis, scenario analysis, and tornado charts. Use Data → What-If Analysis → Data Table.

Read the deep-dive on Data table

Data validation

Cell-level rules that restrict what a user can type or paste.

Drop-downs, number ranges, dates, text length, custom formulas. The single highest-ROI feature for stopping bad data at the door.

Read the deep-dive on Data validation

DATE

Constructs a date from year, month, day components.

The safest way to build a date in a formula - avoids the locale-dependent text parsing of "1/1/2026".

=DATE(2026, 1, 1)

DATEDIF

Returns the difference between two dates in years, months, or days.

Hidden function - does not appear in autocomplete. The right tool for tenure, age, and cohort month calculations.

=DATEDIF(start, end, "m") → months between

Dynamic array

A formula that returns multiple values into neighbouring cells (the spill range).

Excel 365 / 2021 only. FILTER, SORT, UNIQUE, SEQUENCE, SORTBY, RANDARRAY all spill by default. A single cell with #N/A at the spill boundary means the spill range is blocked.

Read the deep-dive on Dynamic array

EOMONTH

Returns the last day of the month a specified number of months from a date.

The right tool for month-end calculations, fiscal quarter cut-offs, and any YTD/MTD pattern.

=EOMONTH(TODAY(), 0) → last day of current month

ERROR.TYPE

Returns a number that corresponds to the error type in a cell.

Use it in conditional logic to branch on the specific error type - for example, treat #N/A differently from #REF!.

=ERROR.TYPE(A1) → 7 for #DIV/0!, 2 for #VALUE!

FILTER

Returns an array of rows that match a condition.

The replacement for advanced filter in the UI. Spills to a dynamic range - wrap in SORT or UNIQUE for sorted, deduplicated output.

=FILTER(Sales, Region="Mumbai")

FIND

Returns the position of a substring within a string (case-sensitive).

Companion to SEARCH (case-insensitive). Use FIND when you need case sensitivity - for example, matching the literal "GST" not "gst".

=FIND("Excel", "Excel rocks") → 1

FORECAST.ETS

Time-series forecasting with seasonality using exponential smoothing.

Excel 2016+. Needs at least two full seasonal cycles (24 months for monthly). Returns a point forecast - use FORECAST.ETS.CONFINT for the 95% confidence interval.

Read the deep-dive on FORECAST.ETS

FORECAST.LINEAR

Linear trend forecasting - fits a straight line to historical data.

Use it for annual series or any series without a seasonal cycle. For seasonal data, use FORECAST.ETS instead.

=FORECAST.LINEAR(target_x, known_ys, known_xs)

Formula bar

The textbox above the grid showing the live formula in the active cell.

Click any total on a summary page and read the formula bar. If the value is hard-typed instead of a formula, the report can lie without warning.

FV

Future value of a series of payments.

The companion to PV. Use FV for savings goals (how much will my monthly deposit be worth at retirement?).

=FV(0.07/12, 12*30, -1000)

Goal Seek

Finds the input value that makes a formula return a target value.

Data → What-If Analysis → Goal Seek. The single-input version of Solver. Good for break-even analysis.

Read the deep-dive on Goal Seek

HLOOKUP

Horizontal lookup - searches the first row, returns from a row below.

Same age and same limitations as VLOOKUP. Use it only for legacy workbooks; XLOOKUP is the modern replacement.

IFERROR

Wraps a formula and returns a fallback if it produces an error.

Use sparingly. Catching errors with IFERROR hides the bug - fix the bug instead. The exception: VLOOKUP/XLOOKUP misses, where IFERROR("Not found") is fine.

Read the deep-dive on IFERROR

IFNA

Wraps a formula and returns a fallback only for #N/A errors.

More specific than IFERROR. Use it for lookups where #N/A is the only expected error - any other error is a real bug and should not be silently swallowed.

=IFNA(XLOOKUP(...), "Not found")

IFS

Evaluates a list of conditions and returns the value for the first one that is true.

Modern replacement for nested IF. The TRUE default at the end handles the no-match case.

Read the deep-dive on IFS

INDIRECT

Converts a text string into a cell reference.

Volatile and dangerous - INDIRECT does not respond to row inserts and breaks column references. Use it for dependent drop-downs only as a last resort.

INDEX / MATCH

Two-function combo that does the same job as XLOOKUP - left-lookups, two-way lookups.

Works in every Excel since 2007. Pick it when the audience is on Excel 2016 or older. Otherwise XLOOKUP is shorter and clearer.

Read the deep-dive on INDEX / MATCH

IPMT

Interest portion of a loan payment in a specific period.

Companion to PMT and PPMT. Use it to build an amortisation table - the interest column is IPMT, the principal column is PPMT.

=IPMT(0.05/12, 1, 360, 500000)

IRR

Internal rate of return - the discount rate at which NPV equals zero.

Requires at least one sign change in the cash flow series. For irregular dates, use XIRR.

Read the deep-dive on IRR

JOIN (Power Query)

Merges two tables in Power Query on one or more key columns.

Six join types: Left Outer, Right Outer, Full Outer, Inner, Left Anti, Right Anti. The right tool for every multi-source consolidation.

LAMBDA

Define a reusable, named function inside Excel itself.

Excel 365 only. Lets you write =MyFunc(args) the same way =SUM() works, but with your own logic. Power-user feature - most teams never need it.

Read the deep-dive on LAMBDA

LARGE

Returns the kth largest value in a range.

Companion to SMALL. Use for top-N analysis - top 10 customers, top 5 products, top 3 expense lines.

=LARGE(amounts, 5) → 5th largest value

LEFT

Returns the leftmost N characters of a string.

Companion to MID and RIGHT. For variable-width extraction (e.g. extract the first word), use FIND + LEFT.

=LEFT("Excel rocks", 5) → "Excel"

LEN

Returns the number of characters in a string.

The basis for any text-length validation. Use it inside data validation rules: =LEN(A1)=10 for a 10-digit phone number.

=LEN("Excel") → 5

LET

Assigns a name to a sub-expression inside a single formula.

Improves readability and speed. The named values only compute once, even if referenced multiple times. Useful for long XLOOKUP or LAMBDA chains.

Read the deep-dive on LET

M code

The formula language behind Power Query.

Every transformation step in Power Query is M. The Advanced Editor exposes the full code. Documented M in a workbook is auditable in a way the click-only UI is not.

MAXIFS

Returns the maximum value among cells that match multiple criteria.

Companion to MINIFS, SUMIFS, COUNTIFS. Available in Excel 2019+ and Excel 365.

=MAXIFS(amounts, region, "North", year, 2026)

MID

Returns N characters from the middle of a string.

Companion to LEFT and RIGHT. For substring extraction between two delimiters, use FIND + MID + LEN.

=MID("Excel rocks", 7, 5) → "rocks"

Named range

A cell, range, formula, or constant that has been assigned a human-readable name.

Use named ranges for assumptions, constants, and lookup tables. The single most important readability feature in any Excel workbook.

Read the deep-dive on Named range

NORM.INV

Returns the inverse of the normal cumulative distribution for a specified mean and standard deviation.

The right tool for Monte Carlo simulation of normally-distributed inputs (most financial variables).

=NORM.INV(RAND(), mean, std_dev)

NPV

Net present value of a series of cash flows at a constant discount rate.

Assumes equal spacing between cash flows. For irregular dates, use XNPV. Remember to add the initial investment separately - NPV discounts by one period.

Read the deep-dive on NPV

OFFSET

Returns a reference offset from a starting cell by a number of rows and columns.

Volatile and slow. Avoid it for dynamic ranges - use a Table (Ctrl+T) instead. The only legitimate use is in some chart source definitions.

OFFSET (legacy)

A pre-Table way to define a growing range.

The classic OFFSET+COUNTA dynamic range pattern is now obsolete - use a Table (Ctrl+T) and structured references. The Table grows automatically; the OFFSET pattern breaks on every other interaction.

M code

The formula language behind Power Query.

Every transformation step in Power Query is M. The Advanced Editor exposes the full code. Documented M in a workbook is auditable in a way the click-only UI is not.

Pivot table

A summary table that groups and totals rows by any field you drag into it - no formulas needed.

The fastest way to answer 'total by month, by region' questions. Refresh it after the source data changes.

Read the deep-dive on Pivot table

PERCENTRANK

Returns the rank of a value as a percentage of the dataset.

The basis for RFM scoring. Returns 0-1; multiply by 4 and round up to get 1-5 buckets.

=PERCENTRANK($B$2:$B$N, B2, 4)

PMT

Periodic payment of a loan or annuity.

Negative if you are paying (lender side), positive if you are receiving (borrower side). The cornerstone of any loan amortisation table.

Read the deep-dive on PMT

Power Pivot

Excel's in-memory data model. Lets one workbook hold 10M+ rows across related tables.

Same engine as Power BI. Add via the COM Add-in if not visible. The most powerful feature in Excel - the one nobody uses.

Read the deep-dive on Power Pivot

Power Query

Refreshable data import + transformation engine inside Excel.

Replaces the copy-paste loop. Multi-source consolidation, validation checks, documented M code. The single biggest reliability improvement you can make to a workbook.

Read the deep-dive on Power Query

PPMT

Principal portion of a loan payment in a specific period.

Companion to IPMT. The two together build a full amortisation table.

=PPMT(0.05/12, 1, 360, 500000)

PROPER

Capitalizes the first letter of each word in a string.

Part of the standard clean-text pattern: =TRIM(PROPER(SUBSTITUTE(A1, " ", " "))). Use on every imported name field.

PV

Present value of a series of payments.

Companion to FV. The cornerstone of any DCF valuation.

=PV(0.05, 10, -1000)

QUARTILE

Returns the value at a given quartile of a dataset.

The basis for any quartile-based bucketing. QUARTILE.INC and QUARTILE.EXC are the two variants.

=QUARTILE($B$2:$B$N, 1) → 25th percentile

QUERY (Power Query)

A named, refreshable transformation in Power Query.

Each query has a source, a series of transformation steps, and a load destination. Right-click → Advanced Editor to see the M code.

Range

A block of cells, written as its top-left and bottom-right corners - for example A1:D20.

Formulas work on ranges. Convert important ranges to Tables so they grow automatically when data is added.

Refresh

Re-running a query, pivot table or data connection so it picks up the latest source data.

Data > Refresh All updates every Power Query query and pivot table in the workbook in one click.

RAND

Returns a random number between 0 and 1.

The basis for Monte Carlo simulation. Wrap in NORM.INV, TRIANG.INV, or BETA.INV to sample from a specific distribution.

=NORM.INV(RAND(), mean, std_dev)

RATE

Periodic interest rate of an annuity.

Companion to PMT, FV, PV. Use it when the rate is not stated explicitly - common in lease analysis.

=RATE(360, -1500, 500000)

REGEXMATCH

Tests whether a string matches a regular expression pattern.

Excel 365. Returns TRUE/FALSE. Use it for validations that would otherwise need 5+ SUBSTITUTE chains - valid email, valid phone, valid PAN.

REPLACE

Replaces characters in a string based on position and length.

Companion to SUBSTITUTE. Use REPLACE when the position is known; use SUBSTITUTE when the text is known.

=REPLACE("123-456", 1, 3, "***") → "***-456"

RIGHT

Returns the rightmost N characters of a string.

Companion to LEFT and MID.

=RIGHT("Excel rocks", 5) → "rocks"

ROUND

Rounds a number to a specified number of digits.

Use ROUND for financial calculations where partial rupees matter. The companion functions are ROUNDUP, ROUNDDOWN, and MROUND.

=ROUND(A1, 0) → nearest rupee

ROW

Returns the row number of a reference.

Useful in array formulas and in dynamic ranges. ROW(A1) returns 1, ROW(A2) returns 2, etc.

RSQ

Returns the R-squared (goodness of fit) of a linear regression.

Always report RSQ alongside the slope. A regression without RSQ is a half-answer.

Read the deep-dive on RSQ

Spill

When one formula returns several values and fills the neighbouring cells automatically.

Dynamic array functions such as FILTER, SORT and UNIQUE spill. #SPILL! means something is blocking the cells it needs.

Scenario Manager

Saves and applies named sets of input values for what-if analysis.

Data → What-If Analysis → Scenario Manager. Use it for discrete scenarios (Base, Best, Worst). For continuous sensitivity, use Data Tables instead.

Read the deep-dive on Scenario Manager

Slicer

Visual filter that works on Pivot Tables, Pivot Charts, and Excel Tables.

The single biggest unlock for interactive Excel dashboards. Insert a Slicer, connect to multiple pivots, and the dashboard becomes click-driven.

SLOPE

Returns the slope of the linear regression line through a dataset.

Companion to INTERCEPT. The two together describe the line. Always report RSQ alongside the slope.

Read the deep-dive on SLOPE

SORT

Returns a sorted array of values (dynamic array).

Excel 365. Spills to a dynamic range. Combine with UNIQUE and FILTER for sorted, deduplicated, filtered output.

=SORT(FILTER(Sales, Region="Mumbai"), 3, -1)

Solver

Optimisation add-in for linear, integer, and nonlinear programming.

Data → Solver (after enabling the add-in). Set the objective, the variables, the constraints. Solver finds the optimal values.

Read the deep-dive on Solver

Sparkline

A tiny chart that fits inside a single cell.

Insert → Sparklines. Three types: line, column, win/loss. The right tool for KPI tiles and trend columns.

Read the deep-dive on Sparkline

SUBSTITUTE

Replaces specific text in a string with new text.

Use it for cleaning messy text. The standard pattern: TRIM + PROPER + SUBSTITUTE on every imported field.

=SUBSTITUTE("Excel  rocks", "  ", " ") → "Excel rocks"

SUMIFS

Sums cells that match multiple AND conditions.

Best tool for simple AND-sums across one or two columns. For OR-conditions, weighted averages, or counts across many columns, SUMPRODUCT is shorter.

Read the deep-dive on SUMIFS

SUMPRODUCT

Multiplies arrays, then sums the result.

The cleanest way to do OR-conditions, weighted averages, multi-column conditional counts, and conditional max/min. Replaces SUMIFS, COUNTIFS, AVERAGEIF and MAXIFS in one go.

Read the deep-dive on SUMPRODUCT

SWITCH

Evaluates an expression against a list of values and returns the matching result.

Use SWITCH when the same expression is being compared. Use IFS when the conditions are different comparisons.

Read the deep-dive on SWITCH

Table (Ctrl+T)

A named, growing range that holds a single source of truth.

Press Ctrl+T on any data range. Every pivot, SUMIFS, dashboard, Power Query output points at the Table. New rows are picked up automatically. The single biggest reliability improvement to a workbook.

Read the deep-dive on Table (Ctrl+T)

TEXT

Formats a value as a text string using a specified format.

Always wrap dates and currencies in TEXT when concatenating into a label.

="Date: " & TEXT(A1, "dd-mmm-yyyy")

TEXTJOIN

Joins a range of cells with a delimiter, with an option to ignore empty cells.

The right tool for building CSV strings, address lines, and any other text that combines many cells with a separator.

=TEXTJOIN(", ", TRUE, A1:A1000)

Timeline

A visual date-range filter for Pivot Tables and Pivot Charts.

Insert → Timeline. The cleanest way to filter a pivot chart by date - bigger than a Slicer and shows the date range.

TRIANG.INV

Returns the inverse of the cumulative triangular distribution.

Used in Monte Carlo simulation for inputs with a min, most likely, and max - common for expert estimates.

=TRIANG.INV(RAND(), 0, 100, 70)

TRIM

Removes leading, trailing, and extra internal spaces from a string.

Part of the standard clean-text pattern: =TRIM(PROPER(SUBSTITUTE(A1, " ", " "))). Use on every imported text field.

UNIQUE

Returns an array of unique values from a range (dynamic array).

Excel 365. Spills to a dynamic range. Use it to build a list of distinct customers, products, regions, etc.

=UNIQUE(A2:A1000)

Table (Ctrl+T)

A named, growing range that holds a single source of truth.

Press Ctrl+T on any data range. Every pivot, SUMIFS, dashboard, Power Query output points at the Table. New rows are picked up automatically. The single biggest reliability improvement to a workbook.

VALUE

Converts a text string that represents a number into a number.

Use it after importing text-formatted numbers, or to clean up data that has been corrupted by a text-format source.

=VALUE("123.45") → 123.45

VLOOKUP

Vertical lookup - searches the first column, returns a value from a column to the right.

39 years old. Still works. Always looks right, hard-codes a column number, defaults to approximate match, returns #N/A on a miss. Switch to XLOOKUP.

Read the deep-dive on VLOOKUP

Workbook

An Excel file (.xlsx). It contains one or more worksheets.

A well-built workbook separates inputs, calculations and outputs onto different sheets.

Worksheet

A single tab inside a workbook - one grid of rows and columns.

Name sheets for what they hold (Inputs, Data, Report) rather than Sheet1, Sheet2.

What-if analysis

A family of tools for exploring how outputs change as inputs change.

Three built-in tools: Goal Seek (one input, one target), Data Table (one or two inputs, full grid of outputs), Scenario Manager (discrete named scenarios).

Read the deep-dive on What-if analysis

XIRR

Internal rate of return with actual dates (not equal spacing).

Companion to XNPV. The right tool for deal IRR, investment IRR, and any cash flow series with irregular timing.

Read the deep-dive on XIRR

XLOOKUP

Modern replacement for VLOOKUP - searches any column, returns from any column.

Excel 365 / 2021 / for the web. Any direction, exact match by default, built-in not-found value, survives inserted columns. The right default for any new workbook.

Read the deep-dive on XLOOKUP

XMATCH

Modern replacement for MATCH - searches a range and returns the position.

Excel 365. Companion to XLOOKUP. Use XMATCH on its own when you want the position (e.g. for INDEX), not the value.

=XMATCH(lookup_value, lookup_range)

XNPV

Net present value with actual dates.

Companion to NPV. Use XNPV for any cash flow series with irregular timing - capital calls, milestone payments, drawdowns.

Read the deep-dive on XNPV

YEAR

Returns the year from a date.

Companion to MONTH and DAY. Use for year-based filtering and grouping.

=YEAR(TODAY()) → current year

YEARFRAC

Returns the fraction of the year between two dates.

The right tool for interest accrual, depreciation, and any calculation that needs to weight by time.

=YEARFRAC(start_date, end_date, basis)

Z.TEST

Returns the one-tailed probability of a z-test.

Statistical hypothesis testing. Use it when you have a large sample and a known population mean and standard deviation.