MOS Excel Expert (Office 2019) flashcards

32 cards. Read the term and the definition here. The deck at the top of this page is optional.

Track Changes limitation

Track Changes is unavailable in workbooks that contain Excel tables and in workbooks shared via OneDrive/co-authoring; use version history or comments instead of the legacy feature.

Protect Workbook Structure

Blocks inserting, deleting, renaming, moving, hiding, or unhiding worksheets, and prevents changing sheet tab order. It does not restrict cell editing; Protect Sheet handles that.

Mark as Final effect

Sets the workbook to read-only and marks the status property. It is advisory, not secure: any user can choose Edit Anyway and modify the file.

Document Inspector purpose

Finds and removes hidden metadata such as comments, hidden rows and columns, personal information, and custom XML before sharing. Removed data cannot be restored by undo.

Calculation Options scope

Automatic, Automatic Except for Data Tables, and Manual are saved per workbook. File > Options > Formulas sets only the default for newly created workbooks.

Macro-enabled file formats

Macros require .xlsm (workbook) or .xltm (template). Saving VBA code as .xlsx discards the code without warning after a prompt.

Allow Edit Ranges

Defines password-protected ranges that remain editable while the rest of a protected worksheet is locked. Ranges are managed from Review > Allow Edit Ranges.

Compatibility Checker trigger

Runs automatically when saving to an older format and lists features Excel 2007-2013 cannot render, such as new functions, slicers, or icon sets.

Flash Fill behavior

Ctrl+E fills a column by inferring a pattern from one or two typed examples. It handles text splitting, joining, and case changes, but not arithmetic transformations.

Remove Duplicates scope

Deletes entire rows whose values repeat across the selected columns. Selecting only some columns removes rows matching on those columns alone, keeping the first occurrence.

Advanced Filter unique extract

With Copy To and Unique records only checked, Advanced Filter writes a deduplicated list to another location while leaving the source data unchanged.

Data Validation error styles

Stop rejects entry, Warning asks for confirmation but allows it, and Information shows a message while accepting any value. Input Message shows a tooltip on selection.

Conditional formatting Stop If True

When set on a rule, lower-priority rules are not evaluated for cells that meet this rule. Rules are applied in the order shown in Manage Rules.

SUBTOTAL function numbers

1-11 include manually hidden rows in the calculation; 101-111 ignore them. Both variants always ignore rows hidden by an AutoFilter.

Custom format four sections

Sections separated by semicolons format positive, negative, zero, and text values in that order. A format of ;;; hides all displayed content while the value remains.

Text to Columns fixed width

Splits a column at break lines you place manually. The final step sets each resulting column's data format and can skip columns entirely.

INDEX zero arguments

Passing 0 as the row or column argument returns the whole column or row: INDEX(A1:D10,0,3) returns C1:C10, usable inside SUM or MATCH.

Spill range operator

The # symbol after a dynamic array reference returns the entire spilled range, e.g. SUM(A1#). It errors if the reference is not a spill anchor cell.

SUMIFS criteria syntax

Each criteria pair uses a range and a criterion. Wildcards ? and * work in text criteria; comparison operators must be quoted, as in ">100".

SUMPRODUCT double unary

-- converts TRUE/FALSE arrays to 1/0 so SUMPRODUCT can multiply them by value arrays, enabling multi-condition sums without array entry.

Recorder absolute references

The macro recorder emits Selection-based code tied to the active cell. Replace it with explicit Range or Cells references so the macro runs predictably.

Personal.xlsb macro storage

A hidden workbook loaded at startup. Macros stored there are available in every open workbook; saving there avoids embedding code in each file.

Macro shortcut key scope

In the Macro dialog, Options assigns Ctrl plus a letter. The shortcut works only while a workbook containing that macro is open.

INDEX MATCH exact lookup

MATCH with 0 finds the exact position; INDEX returns the value at that position. Unlike VLOOKUP it can look left of the key column.

Change PivotTable source

PivotTable Analyze > Change Data Source redefines the range or table. Refresh alone does not pick up rows added outside the original source range.

PivotTable slicer connections

Right-click a slicer > Report Connections links it to multiple PivotTables sharing the same data source, so one click filters all of them.

PivotTable calculated field basis

Calculated fields compute from the sum of underlying source fields, not from displayed values of other fields, so results can differ from a formula outside the table.

Show Values As running total

Displays cumulative values across a chosen base field. The setting writes a formula into the value field and can reset on each group.

Secondary axis combo chart

Changing a series to a different chart type and checking Secondary Axis plots it against a right-hand scale, useful when series magnitudes differ greatly.

Structured reference syntax

Table references use TableName[Column], [#All], and [#This Row]. They auto-expand as rows are added, unlike fixed A1 ranges.

PivotChart linkage

A PivotChart is bound to one PivotTable. Filtering the chart filters the table; deleting the PivotTable also deletes the chart.

Switch Row and Column

On Chart Design, swaps which fields are plotted as series and which as categories, changing the legend and axis orientation without editing data.