MOS Excel Expert (Office 2019) key terms

A user-interface modification created by editing the workbook's XML parts to add, hide, or reorder tabs and groups. It travels with the file rather than with the application installation.

The zip-based file container that holds a workbook's parts, such as worksheets, styles, and custom UI XML. Renaming a copy to .zip lets you inspect and edit those parts directly.

The settings area controlling macro security, trusted locations, protected view, and add-in behavior for all workbooks opened on the machine. Policy here decides whether code runs or is blocked.

A folder or network path the user marks as safe, so files opened from it skip Protected View and macro blocking. Useful for a controlled macro library shared by a team.

A read-only sandbox in which files from the internet or unsafe locations open, with editing and macros disabled until the user enables content. It limits damage from malicious files.

A document property that flags a workbook as finished, making it read-only and showing a notification bar. It is advisory only; anyone can turn the flag off again.

A tool that finds and removes hidden content such as comments, personal information, invisible rows, and off-sheet metadata before a workbook is shared externally.

A state shown when a workbook uses an older file format, disabling newer features such as certain functions and chart types. Converting to the current format removes the restriction.

The setting that decides whether VBA code runs automatically, prompts the user, or is disabled. Digital signatures and trusted locations can override the default behavior.

A cryptographic certificate attached to a macro project or workbook that identifies the publisher and confirms the file has not been altered since signing.

The setting that controls when formulas recalculate: automatically, automatically except for data tables, or only when the user requests it. Manual mode speeds up very large models.

A pattern-recognition feature that fills a column based on examples you type, splitting or combining text without formulas. It works best when the pattern is consistent across rows.

A wizard that splits one column into several using a fixed width or a delimiter such as comma or semicolon. The source column is overwritten unless a destination is specified.

Rules that restrict what can be entered in a cell, such as a list, whole-number range, or date limit, optionally with an input message and error alert.

Formatting applied automatically when a cell value or formula meets a rule, such as highlighting duplicates or coloring data bars. Rules can be layered, prioritized, and stopped.

A descriptive label assigned to a cell or range so formulas and validation lists can refer to it by name instead of an address. Names can be workbook-wide or sheet-scoped.

Table-aware syntax that names columns instead of cell addresses, such as Sales[Amount]. References expand automatically as rows are added or removed from the table.

A range converted to a table object, gaining filter buttons, banded rows, automatic expansion, and structured references. Tables are the preferred source for PivotTables and charts.

A command that deletes rows sharing identical values in the selected columns, keeping the first occurrence. It changes the data permanently, unlike a filter.

A filter that uses a separate criteria range, supports complex AND/OR logic across columns, and can copy results to another location without altering the source.

A block of cells containing column headings and condition values that drives an Advanced Filter or database function. Rows of criteria are joined by OR; columns by AND.

The built-in data transformation engine that connects to sources, cleans and reshapes data through recorded steps, and loads the result to a table or data model.

An outline feature that inserts grouped totals and collapse buttons for sorted data, using the SUBTOTAL function so hidden rows are excluded from the result.

A function that performs calculations such as sum or average while optionally ignoring errors, hidden rows, or nested SUBTOTAL results, controlled by its second argument.

A formula result that spills into neighboring cells automatically, as with UNIQUE, SORT, or FILTER. The spill range is referenced with the hash operator.

The block of cells a dynamic array formula fills. Referencing the anchor cell with # addresses the whole block, and a #REF! error appears if the area is blocked.

The behavior where a formula expecting one value silently returns the item from the same row or column as the formula cell. The @ operator forces this deliberately.

A way to define a reusable custom calculation from a formula, with named parameters, without VBA. It can be saved as a named function for use across the workbook.

Assigns names to intermediate results inside a formula so each is computed once and the formula stays readable. It often improves performance in long calculations.

A lookup function that searches a range for a value and returns the matching item from another range, with built-in handling for not-found results and match modes.

A combined lookup pattern where MATCH finds a position and INDEX returns the value at that position. It works left-to-right or right-to-left and is robust to column insertion.

A formula that processes multiple values at once and, in older Excel, required Ctrl+Shift+Enter. Legacy array formulas show braces and can be replaced by dynamic arrays.

A function such as NOW, TODAY, OFFSET, INDIRECT, or RAND that recalculates every time any calculation occurs, even if its inputs have not changed.

A tool that captures keystrokes and clicks as VBA code in a module. The generated code is often verbose and needs editing to be general and reliable.

A container for procedure code inside a workbook project. Standard modules hold general macros, while sheet and ThisWorkbook modules hold event-driven procedures.

VBA code that runs automatically in response to an action, such as opening the workbook or changing a cell. It must sit in the correct object module to fire.

A recorder mode that stores movements relative to the starting cell rather than absolute addresses, so the macro can be reused at any position in the sheet.

An interactive summary that groups and aggregates source data by dragging fields into row, column, value, and filter areas, without writing formulas.

An in-memory relational layer inside the workbook that stores tables and relationships, letting PivotTables and Power Pivot analyze millions of rows and multiple sources.

A floating button control that filters a PivotTable, PivotChart, or table by clicking values. It shows current selection state and can be linked to several pivots.

A filter control for date fields that lets users select a period such as a month or quarter with a slider, applying to connected PivotTables.

A formula added to a PivotTable that computes a new value from existing fields, evaluated at the source-row level before aggregation rather than on displayed totals.

A custom member inside a PivotTable row or column field that combines existing items, such as grouping two products into a category. It cannot be used with grouped fields.

A chart that plots two or more series with different chart types on shared or secondary axes, commonly columns for amounts and a line for percentage.

An additional value axis on the opposite side of a chart, used when series have very different scales. It is enabled per series in the Format Series pane.

A saved file of chart formatting that can be applied to new charts to keep a consistent look. It stores colors, fonts, and layout but not the data.

A tiny chart drawn inside a single cell that shows a trend across a row or column of values. It is formatted through its own dedicated ribbon tab.

A chart bound to a PivotTable, sharing its filters and field layout. Dragging fields restructures the chart, and connected slicers filter both objects together.