ICDL Advanced Spreadsheets key terms

A feature that automatically applies a format such as colour, an icon or bold text to cells whose contents meet rules you set, so exceptions and patterns stand out without changing the stored values.

A saved combination of formatting settings - font, size, colour, borders, number format and alignment - that can be applied to a cell in one step and updated everywhere it is used.

A code built from placeholders such as 0, #, commas and currency symbols that controls how a number is displayed without altering the stored value, for example fixing decimals or adding thousands separators.

A tool that copies the formatting of a selected cell or range and applies it to another cell or range with a click or drag, leaving the underlying data untouched.

Two or more adjoining cells combined into one larger cell that spans their columns or rows, typically used for titles; only the content of the top-left cell is kept.

A view setting that locks chosen rows or columns in place so that headings stay visible while you scroll through the rest of a large worksheet.

A cell address such as A1 that shifts automatically when a formula is copied or filled to another row or column, because it is stored as an offset from the formula's own cell.

A cell address written with dollar signs, such as $A$1, that keeps pointing at the same cell wherever the formula is copied, which is essential for fixed rates and lookup tables.

A cell address in which only the column or only the row is locked, such as $A1 or A$1, so copying in one direction keeps part of the address fixed while the other part moves.

A descriptive label given to a cell or range, such as TaxRate, that can be used in formulas in place of the raw address, making the formulas easier to read and maintain.

A function placed inside the arguments of another function, for example an IF inside an IF, so that a single formula can handle several tests or calculations.

A logical function that tests a condition and returns one value when the test is true and a different value when it is false.

A lookup function that searches for a value in the first column of a table and returns data from a stated column of the matching row, commonly used to turn codes into descriptions.

A function that counts how many cells in a range meet a single condition, such as all entries for one region or all amounts above a stated figure.

A message such as #DIV/0!, #N/A or #VALUE! that appears in a cell when a formula cannot produce a result, usually because of a bad reference, wrong data type or impossible calculation.

A value, cell reference, range or piece of text supplied to a function inside its brackets that tells the function what data to work on and how to behave.

The set of related values in a chart, such as one row or column of numbers, that is plotted together and shares a colour, marker or pattern and a name in the legend.

The axis of a chart that displays the labels or groups being compared, usually the horizontal axis, against which each data series is plotted.

The axis of a chart that carries the numeric scale the data is measured against, usually vertical, and that determines how tall or long each plotted point appears.

The key shown beside a chart that pairs each colour, pattern or marker with the name of the data series it represents, so readers can tell the series apart.

Text attached to individual points, bars or slices of a chart that shows the underlying value, category name or percentage directly on the plot area.

A line added to a chart that follows the general direction of a data series and can optionally display a forecast or a measure of how well it fits the points.

An extra value axis placed on the other side of a chart so a series measured on a very different scale can be plotted alongside the main series without being flattened.

Reordering the rows of a table or range by one or more columns, in ascending or descending order, while keeping each record's data together on its row.

A table feature that adds drop-down arrows to column headings so rows can be shown or hidden by picking values or by applying conditions on text, numbers and dates.

A filter condition the user builds, such as greater than, begins with or between two values, which can be combined with AND or OR to isolate specific records.

A command or function that inserts summary rows, such as sums or averages, beneath groups of sorted data and can collapse or expand the detail rows by level.

An interactive summary table that groups and aggregates large amounts of data by dragging fields into row, column, value and filter areas without writing any formulas.

An on-screen panel of buttons that filters a pivot table or table when you click a value, showing at a glance which items are currently selected.

A what-if tool that finds the single input value needed for a formula to reach a target result, such as the price that produces a given level of profit.

A rule placed on cells that restricts what may be typed into them, for example whole numbers within a range or items from a list, and can explain or correct invalid entries.

A validation setting that limits a cell to values picked from a drop-down arrow, built either from a range of cells or from a list typed into the dialog box.

A short note that appears when a cell is selected, telling the user what kind of entry is expected before anything is typed.

The message shown when an invalid entry is typed into a validated cell; its style can stop the entry completely, warn the user or simply offer information.

A setting that blocks changes to cells, formatting or inserted rows on a single sheet, optionally behind a password, while leaving other sheets in the file editable.

A cell property that is switched on by default but only takes effect once sheet protection is turned on, deciding whether that cell can be edited.

A setting that restricts structural changes to a whole file, such as adding, deleting, hiding or renaming sheets, and can also require a password to open it.

The specific range or ranges of a worksheet that have been marked to print, so that any other content on the sheet is left out of the printed output.

Rows or columns, usually headings, that are set to repeat at the top or on the left of every printed page of a long worksheet.

A manual or automatic division that decides where one printed page ends and the next begins, which can be inserted, moved or removed in page break preview.