MO-210 flashcards

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

Ctrl+Page Down

Keyboard shortcut that activates the next worksheet tab to the right in the workbook. Ctrl+Page Up moves to the previous tab instead.

3D reference

Formula syntax spanning several worksheets, such as =SUM(Sheet1:Sheet3!B2), which adds cell B2 on every sheet from Sheet1 through Sheet3 inclusive.

Freeze Panes

View tab command that keeps the rows above and the columns to the left of the active cell visible while scrolling. Freeze Top Row and Freeze First Column are one-click presets.

Shift+F11

Keyboard shortcut that inserts a new blank worksheet to the left of the active sheet tab. The Insert Sheet command on the Home tab ribbon does the same thing.

Custom Views limitation

Custom Views store print settings, hidden rows and column widths, but Excel disables the entire feature once any worksheet in the workbook contains an Excel table.

Print titles

Page Layout setting that repeats specified rows at the top and columns at the left of every printed page. It is stored per worksheet, not per workbook.

Name Box

Box at the left end of the formula bar that shows the active cell address and can create or jump to a named range when a name is typed into it.

Paste Special Transpose

Option in the Paste Special dialog, opened with Ctrl+Alt+V, that reorients copied data so rows become columns and columns become rows in the destination range.

Flash Fill

Data tab feature, shortcut Ctrl+E, that learns a pattern from examples typed in a neighboring column and fills the remaining cells without any formula.

Center Across Selection

Alignment option in Format Cells that centers a heading across adjacent blank cells without merging them, so every cell keeps its own usable address.

Stop If True

Conditional formatting rule setting that ends evaluation for a cell once that rule is satisfied, so any lower-priority rules never apply to it.

Text to Columns

Data tab wizard that splits one column into several using delimiters such as comma or tab, or fixed character widths, with an optional destination cell.

Structured reference

Formula syntax that uses table and column names instead of cell addresses, for example =SUM(Sales[Amount]) for a whole column or =Sales[@Amount] for the current row.

Total Row

Table option that adds a final row of SUBTOTAL formulas, which recalculate so the totals reflect only the rows that remain visible after filtering.

Convert to Range

Table Design command that removes table features such as structured references, automatic expansion and the Total Row while keeping the data and its formatting intact.

Remove Duplicates

Data tab command that permanently deletes rows with repeated values in the selected columns and reports how many rows were removed and how many unique rows remain.

Ctrl+T

Shortcut that converts the selected range into an Excel table with filter buttons, a table style and automatic expansion. Ctrl+Shift+L only toggles filter arrows.

Slicer

Clickable filter panel inserted for a table column with Table Design > Insert Slicer. It lists only the values present in that column and can be resized and styled.

VLOOKUP exact match

=VLOOKUP(key, table, column_number, FALSE) searches the first column of the table for an exact match to the key and returns a value from the specified column.

XLOOKUP

Function that searches any row or column and returns a matching value from a separate range, with optional if_not_found and match_mode arguments, avoiding column counting.

IFERROR

Wraps another formula and returns a chosen alternative result when the calculation produces an error such as #N/A, #VALUE!, #DIV/0!, #REF!, #NUM! or #NAME?.

IFS function

=IFS(test1, result1, test2, result2, ...) returns the result attached to the first true test, replacing nested IFs, and returns #N/A when no test is true.

F4 key

While editing a formula, cycles the reference under the cursor through relative (A1), absolute ($A$1), row-locked (A$1) and column-locked ($A1) forms.

SUMIFS

=SUMIFS(sum_range, criteria_range1, criteria1, ...) adds the values that satisfy every listed condition; criteria accept comparison operators and wildcards such as * and ?.

TEXTJOIN

=TEXTJOIN(delimiter, ignore_empty, text1, ...) combines text from multiple cells or ranges, inserting the delimiter between items and optionally skipping blank cells.

NETWORKDAYS

=NETWORKDAYS(start_date, end_date, [holidays]) counts the working days between two dates, excluding Saturdays, Sundays and any holiday dates supplied in the third argument.

Alt+F1

Creates an embedded chart of the selected data on the current worksheet using the default chart type. F11 instead creates the chart on a separate chart sheet.

Switch Row/Column

Chart Design command that exchanges which direction of the source data is plotted as series and which supplies the category labels along the horizontal axis.

Secondary axis

Chart option for a series that uses a different scale or unit from the others. The axis appears on the right side and is set through Change Chart Type in a combo chart.

Scatter chart

Chart type that plots one numeric variable against another using X and Y values, revealing correlation. Both the horizontal and vertical axes are value axes.

Sparkline

Miniature chart drawn inside a single cell through Insert > Sparklines that summarizes a row or column of values as a line, column or win/loss graphic.

Value From Cells

Data label option that displays text taken from a worksheet range rather than the plotted value, series name or category name, via Format Data Labels.