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

MO-210: Manage Tables and Table Data

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

All guide sections

Manage Tables and Table Data

Tables as structured objects

An Excel table (Ctrl+T) is a named object with banded rows, a header row, filter buttons, a Total Row, and automatic calculated columns that fill every row when you type a formula. The big idea is structured references: instead of =SUM(C2:C50) you write =SUM(Sales[Amount]), and instead of =B2*C2 you write =[@Price]*[@Quantity]. Know the special item specifiers: [#Headers], [#Data], [#Totals], [#All], and [@Column] for the current row.

Slicers can be attached to a table for quick filtering, and tables are the preferred source for PivotTables and charts because they expand automatically.

Traps exam writers like

  • Table names cannot contain spaces and cannot look like cell references. Duplicate header text is silently renamed (Column2, Column3), which breaks structured references you copied from elsewhere.
  • Convert to Range keeps the values and formatting but destroys structured references - formulas using them return #NAME? or #REF!.
  • The Total Row uses SUBTOTAL-style functions, so it respects filtering; a hand-typed SUM under the table does not.
  • Calculated columns overwrite a manually entered value in a new row unless you undo the auto-fill.
  • Slicers work with tables and PivotTables, not with ordinary ranges.

Worked example

In a table named Sales with columns Amount and Region, region totals use =SUMIFS(Sales[Amount],Sales[Region],"West"), and the table's own total appears in =Sales[[#Totals],[Amount]]. Note the double brackets - a single bracket pair with a special item fails.