Excel glossary: 108 terms in plain English

Not an error: the column is too narrow to show the number or date. Widen the column. A negative date or time also shows as #####.

Why Excel shows ##### → · Keyboard shortcut

Tells a formula to use one value instead of a whole range. Excel 365 adds it to some older formulas, such as =@A2:A10, to keep their old single-value result.

The @ and # operators →

A calculation Excel cannot finish, most often FILTER finding nothing to return or an array that ends up empty. Give FILTER its third argument, such as "None", to show instead.

Fix the #CALC! error →

A formula divided by zero or by an empty cell, which counts as zero. Test the divisor first, for example =IF(B2=0,"",A2/B2).

=C2/0 → #DIV/0!

Fix the #DIV/0! error →

Value not available. Lookups such as VLOOKUP, XLOOKUP and MATCH return it when they cannot find what you asked for.

=MATCH("Lamp",A2:A5,0) → #N/A

Fix the #N/A error →

Excel does not recognise a name in the formula: a misspelled function, text without quotes, or a function your version does not have.

Fix the #NAME? error →

Two ranges were joined with a space, the intersection operator, but they do not overlap. It usually means a comma or colon is missing.

Fix the #NULL! error →

A number is invalid for the calculation, such as the square root of a negative number, or the result is too large for Excel to hold.

=SQRT(-C2) → #NUM!

Fix the #NUM! error →

A formula points at a cell that no longer exists, usually because a row, column or sheet it used was deleted.

Fix the #REF! error →

A formula that returns several values cannot spill them, because cells in the way are not empty or are merged. Clear the cells it needs.

Fix the #SPILL! error →

A value is the wrong type for the operation, such as text in a sum with +. Functions like SUM skip text instead of failing.

=A2+B2 → #VALUE!

Fix the #VALUE! error →

A cell reference with $ signs, such as $C$2, that stays fixed when you copy the formula. Use it for a rate, a total or a lookup table every row needs.

=B3*$C$2 → 78

Absolute references explained → · Practice: Absolute references lesson · Keyboard shortcut

The selected cell, the one with the thick border. What you type goes into it, and its address shows in the Name Box.

Unit 1: Cells and formulas → · Practice: Meet the grid lesson · Keyboard shortcut

Every condition must be true. AND(test1, test2) returns TRUE only when all tests pass, and the S functions such as COUNTIFS combine their conditions the same way.

=AND(B2>=4,D2="North") → TRUE

IF with AND and OR → · Practice: AND and OR lesson

A lookup that returns the closest value at or below the one you asked for, from a list sorted in ascending order. It is how tax brackets, grade bands and shipping tiers are looked up.

Look up a value in a range of numbers → · Practice: VLOOKUP lesson

A value you give a function, inside its brackets and separated by commas. In ROUND(C4,0), C4 is the number and 0 the number of decimals.

=ROUND(C4,0) → 13

Excel formulas cheat sheet →

A set of values in rows and columns that a formula works on at once, such as B2:B5*C2:C5, which multiplies each pair.

=B2:B5*C2:C5 → spills 156, 178, 75, 45

Unit 12: Dynamic arrays → · Practice: Spill basics lesson

A list typed straight into a formula in curly braces: {1,2,3} is a row and {1;2;3} a column. Handy for several criteria at once.

=SUM(COUNTIF(D2:D5,{"North","East"})) → 3

COUNTIF with multiple values →

A formula that calculates on whole arrays. In Excel 2019 and earlier you confirm it with Ctrl+Shift+Enter and it shows {braces}; in Excel 365 any formula can do this and you just press Enter.

SUMPRODUCT, the array formula that needs no CSE → · Keyboard shortcut

Dragging the small square at the corner of a selection to copy a formula or continue a series like Mon, Tue, Wed. Double-click it to fill down to the end of the data.

Auto number rows → · Keyboard shortcut

The Σ button (Alt+= on Windows) that writes a SUM of the numbers above or to the left. Always check the range it guessed before pressing Enter.

When AutoSum picks the wrong range → · Practice: AutoSum and totals lesson · Keyboard shortcut

A cell with nothing in it. A formula that returns "" looks blank but is not empty: ISBLANK says FALSE and COUNTA counts it.

=COUNTBLANK(A2:E5) → 0

Check if a cell is blank → · Practice: Blanks and flags lesson

TRUE or FALSE, the result of a comparison. In math TRUE counts as 1 and FALSE as 0, which is why --(test) turns tests into numbers you can add.

=B2>3 → TRUE

Comparison operators →

Whether Excel recalculates formulas as soon as something changes (Automatic) or only when you press F9 (Manual). A workbook left on Manual is the classic "formula not updating" cause.

Formula not calculating → · Keyboard shortcut

One box of the grid, where a column and a row cross. It holds one value or one formula.

Unit 1: Cells and formulas → · Practice: Meet the grid lesson

The column letter and row number that name a cell, such as C3. A formula that uses C3 always reads whatever is currently in C3.

=C3 → 89

Unit 3: Cell references → · Practice: Meet the grid lesson

A formula that depends on its own cell, directly or through other cells, such as a total that includes itself. Excel warns you, and the cell often shows 0 until you break the loop.

Find and fix a circular reference →

The symbols that compare two values and return TRUE or FALSE: = equal, <> not equal, >, <, >= and <=. Text comparisons ignore upper and lower case.

=D2="north" → TRUE

Comparison operators →

Joining values into one piece of text with the & operator or CONCAT and TEXTJOIN. Numbers lose their formatting when joined unless you wrap them in TEXT.

=A2&" x"&B2 → Desk lamp x4

Concatenate with a space → · Practice: Joining text lesson

Formatting that changes with the value, such as red fill for overdue dates or a color scale on sales. A formula rule can format a whole row based on one cell.

Highlight a row based on a cell value →

The condition a function such as COUNTIF, SUMIFS or AVERAGEIF tests each cell against: a value, a comparison in quotes like ">=4", or a wildcard pattern.

=COUNTIF(D2:D5,"North") → 2

COUNTIFS with multiple criteria → · Practice: COUNTIF lesson

The range a conditional function checks against the criteria. In SUMIFS every criteria range must be the same size as the sum range.

=SUMIF(D2:D5,"North",B2:B5) → 10

SUMIFS not working → · Practice: SUMIF and AVERAGEIF lesson

A format code you write yourself in Format Cells > Custom, such as #,##0.0,,"M" to show 1,250,000 as 1.3M. It changes how a value looks, never the value.

Custom number format codes →

Rules about what can be typed into a cell: a drop-down list, whole numbers only, dates in a range, or a custom formula. Data > Data Validation.

Data validation with a custom formula →

How Excel stores a date: a count of days since January 1, 1900 (day 1). Formatting makes it look like a date, which is why you can subtract one date from another.

=E3-E2 → 1

Convert text to a date → · Practice: Date basics lesson

The character that separates parts of text, such as the comma in "Austin, TX" or the @ in an email address. TEXTSPLIT, TEXTBEFORE and Text to Columns split at it.

Split text by a delimiter →

A second drop-down whose choices depend on the first, such as cities that change with the chosen state. Built with data validation and INDIRECT or FILTER.

Make a dependent drop-down list →

A cell that offers a list to pick from, made with Data > Data Validation > List. It keeps entries consistent, which keeps COUNTIF and pivot tables accurate.

Create a drop-down list →

A value that appears more than once in a list. You can highlight them, count them with COUNTIF, or remove them with Data > Remove Duplicates or UNIQUE.

=COUNTIF(D2:D5,D2)>1 → TRUE

Find duplicates →

A formula result that holds several values and spills into the cells next to it, introduced in Excel 365 and 2021. FILTER, SORT, UNIQUE and SEQUENCE all return one.

=SORT(C2:C5) → spills 12.5, 39, 45, 89

Unit 12: Dynamic arrays → · Practice: Spill basics lesson

What a cell shows when a formula cannot produce a result, such as #N/A, #VALUE! or #DIV/0!. Each name tells you what kind of problem it is.

Handle errors with IFERROR → · Practice: Reading errors lesson

A tool that steps through a formula one calculation at a time, so you can see where it goes wrong. Formulas > Evaluate Formula.

Debug a #VALUE! error →

A lookup that only accepts the same value: FALSE at the end of VLOOKUP, 0 in MATCH, and the default in XLOOKUP. Use it for IDs, names and codes.

=VLOOKUP("Footrest",A2:C5,3,FALSE) → 45

VLOOKUP not working → · Practice: VLOOKUP lesson

Data > Filter adds drop-down arrows to headers so you can hide rows that do not match. It hides rows; the FILTER function instead copies the matching rows somewhere else.

=FILTER(A2:A5,D2:D5="North") → spills Desk lamp, Cable tray

FILTER with multiple criteria → · Keyboard shortcut

Excel fills a column by copying a pattern you type once or twice, such as first names pulled from full names (Data > Flash Fill). The results are typed values, so they do not update when the source changes.

Separate first and last names → · Keyboard shortcut

Tiny rounding differences from how computers store decimals, so a sum that should be 0 can come out as a very small number. ROUND the result before comparing it.

ROUND vs ROUNDUP vs ROUNDDOWN →

A calculation in a cell, starting with =. It can use numbers, cell references, operators and functions, and recalculates when its inputs change.

=B2*C2 → 156

Unit 1: Cells and formulas → · Practice: Basic arithmetic lesson

The bar above the grid that shows what is really in the active cell: the formula, not its result. Click into it to edit a long formula.

Formula showing as text → · Practice: Meet the grid lesson · Keyboard shortcut

Keeps header rows or first columns in view while you scroll (View > Freeze Panes). Freeze Top Row is the usual choice for a list.

Freeze panes →

A named, ready-made calculation such as SUM, IF or XLOOKUP. You give it arguments in brackets and it returns a result.

=SUM(B2:B5) → 13

All Excel functions → · Practice: SUM lesson

A What-If Analysis tool that finds the input value that makes a formula hit a target, such as the price needed to break even. Data > What-If Analysis > Goal Seek.

Calculate a break-even point →

The first row of a list, with one label per column. Sorting, filtering, tables and pivot tables all rely on it, so keep it to one row with no blanks.

Create a pivot table →

An extra column that holds an in-between result, such as a cleaned name or a combined key, so the main formula stays simple. You can hide it afterwards.

XLOOKUP with multiple criteria →

How older Excel turned a range into one value: a formula expecting one value used the cell in its own row or column. Excel 365 shows this behaviour with the @ operator.

The @ and # operators →

A cell that holds a number other formulas use, such as a tax rate or a growth rate, instead of typing the number into each formula. Change it once and everything updates.

=B2*C2*(1+$F$1) → 156

Unit 3: Cell references → · Practice: Locking inputs lesson

A space between two ranges means only the cells they share: =B1:B5 A3:D3 is just B3. If they do not overlap the result is #NULL!.

Reference operators →

Zeros at the start of a code, such as 00742. Excel drops them from numbers, so store such codes as text or use a format like 00000.

=TEXT(742,"00000") → 00742

Add leading zeros →

A function that names a part of a formula so you calculate it once and reuse it, which makes long formulas shorter and easier to read (Excel 365 and 2021).

=LET(total,SUM(C2:C5),ROUND(C3/total,2)) → 0.48

The LET function →

A new line inside one cell, typed with Alt+Enter (Option+Return on a Mac) or added in a formula with CHAR(10). Wrap Text must be on for it to show.

Join text with a line break → · Keyboard shortcut

Stops edits to chosen cells. Every cell is locked by default, but locking only works once you turn on Review > Protect Sheet.

Lock cells with formulas →

A comparison that comes out TRUE or FALSE, used as the first argument of IF: if the test is true, IF returns its second argument, otherwise its third.

=IF(B2>=4,"Bulk","Single") → Bulk

The IF function → · Practice: IF basics lesson

Finding a value in one column and returning the matching value from another, such as a price for a product name. XLOOKUP, VLOOKUP and INDEX with MATCH do this.

=XLOOKUP("Cable tray",A2:A5,C2:C5) → 12.5

VLOOKUP vs XLOOKUP → · Practice: VLOOKUP lesson

A recorded or written program that repeats steps for you. Macros are written in VBA and saved in .xlsm files; most everyday tasks no longer need one.

Count colored cells (formula or macro) → · Keyboard shortcut

Several cells joined into one. They break sorting, filtering, copying and spilled formulas, so Center Across Selection is often the better way to center a title.

Sort by multiple columns → · Keyboard shortcut

A reference with only the column or only the row locked. $A2 keeps column A when you fill right; A$1 keeps row 1 when you fill down. It is what makes one formula fill a whole grid.

Absolute and mixed references → · Practice: Mixed references lesson · Keyboard shortcut

The box left of the formula bar that shows the active cell’s address. Type an address in it to jump there, or type a name to create a named range for the selection.

References and the Name Box →

A name such as TaxRate that stands for a cell or range, so formulas read =B2*TaxRate instead of =B2*$F$1. Manage them in Formulas > Name Manager.

Named ranges in a dependent drop-down →

A function used as an argument of another function, like ROUND around AVERAGE. Excel works from the inside out.

=ROUND(AVERAGE(C2:C5),1) → 46.4

Nested IF formulas → · Practice: Nested decisions lesson

A space character, code 160, that arrives with data copied from web pages. It looks like a normal space but TRIM does not remove it and lookups treat it as different.

Find it with CODE →

How a value is displayed: currency, percent, date, decimals and so on (Ctrl+1 opens Format Cells). Formatting never changes the stored value, so 0.125 shown as 13% is still 0.125.

=TEXT(0.125,"0%") → 13%

Number format codes → · Keyboard shortcut

A number Excel treats as text, often from an import or a leading apostrophe. It sits on the left of the cell, gets a green triangle, and SUM ignores it.

=ISNUMBER("42") → FALSE

Fix numbers stored as text →

A symbol that tells a formula what to do: + - * / ^ for math, & to join text, = <> > < for comparisons, and : , and space for references.

=B2*C2+5 → 161

Excel operators and order of operations → · Practice: Basic arithmetic lesson

The order Excel calculates in: negation, then %, then ^, then * and /, then + and -, then &, then comparisons. Brackets override it.

=1+2*3 → 7

Order of operations in Excel → · Practice: Order and errors lesson

At least one condition must be true. OR(test1, test2) returns TRUE when any test passes; with COUNTIFS or SUMIFS you add one function per option instead.

=OR(D3="North",D3="East") → FALSE

SUMIF with OR criteria → · Practice: AND and OR lesson

Paste only part of what you copied: values without formulas, formats only, or a calculation such as multiply. Paste Values is how you freeze formula results.

Paste Special to fix text numbers → · Keyboard shortcut

A number shown out of 100. Excel stores 25% as 0.25, so a percent cell times a number gives that share, and giving a cell that already holds 25 a percent format shows 2500%.

=C2*25% → 9.75

Percentage format showing wrong → · Practice: Ranges and percents lesson

A summary table you build by dragging fields: totals by region, counts by month, averages by product, with no formulas. Insert > PivotTable, and refresh it after the data changes.

Create a pivot table → · Keyboard shortcut

Excel’s tool for importing and cleaning data with recorded steps (Data > Get Data). You set up the clean-up once, then refresh it each time new data arrives.

Remove blank rows (Power Query option) → · Keyboard shortcut

Precedents are the cells a formula reads; dependents are the formulas that read a cell. Formulas > Trace Precedents and Trace Dependents draw arrows between them.

Tracing a circular reference → · Keyboard shortcut

An alternative way of writing references by row and column number, such as R2C3 for C2, or R[-1]C for the cell above. Turn it off in File > Options > Formulas if your columns show numbers instead of letters.

Unit 3: Cell references →

A block of cells written as the first and last cell with a colon, such as B2:B5 or A1:C10. Most functions take ranges as arguments.

=SUM(B2:B5) → 13

Reference operators → · Practice: Ranges and percents lesson

The colon between two cells that means every cell from one to the other: A1:C3 is nine cells.

=COUNT(B2:C5) → 8

Reference operators →

A normal reference such as C2, which shifts when you copy the formula: copied one row down it becomes C3. That is what lets one formula fill a column.

Unit 3: Cell references → · Practice: Relative references lesson · Keyboard shortcut

Data > Remove Duplicates deletes repeated rows in place, keeping the first one. It changes your data, so work on a copy or use UNIQUE for a formula version.

Remove duplicates →

The tabbed toolbar across the top of Excel (Home, Insert, Formulas, Data...). Menu paths such as Data > Remove Duplicates name a ribbon tab and then a button.

Excel shortcuts for beginners → · Keyboard shortcut

A total that grows down a column: each row adds its value to everything above. SUM with a locked start, $B$2:B2, filled down.

=SUM($B$2:B4) → 12

Running total → · Practice: Running totals lesson

Reorder rows by one or more columns (Data > Sort). Select the whole table first so each row stays together; the SORT function returns a sorted copy instead.

=SORT(A2:A5) → spills Cable tray, Desk lamp, Footrest, Monitor arm

Sort by multiple columns → · Keyboard shortcut

The cells a dynamic array formula fills. Only the top-left cell holds the formula; the other cells show its formula greyed out in the formula bar, and typing into them blocks the spill.

Spill ranges and #SPILL! → · Practice: Spill basics lesson

A # after the first cell of a spill, such as F2#, means the whole spill range, however long it grows. Other formulas can use it, such as =COUNTA(F2#).

The @ and # operators →

The strip at the bottom of the window. Select some numbers and it shows their sum, average and count without a formula; right-click it to choose which.

Sum a column →

A reference to an Excel Table by names instead of addresses, such as Sales[Amount] for a column. It grows with the table, so formulas never miss new rows.

SUMIFS with table references →

A function that totals only the rows a filter leaves visible, such as SUBTOTAL(9,range) for a sum. Use 109 to also skip rows hidden by hand.

Sum visible cells only → · Keyboard shortcut

A range turned into a Table with Ctrl+T: it gets banded rows, filter buttons, structured references, and formulas that fill down automatically as you add rows.

Banded rows with a Table → · Keyboard shortcut

Text in a formula, always inside double quotes, such as "North". Without quotes Excel reads it as a name and shows #NAME?.

="Region: "&D2 → Region: North

The & operator → · Practice: Joining text lesson

Data > Text to Columns splits one column into several at a delimiter or fixed width. It is also a quick way to turn text numbers and text dates into real ones.

Split text by a delimiter →

How Excel stores a time: a fraction of a day, so 6:00 PM is 0.75 and 12 hours is 0.5. Multiply a time by 24 for hours.

=TEXT(0.75,"h:mm AM/PM") → 6:00 PM

Sum time →

Removes spaces at the start and end of text and squeezes double spaces to one. It is the first fix when a lookup fails on values that look identical.

=LEN(TRIM(" Desk lamp ")) → 9

Remove spaces → · Practice: Cleaning text lesson

Finding the value where a row and a column meet, such as March sales for one store. INDEX with two MATCHes or XLOOKUP inside XLOOKUP does it.

Two-way lookup →

In a reference, a comma joins separate ranges into one, so =SUM((A1:A3,C1:C3)) adds both. Inside a function’s brackets the comma is usually just separating arguments.

Reference operators →

Each value once, with repeats removed. UNIQUE returns them as a spilled list (Excel 365 and 2021); COUNTA(UNIQUE(range)) counts them.

=UNIQUE(D2:D5) → spills North, South, East

Make a unique list →

A function that recalculates every time anything in the workbook changes: TODAY, NOW, RAND, RANDBETWEEN, OFFSET and INDIRECT. Many of them can slow a big workbook.

TODAY, a volatile function →

An average where some values count more than others, such as the average price per unit sold. SUMPRODUCT of values and weights, divided by the sum of the weights.

=SUMPRODUCT(B2:B5,C2:C5)/SUM(B2:B5) → 34.923076923

Calculate a weighted score →

A stand-in character in criteria: * matches any number of characters, ? exactly one, and ~ makes the next * or ? literal. COUNTIF, SUMIF, XLOOKUP and MATCH accept them.

=COUNTIF(A2:A5,"*lamp*") → 1

Wildcards in Excel → · Practice: Wildcards and averages lesson

An Excel file (.xlsx). It holds one or more worksheets, each with its own tab along the bottom.

Unit 1: Cells and formulas → · Practice: Meet the grid lesson

One grid inside a workbook. A formula can read another sheet with a reference such as Sheet2!B4, or 'Q1 Sales'!B4 when the name has a space.

Compare columns across sheets → · Keyboard shortcut

Makes long text in a cell continue on new lines instead of spilling over neighbours (Home > Wrap Text). Line breaks made with Alt+Enter or CHAR(10) only show with it on.

CHAR(10) and Wrap Text → · Keyboard shortcut

The modern lookup in Excel 365 and 2021: it searches any column, returns from any column, has a built-in not-found message, and defaults to an exact match.

=XLOOKUP("Footrest",A2:A5,B2:B5,"Not found") → 1

The XLOOKUP function →

Text with no characters, written "". Formulas return it to make a cell look blank, but the cell is not empty: COUNTA counts it and a chart plots it as zero.

=LEN("") → 0

Why NA() suits charts better →