MO-200 cheat sheet

Workbook file formats: .xlsx (no macros), .xlsm (macros enabled), .xlsb (binary), .xltx/.xltm (templates), .csv (plain text, single sheet, no formatting or formulas preserved).

Insert a worksheet: Shift+F11 inserts a new sheet before the active sheet; Home > Insert > Insert Sheet adds it the same way; right-click a tab > Insert offers a dialog with worksheet, chart sheet, and template options.

Navigate between sheets with Ctrl+Page Down (next) and Ctrl+Page Up (previous); Ctrl+Shift+Page Down/Up extends selection across multiple sheets into a grouped sheet selection.

Grouped sheets: select multiple tabs (Ctrl+click for non-adjacent, Shift+click for a range) so edits, formatting, and page setup apply to all selected sheets; ungroup by right-clicking a tab > Ungroup Sheets.

Freeze panes: View > Freeze Panes > Freeze Top Row, Freeze First Column, or Freeze Panes freezes rows above and columns left of the active cell; the split is placed at the active cell, not centered on it.

Print setup: Page Layout > Print Titles repeats rows/columns on every printed page; Scale to Fit offers Adjust to % and Fit Sheet on One Page; Print Area defines the exact range that prints.

Workbook protection: Review > Protect Workbook restricts structural changes (insert/delete/move sheets); Protect Sheet restricts cell editing and is configured per worksheet.

Custom views (View > Custom Views) store hidden rows/columns, print settings, and column widths per workbook; they are unavailable if the workbook contains a table.

Document properties: File > Info > Properties > Advanced Properties edits title, author, and keywords; the header/footer can insert fields such as page number, date, file name, and sheet name.

Paste Special options: Values, Formulas, Formats, Transpose, Skip Blanks, Add/Subtract/Multiply/Divide, Column Widths; Ctrl+Alt+V opens the Paste Special dialog.

Flash Fill (Ctrl+E or Data > Flash Fill) infers a pattern from one typed example and fills the column; it requires a consistent pattern and does not update automatically when source data changes.

Text to Columns (Data > Text to Columns) splits one column into several using Delimited (comma, tab, semicolon, space) or Fixed Width; each column can be assigned a data format during the wizard.

Transpose reverses rows and columns; use Paste Special > Transpose, or TRANSPOSE as a dynamic array formula in modern Excel.

Name a range via the Name Box or Formulas > Define Name; names must start with a letter, underscore, or backslash, cannot contain spaces, and cannot look like a cell reference (e.g., A1).

Go To Special (Ctrl+G > Special) selects Blanks, Constants, Formulas, Current Region, Row Differences, Column Differences, Precedents, and Dependents; useful for filling blanks or auditing.

Custom number format sections follow positive;negative;zero;text order; placeholders include 0 (digit), # (optional digit), , (thousands separator), and % (percent).

Conditional formatting rules are managed in Home > Conditional Formatting > Manage Rules; rule order determines priority, and Stop If True prevents lower rules from applying.

Create a table with Ctrl+T or Insert > Table; a header row is assumed, and each column header must be unique and non-blank.

Structured references use the table name plus column name in brackets, e.g., =SUM(Sales[Amount]) or =Sales[@Amount]*Sales[@Rate]; [@] refers to the current row.

Total Row (Table Design > Total Row) adds a summary row; the dropdown offers Sum, Average, Count, Count Numbers, Max, Min, and other functions per column.

Table styles are applied via Table Design > Table Styles; banded rows/columns, header row, total row, first column, and last column are toggles in Table Style Options.

Slicers (Table Design > Insert Slicer) filter table data visually; they can be connected to multiple tables or PivotTables so one selection filters all of them.

Remove duplicates (Data > Remove Duplicates) permanently deletes rows; it does not highlight them, and it operates on the selected range or table.

Table formulas auto-fill down new rows when the table is extended; calculated columns use the same formula in every row of a column.

Convert a table back to a normal range with Table Design > Convert to Range; structured references are replaced by cell references.

Lookup: VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) searches the first column and returns a value to the right; XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) can look left or right and defaults to exact match.

INDEX(array, row_num, [column_num]) returns a value by position; MATCH(lookup_value, lookup_array, [match_type]) returns position; MATCH with 0 is exact match, 1 is ascending, -1 is descending.

SUMIFS(sum_range, criteria_range1, criteria1, ...) and COUNTIFS(criteria_range1, criteria1, ...) accept multiple criteria; criteria can be text, numbers, or expressions like ">100" and "<>"&A1.

IF(logical_test, value_if_true, [value_if_false]) nests up to 64 levels; IFS(condition1, value1, condition2, value2, ...) evaluates in order and returns the first TRUE result.

Error handling: IFERROR(value, value_if_error) and IFNA(value, value_if_na) replace errors; common errors are #N/A (not found), #DIV/0!, #VALUE!, #REF!, and #NAME?.

Text functions: CONCAT/TEXTJOIN( delimiter, ignore_empty, text1, ...), LEFT, RIGHT, MID, LEN, TRIM, UPPER, LOWER, PROPER, and SUBSTITUTE; TEXTJOIN ignores empty cells when ignore_empty is TRUE.

Date and time: TODAY() and NOW() are volatile; DATEDIF(start, end, unit) with units "Y", "M", "D"; EDATE and EOMONTH shift by months; dates are stored as serial numbers starting at 1 = January 1, 1900.

Rounding: ROUND(number, num_digits) rounds half away from zero; ROUNDUP and ROUNDDOWN always round in one direction; INT truncates toward negative infinity while TRUNC truncates toward zero.

Chart types: column, bar, line, pie, doughnut, area, scatter (XY), bubble, stock, radar, treemap, sunburst, histogram, Pareto, box and whisker, waterfall, funnel, and combo charts.

Insert a chart with Alt+F1 (embedded on the current sheet) or F11 (chart sheet); both use the currently selected data range.

Switch Row/Column (Chart Design > Switch Row/Column) swaps which series are plotted as categories versus values; use it when Excel misreads the data orientation.

Select Data Source dialog sets the chart data range, individual series, category axis labels, and the position of series; it also allows adding a series that is not adjacent to the source range.

Chart elements (Chart Design > Add Chart Element): chart title, axis titles, legend, data labels, data table, gridlines, trendline, error bars, and axis options.

A secondary axis (Format Data Series > Secondary Axis) plots a series on a different scale; it is typically used when series have very different magnitudes or units.

Sparklines (Insert > Sparklines) are line, column, or win/loss mini-charts placed in a single cell; they reference a data range and a location range.

A PivotChart is linked to a PivotTable; filtering the PivotChart filters the PivotTable, and field buttons can be hidden via PivotChart Analyze > Field Buttons.