Microsoft Excel Managing Tables and Data 2 — Questions and Answers
Question 1: What happens when you convert a range to an Excel Table (Ctrl+T)?
- Data becomes read-only
- Gains structured references, auto-filters, formatting, and dynamic expansion (Correct answer)
- Data moves to new worksheet
- Formulas convert to values
Correct answer: Gains structured references, auto-filters, formatting, and dynamic expansion
Tables add auto-filters, structured references, automatic formatting, and auto-expansion.
Tables also include calculated columns, Total Row, banding, and are automatically included in charts and PivotTables.
Question 2: How does multi-level sorting work in Excel?
- Only one level allowed
- Each level sorts within groups created by the previous level (Correct answer)
- Multiple sorts overwrite each other
- Each level sorts independently
Correct answer: Each level sorts within groups created by the previous level
Multi-level sorting creates a hierarchy: first level sorts all data, subsequent levels sort within groups.
Up to 64 levels. Custom lists enable non-alphabetical sorting. Case-sensitive sorting available under Options.
Question 3: What is the Total Row in an Excel Table?
- Shows table dimensions
- A summary row with dropdown aggregate functions for each column (Correct answer)
- Header row duplicate
- Locks table from editing
Correct answer: A summary row with dropdown aggregate functions for each column
Total Row adds summary functions (SUM, AVERAGE, COUNT, etc.) at the bottom of each column.
Uses SUBTOTAL function internally, so totals always reflect visible (filtered) data. Enable with Ctrl+Shift+T.
Question 4: How does Advanced Filter differ from AutoFilter?
- Advanced Filter is slower
- Can extract to another location, use complex AND/OR criteria, find unique records (Correct answer)
- Only works with Tables
- Identical functionality
Correct answer: Can extract to another location, use complex AND/OR criteria, find unique records
Advanced Filter supports complex multi-field AND/OR criteria, copying results elsewhere, and unique records.
Set up criteria range with column headers and conditions. Different rows = OR logic. Same row = AND logic.
Question 5: What are Slicers for Tables?
- Split tables into pieces
- Visual filter buttons for interactive single-click filtering (Correct answer)
- Remove unwanted columns
- PivotTable only
Correct answer: Visual filter buttons for interactive single-click filtering
Slicers provide clickable buttons that instantly filter Table data.
Available since Excel 2013. Click for single filter, Ctrl+click for multiple. Easier than dropdown filters for dashboards.
Question 6: How does SUBTOTAL differ from regular aggregate functions?
- SUBTOTAL is slower
- Ignores hidden/filtered rows and supports 11 different functions (Correct answer)
- Only numeric data
- No difference
Correct answer: Ignores hidden/filtered rows and supports 11 different functions
SUBTOTAL can ignore hidden rows, filtered rows, and nested SUBTOTAL results.
Function numbers 1-11 include hidden rows; 101-111 exclude them. Both series always exclude filtered rows.
Microsoft Excel Managing Tables and Data 2
What happens when you convert a range to an Excel Table (Ctrl+T)?