Excel & MOS Certification Glossary

A cell reference locked with dollar signs ($A$1) so it does not change when the formula is copied to other cells — as opposed to a relative reference (A1), which shifts with the copy.

A cell or range given a readable name (SalesTotal instead of $B$2:$B$50) so formulas can reference it by name, which is easier to read and does not break if rows are inserted above it.

A cell reference (A1, with no dollar signs) that shifts to match its new position when a formula is copied elsewhere — the default behavior, and the opposite of an absolute reference.

A formula reference that uses an Excel Table's column names instead of cell addresses, e.g. Table1[Revenue] instead of C2:C50. It updates automatically as the table grows or shrinks.

A formula that automatically "spills" its results across as many cells as needed, rather than requiring you to copy it down or across. SORT, UNIQUE, FILTER, SORTBY, and RANDARRAY are all dynamic-array functions.

A dynamic-array function that returns only the rows of a range matching a condition you specify, spilling the matches across as many cells as needed.

IFS() evaluates multiple conditions in order without nesting IF() inside IF(). SWITCH() matches one value against several possible results. Both replace deeply nested IF() statements with something easier to read.

A two-function combination used as a lookup: MATCH() finds a value's position in a range, and INDEX() returns the value at that position from another range. More flexible than VLOOKUP because it can look in either direction.

A function that lets you name a value or a calculation inside a formula and reuse that name later in the same formula, reducing repetition in long formulas. The name only exists within that one formula.

A dynamic-array function that returns a sorted version of a range without changing the original data or requiring a manual Sort command.

A dynamic-array function that sorts one range by the values in another — for example, sorting a list of products by a separate column of sales figures.

Functions that calculate across multiple criteria at once (unlike SUMIF/COUNTIF/AVERAGEIF, which take only one). Their argument order differs from the single-criteria versions — the sum or average range moves from last to first.

A dynamic-array function that returns the distinct values from a range, with duplicates removed automatically.

A classic lookup function that searches the first column of a range for a value and returns a result from a column to its right. Forgetting the fourth argument (range_lookup) is the most common source of a silently wrong answer.

Microsoft's newer, more flexible lookup function. Unlike VLOOKUP, it can look to the left as well as the right, has a built-in not-found argument, and does not require counting columns.

Formatting (color, icons, data bars) that applies automatically based on a cell's value or a formula you write. MO-210 tests the built-in rules; MO-211 goes further into custom and formula-driven rules.

A tiny chart that fits inside a single cell, showing a data trend at a glance without the space a full chart requires.

A named, structured range created with Ctrl+T, with built-in filtering, sorting, banded formatting, and support for structured references — distinct from a plain range of cells that merely looks like a table.

A chart built directly from a PivotTable, so it stays in sync with the PivotTable's grouping and filters as they change.

An interactive summary tool that lets you group, aggregate, and rearrange large amounts of data without writing formulas — and refresh the summary when the source data changes, unlike a one-time Consolidate.

A clickable filter panel for a PivotTable (or Table), letting you filter by clicking buttons instead of using the dropdown filter arrows.

A feature that detects a pattern in the data you have typed (like splitting a full name into first and last) and fills the rest of the column automatically once it recognizes what you are doing.

A recorded sequence of Excel actions that can be replayed with one click. MO-211 expects you to record, name, and make simple edits to a macro — not write VBA from scratch.

A rule attached to a cell or range that restricts what can be entered — a dropdown list, a number range, a date range — and can show a warning when the rule is broken.

A feature that summarizes data from multiple ranges or worksheets into one, using a function like SUM or AVERAGE. A one-time calculation — unlike a PivotTable, it does not stay interactive or refresh automatically.

A what-if tool that works backward: tell it the result you want a formula to produce, and it finds the input value that gets you there. Found under Data > What-If Analysis.

A what-if tool that saves multiple sets of input values as named scenarios, so you can switch between them and compare the resulting outcomes side by side.

A number-display pattern you define yourself (beyond the built-in Currency, Percentage, etc. options) using placeholder codes, so a value can display as, for example, "1.2k" or with a specific date style without changing the underlying number.

A tool that copies all formatting (font, color, borders, number format) from one cell or range and applies it to another, without copying the underlying data.