MO-211 cheat sheet

Calculation modes are Automatic, Automatic Except for Data Tables, and Manual; Manual recalculates only on F9 (all open workbooks) or Shift+F9 (active sheet).

Iterative calculation is enabled at File > Options > Formulas and resolves circular references by limiting maximum iterations (default 100) and maximum change (default 0.001).

Mark as Final (File > Info > Protect Workbook) sets the file to read-only and adds a status message; it does not encrypt the workbook or set a password.

Protect Workbook restricts workbook structure (insert, delete, rename, move, hide, unhide sheets) and windows; Protect Sheet restricts cell editing and can allow specific actions.

Macro security is set in Trust Center > Macro Settings; the default 'Disable all macros with notification' blocks macros and shows a security warning bar.

Compatibility Checker (File > Info > Check for Issues > Check Compatibility) lists features unsupported in Excel 2007–2013 before saving.

Custom document properties are created in Advanced Properties > Custom; checking 'Link to content' binds the property to a named range.

AutoRecover save interval is set in File > Options > Save (default 10 minutes); AutoRecover does not replace saving the workbook.

Conditional formatting rules are evaluated top-to-bottom by priority; selecting Stop If True prevents lower rules from applying when the rule is true.

Data validation error alert styles are Stop (blocks entry), Warning (allows override), and Information (message only); input messages appear when the cell is selected.

Remove Duplicates keeps the first occurrence of each combination of selected columns and permanently deletes the remaining rows.

Advanced Filter criteria: conditions in the same row are joined with AND; conditions in different rows are joined with OR.

Custom number format sections follow positive;negative;zero;text, separated by semicolons; with two sections, the first applies to positive and zero and the second to negative.

Flash Fill (Ctrl+E) completes a column by detecting a pattern from adjacent columns; it runs without formulas and does not update automatically when source data changes.

Text to Columns splits one column into multiple columns using Delimited (comma, tab, semicolon, space, other) or Fixed Width boundaries.

The Subtotal command requires sorted data; 'Each change in' creates a group, and subtotals can use SUM, COUNT, AVERAGE, and other functions.

XLOOKUP syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]); match_mode 0 exact, -1 exact or next smaller, 1 exact or next larger, 2 wildcard.

INDEX/MATCH: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)) performs a left-or-right lookup independent of column order.

SUMIFS, COUNTIFS, and AVERAGEIFS accept criteria as text with operators and wildcards, e.g. '>=100', 'A*', and '~*' for a literal asterisk.

Dynamic array formulas spill into adjacent cells; the spilled range operator # (e.g., A1#) references the entire spill range, and @ forces implicit intersection.

LET assigns names to intermediate results: =LET(name1, value1, name2, value2, calculation); names must start with a letter and cannot conflict with range syntax.

LAMBDA builds a reusable function: =LAMBDA(param1, param2, calculation); name it in Name Manager to call it like a built-in function.

IFERROR returns a fallback for any error value; IFNA returns a fallback only for #N/A and lets other errors surface.

Array constants use commas to separate columns and semicolons to separate rows, e.g., {1,2;3,4} is a 2x2 array.

OFFSET and INDIRECT are volatile functions; they recalculate on every worksheet change.

PivotTable Values area aggregates fields: numeric fields default to SUM, text fields to COUNT; Show Values As enables % of total, running total, and difference from previous.

A calculated field adds a new data field using a formula of existing fields; a calculated item adds a new category within one row or column field.

Slicers and Timelines filter PivotTables; a slicer can be connected to multiple PivotTables via Report Connections.

A PivotChart is linked to its PivotTable; filtering or sorting one changes the other.

Combo charts plot series on a secondary axis when value scales differ; set the series chart type and Secondary Axis in Format Data Series.

Trendline types: linear, exponential, logarithmic, polynomial, power, and moving average; moving average requires a period and does not forecast.

Sparklines are Line, Column, or Win/Loss mini charts stored in a single cell; they reference a data range and can mark high/low points.

Chart templates (.crtx) save chart formatting and are applied through Change Chart Type > Templates.

Tables use structured references such as Table1[Sales]; calculated columns fill automatically when a formula is entered in one cell.