Microsoft Excel Managing Tables and Table Data 2 — Questions and Answers
Question 1: How do calculated columns work in Excel Tables?
- Require manual entry in each cell
- A formula in one cell automatically fills the entire column (Correct answer)
- Only basic math
- PivotTable feature only
Correct answer: A formula in one cell automatically fills the entire column
Formulas auto-propagate through the entire Table column using structured references.
Uses structured references (@ means 'this row'). Editing any cell updates all. Auto-extends when new rows are added.
Question 2: How do structured references differ from cell references?
- Use numbers instead of letters
- Use table and column names like Table1[@Sales] instead of B2 (Correct answer)
- Another name for absolute references
- Only work in VBA
Correct answer: Use table and column names like Table1[@Sales] instead of B2
Structured references use descriptive names making formulas readable and resilient to changes.
Syntax: Table1[Column] = entire column, [@Column] = current row, [#Headers] = headers, [#All] = everything.
Question 3: What does Resize Table do?
- Changes font size
- Adjusts table boundaries to include more or fewer rows/columns (Correct answer)
- Changes column widths proportionally
- Scales for printing
Correct answer: Adjusts table boundaries to include more or fewer rows/columns
Resize Table modifies the table range to include or exclude rows and columns.
Only contiguous cells. For simple expansion, type adjacent to the table for auto-expansion. Useful when auto-expand doesn't trigger.
Question 4: What does Convert to Range do?
- Converts to chart
- Removes Table functionality while preserving data and formatting (Correct answer)
- Exports to file
- Converts to text
Correct answer: Removes Table functionality while preserving data and formatting
Convert to Range removes all Table features but keeps cell data and visual formatting.
Removes: auto-filters, structured references, auto-expansion, Total Row. Preserves: values, formatting, column widths.
Question 5: How do Table column filter dropdowns work?
- Type criteria in header
- Click header dropdown for sort, text/number filters, and checkbox selection (Correct answer)
- View tab filter button
- VBA only
Correct answer: Click header dropdown for sort, text/number filters, and checkbox selection
Each column header has a dropdown with sorting, value selection, and advanced filters.
Text filters: Contains, Begins With, etc. Number: Greater Than, Top 10, Above Average. Date filters for date columns. Multiple = AND logic.
Question 6: What is the Table Name used for?
- Watermark on table
- Unique identifier used in structured references and formulas (Correct answer)
- Sheet tab name
- Cannot be changed
Correct answer: Unique identifier used in structured references and formulas
Table Name is used in structured references and can be renamed in Table Design tab.
Meaningful names improve formula readability: =SUMIFS(SalesData[Amount],...) is clearer than =SUMIFS(Table3[Column2],...).
Microsoft Excel Managing Tables and Table Data 2
How do calculated columns work in Excel Tables?