I85PREP
⌘ K
MO-200 study materials
MO-200 study resources

MO-200: Manage data cells and ranges

MO-200 · Work through your exam, one topic at a time.

All guide sections

Manage data cells and ranges

The Manage data cells and ranges domain covers cell-level editing and formatting: paste options, fill series, text splitting, row/column sizing, number formats, conditional formatting, named ranges, and sparklines. Questions often test what an operation changes and what it leaves untouched.

Cell operations

  • Paste Special can paste Values, Formulas, Formats, or Transpose; operations such as Add and Multiply transform pasted numbers; Skip Blanks protects existing cells.
  • AutoFill extends patterns, dates, and series. Flash Fill (Ctrl+E) detects a pattern from examples. Text to Columns splits delimited or fixed-width text.
  • Inserting or deleting cells shifts neighboring cells. Row height and column width can be set precisely or auto-fit. Merge Cells keeps only the upper-left value.

Formatting and names

  • Number formats change display, not the stored value. The Format Cells dialog also controls alignment, borders, fill, and protection.
  • Conditional Formatting rules have priority order and Stop If True; Quick Analysis can add data bars, color scales, and icon sets.
  • Formulas > Define Name creates a named range; scope can be workbook or worksheet. Name Manager edits or deletes names. Named ranges work in formulas and are absolute by nature.
  • Sparklines are Line, Column, or Winloss mini-charts placed in cells, with Sparkline Tools for markers and axes.

Worked example

A column holds Doe, Jane. Split it with Data > Text to Columns > Delimited > Comma, then trim spaces. To keep a tax rate in one cell while copying a formula down, name the cell TaxRate and use =B2*TaxRate. The name is absolute. Alternatively, =B2*$C$1 locks both row and column with dollar signs. Both work, but the named range is easier to audit.

Traps

Format Painter copies formatting, not column width. Merged cells break sorting and filtering and retain only the upper-left value. A number format does not change the underlying number. Conditional formatting rule order matters. Flash Fill is not dynamic and does not update automatically.