MO-200 flashcards

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

Shift+F11

Keyboard shortcut that inserts a new worksheet into the active workbook, placing it before the currently active sheet.

Freeze Panes

View option that locks rows above and columns to the left of the active cell so they stay visible while scrolling. The split occurs at the active cell.

Print Titles

Page Layout setting that repeats specified rows at the top or columns at the left of every printed page of a worksheet.

Protect Workbook

Review command that restricts structural changes such as inserting, deleting, renaming, or moving worksheets; it does not prevent editing cell contents.

Custom Views

Saved combination of hidden rows and columns, print settings, and column widths for a workbook. Unavailable when the workbook contains an Excel table.

XLSM format

Excel workbook file format that stores VBA macros. The standard XLSX format cannot retain macro code when saved.

Grouped sheets

State in which multiple worksheet tabs are selected, so edits, formatting, and page setup apply to every selected sheet simultaneously.

Paste Special

Dialog (Ctrl+Alt+V) that pastes only chosen attributes such as values, formulas, formats, column widths, or transposed orientation, or applies arithmetic operations.

Flash Fill

Data tool (Ctrl+E) that detects a pattern from one typed example and fills the rest of the column; it does not refresh automatically.

Text to Columns

Wizard that splits one column into several using delimiters such as commas or tabs, or fixed-width breakpoints, assigning a data format per column.

Go To Special

Selection tool (Ctrl+G, Special) that highlights blanks, constants, formulas, current region, row differences, precedents, or dependents in the active range.

Defined name rules

A range name must begin with a letter, underscore, or backslash, contain no spaces, and must not resemble a cell reference such as A1.

Number format sections

Custom formats use up to four sections separated by semicolons: positive; negative; zero; text. Each section formats that category of value.

Transpose

Paste Special option that flips copied data so rows become columns and columns become rows, preserving values and formulas.

Conditional formatting priority

Rules are evaluated in the order shown in Manage Rules; Stop If True prevents lower-priority rules from applying once a rule matches.

Create table shortcut

Ctrl+T or Insert > Table converts a selected range into an Excel table with a header row and filter buttons.

Structured reference

Formula syntax using the table name and column name in brackets, such as Sales[Amount]; [@] refers to the current row value.

Total Row

Table Design option that appends a summary row where each column can use a function such as Sum, Average, Count, or Max.

Table slicer

Visual filter control inserted for a table; it can be connected to multiple tables or PivotTables so one selection filters all of them.

Convert to Range

Table Design command that turns a table back into a normal cell range, replacing structured references with ordinary cell references.

Remove Duplicates

Data command that permanently deletes rows with duplicate values in the selected columns; it does not merely highlight them.

XLOOKUP

Function that searches a lookup array and returns the matching item from a return array, can look left or right, and defaults to exact match.

INDEX and MATCH

Combination where MATCH returns the position of a value and INDEX returns the item at that position, allowing lookups in any direction.

SUMIFS

Function that sums a range only for rows meeting all specified criteria pairs, supporting multiple criteria ranges and conditions.

IFS function

Function that tests conditions in order and returns the value paired with the first TRUE condition, replacing nested IF statements.

IFERROR

Function that returns a specified alternative value when the tested expression produces any error, such as #N/A or #DIV/0!.

TEXTJOIN

Function that concatenates text from a range using a chosen delimiter, with an option to ignore empty cells.

EOMONTH

Function returning the serial number of the last day of the month a specified number of months before or after a start date.

ROUND vs INT

ROUND rounds half away from zero to a set number of digits; INT rounds down to the nearest integer, toward negative infinity for negatives.

F11 chart sheet

Keyboard shortcut that creates a chart from the selected data on a new chart sheet rather than embedding it in a worksheet.

Switch Row/Column

Chart Design command that swaps series and categories when Excel plots the data in the wrong orientation.

Secondary axis

Chart option that plots a series against a separate vertical scale, used when series differ greatly in magnitude or units.

Sparklines

Miniature line, column, or win/loss charts placed inside a single cell and linked to a source data range.