Excel Glossary

Absolute Cell Reference

A reference that stays fixed when a formula is copied. Dollar signs lock both the column and row, for example $A$1. On Windows, pressing F4 while editing a reference cycles through relative, absolute and mixed forms.

=B2*$E$1

Covered in Excel Introduction.

See also: Relative Cell Reference, Mixed Reference.

Active Cell

The cell currently selected for data entry or editing. Its address appears in the Name Box to the left of the formula bar.

Add-in

An extension that adds capabilities to Excel. Examples include Solver, Analysis ToolPak and third-party Office add-ins. Power Query is built into current Microsoft 365 Excel rather than being treated as a separate add-in.

Address (Cell Address)

The coordinates of a cell, formed from its column letter and row number, such as C7.

Array Formula

A formula that works with multiple values at once. Modern Microsoft 365 Excel uses dynamic arrays that can spill results automatically. Older versions also support legacy array formulas entered with Ctrl+Shift+Enter.

=FILTER(A2:D100,D2:D100>=1000)

See also: Dynamic Arrays, Spill Range.

AutoFill / Fill Handle

The small handle on the selected cell used to copy formulas or extend recognised patterns and series. Double-clicking can fill down alongside an adjacent data region.

Bar Chart / Column Chart

Charts used to compare categories. Bar charts display horizontal bars; column charts display vertical columns. Bar charts are useful when category labels are long.

Border

A line applied around one or more cells. Borders can improve readability but should not be confused with worksheet gridlines.

Cell

The intersection of a worksheet row and column. A cell can contain text, a number, a date, a formula, an error value or other supported data.

Cell Reference

A cell or range address used in a formula. References can be relative (A1), absolute ($A$1) or mixed ($A1 or A$1).

Chart

A graphical representation of worksheet data. Excel includes column, bar, line, pie, scatter, histogram, waterfall, map and other chart types.

Guide: How to create and format charts in Excel.

Clipboard / Paste

Excel uses the Office Clipboard for copied and cut content. Paste Special can paste only selected parts such as values, formulas, formatting or column widths.

Conditional Formatting

Automatically applies formatting when specified conditions are met. Rules can use cell values, top/bottom criteria, colour scales, data bars, icon sets or formulas.

=AND($A2="UK",$D2>1000)

Guide: Using Conditional Formatting.

Copilot in Excel

Microsoft’s AI assistance within supported Microsoft 365 Excel experiences. Depending on licence and availability, Copilot can help analyse workbook data, explain or create formulas, edit worksheets and work with Excel objects such as tables, charts and PivotTables.

COUNTIF / COUNTIFS

Count cells or rows that meet one or more criteria.

=COUNTIFS(A:A,"UK",B:B,"Q1")

See also: SUMIF / SUMIFS.

Criteria Range (Advanced Filter)

A separate range containing field headings and criteria used by Advanced Filter to identify records to display or copy.

Dashboard

A worksheet or workbook view that combines key metrics, charts, PivotTables, slicers or other controls to present information at a glance.

Data Type

The kind of value Excel is working with, such as number, text, date/time, logical TRUE/FALSE or an error. Number formatting changes how a value is displayed, not the underlying value itself.

Data Validation

Controls what users are allowed to enter in a cell. Validation rules can restrict numbers, dates, text length or provide a drop-down list, and can display input and error messages.

Guide: Create drop-down lists with Data Validation.

Dependent Drop-down

A validation list whose choices depend on another selection. Traditional solutions often use named ranges and INDIRECT; modern Excel can also use dynamic-array techniques.

DROP

A Microsoft 365 dynamic-array function that removes a specified number of rows or columns from the beginning or end of an array.

Dynamic Arrays

Modern Excel formulas that can return multiple results into neighbouring cells automatically. Common dynamic-array functions include FILTER, SORT, UNIQUE, SEQUENCE, TAKE, DROP, VSTACK and HSTACK.

=SORT(UNIQUE(FILTER(A2:C100,C2:C100>=1000)))

Error Values

Excel displays error codes when a formula cannot return a normal result. Common examples include #DIV/0!, #N/A, #REF!, #VALUE!, #NAME?, #NUM!, #SPILL! and #CALC!. Newer features can introduce additional specialised errors.

IFERROR can provide an alternative result when appropriate, but errors should not be hidden until their cause is understood.

External Links

Formula references to cells or ranges in another workbook. External links can be useful but may become fragile when files move or are renamed. Power Query is often a more maintainable option for repeatable consolidation.

Filter (AutoFilter)

Temporarily hides rows that do not meet selected criteria. Filters can be applied to ordinary ranges and are built into Excel Tables.

Guide: Sort and filter data in Excel.

Find & Replace

Searches for and optionally replaces values, formulas or formatting. Wildcards include * for any sequence of characters and ? for a single character.

Flash Fill

Recognises a pattern from examples and fills the remaining values. Flash Fill produces static results rather than formulas, so it does not automatically recalculate if source data changes.

Form Controls

Worksheet controls such as buttons, check boxes, option buttons, list boxes, combo boxes, scroll bars and spin buttons. They can be linked to cells or macros for interactive models.

Formula

An expression beginning with = that calculates a result. A formula can contain constants, cell references, operators, functions and names.

Function

A predefined calculation used inside a formula, such as SUM, IF, FILTER or XLOOKUP.

Goal Seek

A What-If Analysis tool that changes one input cell until a formula reaches a specified target result.

See also: What-If Analysis.

Group / Ungroup

Creates outline levels so rows or columns can be collapsed and expanded. Useful for reports containing detailed and summary information.

Go To Special

Selects cells with particular characteristics, such as blanks, constants, formulas, errors, visible cells only or conditional formatting.

Headers & Footers

Information printed at the top or bottom of worksheet pages, such as page numbers, dates, worksheet names or file paths.

Histogram

A statistical chart that groups numeric values into intervals (bins) to show their distribution.

HLOOKUP

An older lookup function that searches across the first row of a range and returns a value from a specified row. In supported versions, XLOOKUP is usually more flexible.

HSTACK

A Microsoft 365 dynamic-array function that appends arrays horizontally to create a wider array.

HYPERLINK

Creates a clickable link to a webpage, file, email address or location within a workbook.

IF / IFS

IF returns one result when a test is TRUE and another when it is FALSE. IFS can evaluate several conditions in sequence.

=IF(E2>=Target,"Bonus","No Bonus")

IFERROR

Returns an alternative result when a formula produces an error.

=IFERROR(XLOOKUP(A2,Products[Code],Products[Price]),"Not found")

INDEX

Returns a value from a range or array by position. Often paired with MATCH.

=INDEX(C2:C10,MATCH("Japan",A2:A10,0))

INDIRECT

Creates a reference from text. It is a volatile function, so extensive use can slow large workbooks.

InputBox (VBA)

Displays a prompt so a VBA procedure can receive information from a user.

ans = InputBox("Enter month:")

Justify / Fill Justify

Alignment options used to distribute or reflow text. Fill Justify can spread long text across adjacent cells.

Keyboard Shortcuts

Common examples include Ctrl+C, Ctrl+V, Ctrl+X, Ctrl+; for the current date, Ctrl+Shift+L for filters and Alt for Ribbon KeyTips.

LAMBDA

Creates reusable custom worksheet functions without VBA. A LAMBDA can be stored with Name Manager and called like a built-in function.

LET

Assigns names to intermediate values inside a formula. This can make complex formulas easier to read and can avoid recalculating the same expression repeatedly.

=LET(rate,$E$1,amount,B2,amount*rate)

LEFT / RIGHT / MID

Extract characters from the beginning, end or middle of text.

=LEFT(A2,3)   =RIGHT(A2,4)   =MID(A2,4,2)

LEN

Returns the number of characters in a text value.

=LEN(A2)

Logical Functions

Functions such as AND, OR, NOT, XOR, IF and IFS evaluate logical conditions.

Lookup Functions

Functions used to find matching information include XLOOKUP, VLOOKUP, HLOOKUP, INDEX/MATCH and XMATCH.

MATCH

Returns the relative position of a value in a range. Use match type 0 when an exact match is required.

=MATCH("SKU-9",A2:A100,0)

Macro

An automated sequence of actions, usually recorded or written in VBA. Workbooks containing VBA macros are normally saved as .xlsm.

Basic recording is introduced in Excel Advanced; VBA programming starts with Excel VBA Introduction.

Macro Recorder

Records actions as VBA code. Recorded code is useful for learning and simple automation but often benefits from editing to remove unnecessary selections and hard-coded ranges.

MAXIFS / MINIFS

Return the largest or smallest value that meets one or more conditions.

=MAXIFS(C:C,A:A,"UK",B:B,"Q1")

Merge & Center

Combines cells and centres the displayed content. Merged cells can interfere with sorting, filtering and data manipulation, so use them sparingly.

Mixed Reference

Locks either the column or the row, such as $A1 or A$1. Particularly useful when copying formulas across two-dimensional tables.

Module (VBA)

A container for VBA code. Excel supports standard modules, worksheet/workbook modules and class modules.

Named Range

A meaningful name assigned to a cell, range, constant or formula. Names can make formulas easier to understand.

=SUM(SalesAmount)

Name Manager

Used to create, edit and remove workbook and worksheet-level names.

NETWORKDAYS / WORKDAY

Calculate working days or future working dates while excluding weekends and, optionally, holiday dates.

NOW / TODAY

Volatile functions returning the current date and time or the current date. They recalculate with the workbook.

Office Scripts

Microsoft 365 automation for Excel on the web, Windows and Mac. Actions can be recorded and scripts can be edited using TypeScript. Office Scripts are separate from VBA and are designed for modern cloud-based automation.

OFFSET

Returns a reference shifted from a starting point. OFFSET is volatile; tables, INDEX and dynamic-array techniques are often preferable in performance-sensitive workbooks.

Paste Special

Pastes selected aspects of copied cells, including values, formulas, formats, comments/notes, validation and column widths. It can also transpose data or apply arithmetic operations.

PivotTable

An interactive summary tool for grouping and aggregating large lists of data. Source data is best stored as a properly structured Excel Table.

Covered in Excel Advanced.

PivotChart

A chart connected to a PivotTable. It responds to PivotTable filters and compatible slicers.

Power Pivot / Data Model

Excel's relational data-modelling technology. It can relate multiple tables and supports DAX calculations for more sophisticated analysis.

Covered in Excel Advanced Functions & Data Modelling.

Power Query (Get & Transform)

Excel's built-in data preparation technology for connecting to sources, cleaning and reshaping data, combining datasets and refreshing repeatable transformation steps.

Course: Excel Power Query.

Protect Sheet / Workbook

Restricts changes to worksheet cells or workbook structure. Worksheet protection is not the same as encrypting a file with a password.

Python in Excel

A Microsoft 365 feature that lets supported users write Python calculations directly in the Excel grid. Python executes in the Microsoft Cloud and can work with Excel data for analysis and visualisation. Availability depends on platform and licence.

Quick Access Toolbar (QAT)

A customisable toolbar for frequently used commands. Commands can also receive convenient Alt-key shortcuts.

Power Query Editor

The interface used to inspect and transform data in Power Query. Each transformation is stored as an Applied Step and can be edited or reordered.

Range

One cell or a group of cells, for example A1:D20. Ranges are used as inputs to formulas, formatting commands, charts and many other Excel features.

Relative Cell Reference

A reference such as A1 that changes relative to the destination when a formula is copied.

See also: Absolute Cell Reference, Mixed Reference.

Remove Duplicates

Removes repeated records based on one or more selected columns. For a non-destructive list of distinct values, modern Excel users can also use UNIQUE.

Row

A horizontal line of cells identified by a number. Modern Excel worksheets contain 1,048,576 rows.

Sheet / Worksheet

An individual grid within an Excel workbook. A workbook can contain multiple worksheets as well as chart sheets.

Slicer

A visual filtering control commonly used with Tables and PivotTables. Slicers make filter state easy to see and change.

Sort

Reorders rows according to one or more columns, values, colours or custom lists. Sorting should be performed on the full dataset to keep records together.

Spill / Spill Range

When a dynamic-array formula returns several values, Excel places them into neighbouring cells automatically. The resulting area is called the spill range and can be referenced with the # operator, for example A2#.

Structured Reference

A formula reference that uses Excel Table and column names instead of ordinary cell addresses.

=SUM(Sales[Amount])

SUBSTITUTE

Replaces specified text within a text string.

=SUBSTITUTE(A2,"Ltd","Limited")

SUBTOTAL

Calculates totals such as SUM, AVERAGE or COUNT and can ignore filtered-out rows. Function numbers 101–111 also ignore manually hidden rows.

SUMIF / SUMIFS

Add values that meet one or more criteria.

=SUMIFS(C:C,A:A,"UK",B:B,"Q1")

Excel Table

A structured data range created with Insert > Table or Ctrl+T. Tables automatically expand, provide filtering, use structured references and work well as sources for PivotTables and Power Query.

TAKE / DROP

Dynamic-array functions that return or remove a specified number of rows or columns from the beginning or end of an array.

TEXTSPLIT / TEXTBEFORE / TEXTAFTER

Modern text functions for splitting text by delimiters or returning content before or after a chosen delimiter. They can replace many older combinations of LEFT, RIGHT, MID and FIND.

Timeline

A date-based filtering control for PivotTables that lets users filter by years, quarters, months or days.

TRANSPOSE

Switches rows and columns. It is available as both a Paste Special operation and a worksheet function.

TRIM

Removes extra ASCII spaces from text, leaving single spaces between words. It does not remove every possible non-printing or Unicode space character.

UNIQUE

A dynamic-array function that returns distinct values or rows from a range or array.

=UNIQUE(A2:A100)

Unpivot (Power Query)

Transforms columns into attribute/value rows. Unpivoting is particularly useful when converting report-style data into a structure suitable for analysis.

VBA (Visual Basic for Applications)

Excel's desktop macro programming language. VBA can automate workbook tasks, build user interfaces and create custom functions.

Start with Excel VBA Introduction.

VLOOKUP

Looks for a value in the first column of a table and returns a value from another column in the same row. It remains widely used, although XLOOKUP is more flexible in supported versions of Excel.

VSTACK / HSTACK

Dynamic-array functions that combine arrays vertically or horizontally.

What-If Analysis

Excel tools for exploring how changing inputs affects results. The group includes Goal Seek, Scenario Manager and Data Tables.

Workbook

An Excel file containing one or more worksheets and other workbook objects. Common file types include .xlsx, .xlsm and .xlsb.

WORKDAY / WORKDAY.INTL

Returns a date a specified number of working days before or after a starting date. The INTL version supports custom weekend patterns.

XLOOKUP

A flexible modern lookup function that searches one range or array and returns the corresponding value from another. It supports exact matching by default, can look left or right and can specify a result when no match is found.

=XLOOKUP(A2,Products[Code],Products[Price],"Not found")

XMATCH

Returns the relative position of an item in a range or array and offers more matching and search options than MATCH.

YEAR / YEARFRAC

YEAR extracts the year number from a valid Excel date. YEARFRAC calculates the fraction of a year between two dates.

Zoom

Controls the on-screen magnification of a worksheet without changing printed size, cell dimensions or stored values.

Object

An Excel item that VBA can control, such as a Workbook, Worksheet, Range or Chart.

Property

An attribute of an object, such as Range("A1").Value or a worksheet's Name.

Method

An action an object can perform, such as Range("A1").ClearContents or Workbook.Save.

Procedure / Sub

A named block of VBA code that performs actions. A Sub does not return a value directly.

Function (VBA)

A VBA procedure that returns a value. User-defined functions can be called from worksheet formulas when designed appropriately.

Variable

A named storage location used by VBA code. Variables are normally declared with Dim and a suitable data type.

Loop

A programming structure that repeats code, such as For...Next, For Each...Next or Do...Loop.

Event

An action such as opening a workbook or changing a worksheet cell that can automatically trigger VBA code.