MOS Excel Expert (Office 2019) cheat sheet

Workbook calculation mode is set per workbook at Formulas > Calculation Options; File > Options > Formulas sets the default for new workbooks only.

Track Changes (Review > Track Changes) stores history in a shared workbook and is disabled when the workbook contains Excel tables.

Protect Workbook (Review) has two independent checkboxes: Structure blocks sheet insert/delete/rename/hide, Windows blocks resize/move/close of the window.

Allow Edit Ranges (Review > Protect Sheet) permits specific unlocked ranges with optional passwords even when the sheet is protected.

Mark as Final sets the file to read-only and toggles the status property; it is advisory only and any user can click Edit Anyway.

Document Inspector (File > Info > Check for Issues) removes comments, hidden rows/columns, personal info, and custom XML; removal is irreversible on save.

Compatibility Checker flags features unsupported by Excel 2007-2013; the Version drop-down under Save As sets the file format default.

Trust Center settings control macro and ActiveX behavior; macro-enabled formats are .xlsm, template .xltx/.xltm, binary .xlsb.

AutoRecover interval and location are set at File > Options > Save; AutoRecover is not a substitute for saving and is deleted on successful save.

Flash Fill (Ctrl+E) infers a pattern from one typed example; it works on text transformations but not on numeric arithmetic patterns.

Text to Columns (Data) splits by delimited characters or fixed width; the third step sets each column's format and can skip columns.

Remove Duplicates (Data) compares whole rows by default; selecting a subset of columns deletes rows whose selected values repeat.

Data Validation has three tabs: Settings (rule), Input Message (tooltip), Error Alert (stop/warning/information); Circle Invalid Data marks violations.

Conditional formatting rules are evaluated top-down; use Stop If True on a rule to prevent lower-priority rules from applying.

Advanced Filter with Copy To another location and Unique records only produces a deduplicated extract without altering source data.

Subtotal (Data > Outline) inserts SUBTOTAL with function numbers 1-11 (include hidden) or 101-111 (ignore hidden rows).

Custom number format sections are positive;negative;zero;text, and a format of ;;; hides all cell content while keeping the value.

Dynamic arrays (Excel 2019/365) spill results; the # operator references a whole spill range, e.g. =SUM(A1#).

INDEX with two zero arguments returns an entire row or column: =INDEX(A1:D10,0,3) returns column C of the range.

XLOOKUP is unavailable in Excel 2019; use INDEX/MATCH or VLOOKUP with FALSE for exact matching.

SUMIFS/COUNTIFS/AVERAGEIFS accept multiple criteria ranges; all ranges must be the same size or a #VALUE! error is returned.

SUMPRODUCT with double unary minus coerces TRUE/FALSE to 1/0: =SUMPRODUCT(--(A1:A9="x"),B1:B9).

The macro recorder writes Selection-based code; edit it to use explicit Range objects so the macro is not tied to the active cell.

Workbook macros require .xlsm and a Trust Center macro setting; a macro stored in Personal.xlsb is available in every workbook.

The Macro dialog (Alt+F8) offers Options to assign a shortcut key (Ctrl+letter) and to scope the macro to all open workbooks.

A PivotTable's source range can be extended via Change Data Source; new rows outside the original range are not picked up by Refresh.

PivotTable slicers filter one table; a Timeline filters date fields and can be shared across PivotTables with the same data source via Report Connections.

Calculated fields in a PivotTable operate on the sum of source fields, not on the displayed values of other fields.

Show Values As options include % of Grand Total, % of Parent Row Total, Running Total In, and Rank; each writes a formula to the field.

Combo charts are created by changing a series chart type to Secondary Axis; the secondary axis scales independently of the primary.

A chart's data can be extended by dragging the range outline; Switch Row/Column (Chart Design) swaps series and category assignments.

Excel tables (Ctrl+T) auto-expand with new rows and use structured references such as Table1[Sales] in formulas and charts.

A PivotChart is linked to its PivotTable: filtering the chart filters the table, and deleting the table deletes the chart.