MO-210 cheat sheet

Ctrl+Page Down activates the next worksheet tab and Ctrl+Page Up the previous one; Shift+F11 inserts a new worksheet to the left of the active tab.

Cross-sheet reference syntax is =Sheet2!A1. A sheet name containing spaces must be wrapped in apostrophes: ='Q1 Sales'!B4.

A 3D reference such as =SUM(Sheet1:Sheet3!B2) totals the same cell across every sheet from Sheet1 through Sheet3 inclusive.

Freeze Panes (View tab) locks the rows above and the columns left of the active cell; Freeze Top Row and Freeze First Column are single-click presets that lock only that boundary.

Ctrl+End jumps to the last used cell, Ctrl+Home returns to A1, and Ctrl+Arrow key moves to the edge of the current data block.

Page Layout > Print Titles repeats chosen rows or columns on every printed page; Page Layout > Print Area fixes the range that prints. Both settings are stored per worksheet.

Custom Views (View tab) stores print settings, hidden rows/columns and column widths, but Excel disables the feature for the whole workbook if any worksheet contains an Excel table.

Protect Sheet (Review tab) blocks editing of cells marked Locked; every cell is locked by default, and the lock only takes effect after the sheet protection is turned on.

Right-click a sheet tab > Hide removes it from view; at least one sheet must stay visible, and Unhide appears on the same context menu.

Define Name (Formulas tab) or typing in the Name Box creates a range name. Names cannot contain spaces, must begin with a letter, underscore or backslash, and cannot be written like a cell reference such as A1.

Name Manager (Ctrl+F3) lists, edits, filters and deletes names. The Scope column decides whether a name is workbook-wide or limited to one worksheet, and sheet-scoped names override workbook names on that sheet.

Ctrl+1 opens the Format Cells dialog; Ctrl+Alt+V opens Paste Special with options for Values, Formats, Transpose, Skip Blanks and the Add/Subtract/Multiply/Divide operations.

Flash Fill (Data tab, Ctrl+E) detects a pattern from one or two typed examples in an adjacent column and fills the remaining cells without a formula.

Text to Columns (Data tab) splits one column using Delimited (comma, tab, semicolon, space) or Fixed width settings, and a Destination cell controls where the split values are placed.

Conditional Formatting rules run top-down in Manage Rules; enabling Stop If True on a rule halts evaluation for any cell that the rule already formats.

Paste Special > Transpose reorients copied data so rows become columns and columns become rows; it is a one-time paste, not a live link.

Ctrl+Shift+Plus inserts cells, rows or columns and the Insert dialog asks whether existing cells shift down or right; Ctrl+Minus deletes with the same shift choice.

Center Across Selection (Format Cells > Alignment) centers a heading across adjacent blank cells without merging, so each cell keeps a usable address.

Ctrl+T (or Insert > Table) converts a range to a table. Every column needs a unique header, and merged cells or blank headers inside the range prevent table creation.

Structured references use the table name: =SUM(Sales[Amount]) for an entire column, =Sales[@Amount] for the value on the current row, and =SUM(Sales[[Q1]:[Q4]]) for a span of columns.

Table Design > Total Row adds a final row built from SUBTOTAL formulas, so totals recalculate to reflect only the rows visible after filtering.

Remove Duplicates (Data tab) permanently deletes rows with repeated values in the selected columns and reports how many rows were removed and how many unique rows remain.

Convert to Range (Table Design) keeps the data and formatting but removes structured references, automatic expansion, the Total Row and style options.

Data > Sort supports up to 64 levels, each with its own column, Sort On value (cell value, cell color, font color, icon) and order; the Header row flag must be checked so headers are not sorted.

A slicer (Table Design > Insert Slicer) filters a table with clickable buttons that list only the values present in the chosen column.

Table names must be unique within the workbook and cannot contain spaces; rename a table in Table Design > Table Name.

Data typed in the row directly below a table, or a row inserted inside the table, expands the table range automatically and extends formulas and formats.

VLOOKUP(lookup_value, table_array, col_index_num, FALSE) finds an exact key in the leftmost column of the table and returns a value from the numbered column; INDEX with MATCH or XLOOKUP is used when the return column sits left of the key.

SUMIFS(sum_range, criteria_range1, criteria1, ...) sums values that meet every condition; COUNTIFS and AVERAGEIFS follow the same range/criteria pairing.

IF(logical_test, value_if_true, value_if_false) nests up to 64 levels; IFS(test1, result1, test2, result2, ...) returns the result of the first true test and returns #N/A when no test is true.

IFERROR(value, value_if_error) traps #N/A, #VALUE!, #DIV/0!, #REF!, #NUM! and #NAME? and returns a substitute value; IFNA traps only #N/A.

F4 cycles the reference under the cursor while editing: A1 (relative) > $A$1 (absolute) > A$1 (row locked) > $A1 (column locked).

ROUND(number, num_digits) rounds half away from zero, ROUNDUP moves away from zero and ROUNDDOWN toward zero, and INT floors to the nearest whole number.

Error values: #DIV/0! means division by zero or an empty divisor; #N/A means a lookup found nothing; #NAME? means unrecognized text; #REF! means a deleted reference; #VALUE! means the wrong argument type.

TODAY() and NOW() are volatile and recalculate on every edit; EOMONTH(start_date, months) returns the last day of a month; NETWORKDAYS(start, end, holidays) counts weekdays excluding listed holidays.

Formulas > Evaluate Formula steps through a calculation, and F9 on a selected part of a formula shows its current result; Ctrl+` toggles display of all formulas on the sheet.

Alt+F1 inserts an embedded chart of the selected data on the current worksheet; F11 places the chart on its own chart sheet.

Chart Design > Select Data adds or removes series and sets the horizontal (category) axis labels; Switch Row/Column swaps which direction supplies series and categories.

A scatter (XY) chart puts numeric values on both axes to show the relationship between two variables, while a line chart treats the horizontal axis as evenly spaced categories.

A combo chart plots one or more series on a secondary axis (Chart Design > Change Chart Type, Secondary Axis check box) when the series use very different units or scales.

Data labels can display value, percentage, series name, category name, or text pulled from worksheet cells via Format Data Labels > Value From Cells.

Sparklines (Insert > Sparklines: Line, Column, Win/Loss) live inside one cell and summarize a range of values; they create no chart object on the sheet.

A chart stays linked to its source cells, so edited values redraw automatically; the source range can be resized by dragging the colored outline handles or changed in Select Data.

Move Chart (Chart Design) relocates a chart to a new chart sheet or to any existing worksheet; a chart sheet appears as a tab and prints as a full page.

Pie and doughnut charts display a single data series only; multiple series require column, bar, line, or combo chart types.