Module 2 Test: Manage and Format Data

Course: MO-211: Microsoft Excel Expert Topic: Comprehensive review of Lessons 2.0 – 2.14

This test combines exam-style multiple choice with hands-on micro-tasks. Try every question with your notes closed first; reveal the answer key only after attempting all of them. Practice file: practice/module-2-sales-data.xlsx.

Section A: Lessons 2.0 – 2.3 — Names, Flash Fill, Fill Series, RANDARRAY

Q1. Name Box Scope

You select B2:B100, click in the Name Box (left of the formula bar), type Sales, and press Enter. What is the scope of the new name? - A) Worksheet — only visible from the active sheet. - B) Workbook — visible from every sheet in the workbook. - C) Application — visible from every open workbook. - D) Selection — only valid until you click somewhere else.

Q2. Create from Selection

You have a 2-column lookup table with Country labels in column A and the corresponding Capital values in column B (rows 2-30). You select A2:B30 and pick Formulas > Create from Selection with Left column ticked. What did you just build? - A) A single workbook-scoped name called LookupTable pointing at the whole range. - B) One name per row whose name equals the country label and whose value is the capital cell on its right. - C) One name per column (one for Country, one for Capital). - D) Nothing — Create from Selection requires Top row to be checked as well.

Q3. Invalid Name

Which of the following is rejected by Excel as an invalid defined name? - A) _Tax_Rate_2026 - B) Q.1.Sales - C) Q1 - D) MaxDiscount

Q4. Name Manager Filter

You inherit a workbook with hundreds of named ranges and suspect several point at deleted sheets. Which Name Manager filter exposes them in one click? - A) Names Scoped to Workbook - B) Table Names - C) Names with Errors - D) Defined Names

Q5. Paste Name Dialog

You are part-way through typing =SUM( and want to insert an existing named range without remembering the exact spelling. Which key opens the Paste Name dialog? - A) F2 - B) F3 - C) F5 - D) Ctrl + F3

Q6. Flash Fill Trigger

You typed the desired output for the first row of a name-cleanup column. Which keyboard shortcut tells Excel to infer the pattern and fill the rest of the column? - A) Ctrl + F - B) Ctrl + E - C) Ctrl + D - D) Ctrl + Enter

Q7. Flash Fill Limitation

After running Flash Fill on column B from source data in column A, you change a value in column A. What happens to column B? - A) Column B updates automatically — Flash Fill writes formulas behind the scenes. - B) Column B does not update — Flash Fill produces static values, not formulas. - C) Excel asks whether to re-run Flash Fill. - D) The column B value turns red until you press F9.

Q8. Fill Series — Growth

You enter 100 in A1 and want column A to show 100, 110, 121, 133.1, … — a constant 10% growth per row. Which Type in the Series dialog do you pick? - A) Linear - B) Date - C) Growth - D) AutoFill

Q9. Linked Data Type — Dot Operator

Cell A2 has been converted to a Stocks linked data type for ticker MSFT. Which formula correctly returns its current price? - A) =A2.Price - B) =GETSTOCK(A2, "Price") - C) =VLOOKUP(A2, Stocks, 2, FALSE) - D) =PRICE(A2)

Q10. RANDARRAY Output

You enter =RANDARRAY(4, 2, 1, 100, TRUE). What is in the spilled output? - A) A single random integer between 1 and 100. - B) A 4-row by 2-column grid of random integers between 1 and 100. - C) A 2-row by 4-column grid of random decimals between 1 and 100. - D) A 4-row by 2-column grid of random decimals between 1 and 100.

Section B: Lessons 2.4 – 2.5 — Custom Number Formats and Data Validation

Q11. Section Order

A custom format code with four sections is interpreted in what order? - A) Text ; Zero ; Negative ; Positive - B) Negative ; Positive ; Zero ; Text - C) Positive ; Negative ; Zero ; Text - D) Positive ; Zero ; Negative ; Text

Q12. Conditional Format Code

Which custom format code displays values >= 1,000,000 as 1.5M, values >= 1,000 as 2.5K, and everything else as a plain integer? - A) 0.0,,"M";0.0,"K";0 - B) [>=1000000]0.0,,"M";[>=1000]0.0,"K";0 - C) [Million]0.0;[Thousand]0.0;0 - D) 0.0M;0.0K;0

Q13. Hide All Cell Content

Which custom format code visually hides every value (positive, negative, zero, and text) while leaving the underlying value intact? - A) "";"";"";"" - B) ;;; - C) [Hidden] - D) *;*;*;*

Q14. Color Code Position

Where in a custom number format code must a colour name like [Red] appear to take effect? - A) Anywhere inside the section. - B) As the first item in a section, in square brackets. - C) After the digit code, separated by a space. - D) Wrapped in double quotes after the section.

Q15. Data Validation Enforcement

You configured a List validation but users can still type values that are not in the list — and Excel only warns them. Which Error Alert style was used? - A) Stop - B) Warning - C) Information - D) None — list validation cannot be bypassed.

Q16. Cascading Dropdowns

You built a Country dropdown in A2 and want B2 to show only the cities for that country. Each country's city list is a named range whose name matches the country. What goes in the City cell's Data Validation Source field? - A) =A2 - B) =OFFSET(A2,0,1) - C) =INDIRECT(A2) - D) =VLOOKUP(A2, CityTable, 2, FALSE)

Q17. Custom Validation Formula

You apply Data Validation to A2:A100 with Allow > Custom to forbid duplicate IDs. Which formula belongs in the Formula box? - A) =A2:A100<>"" - B) =COUNTIF($A$2:$A$100,A2)=1 - C) =UNIQUE(A2:A100) - D) =ISNUMBER(A2)

Q18. Find Validated Cells

You inherit a sheet and want to highlight every cell that currently has any data-validation rule applied. Which command does this in one click? - A) Home > Find & Select > Constants - B) Home > Find & Select > Data Validation - C) Data > Data Validation > Circle Invalid Data - D) Review > Track Changes

Section C: Lessons 2.6 – 2.8 — Group, Subtotals, AGGREGATE, Remove Duplicates

Q19. SUBTOTAL with Filtered Rows

A worksheet has an AutoFilter applied. Which formula gives the sum of column D for only the visible rows? - A) =SUM(D2:D500) - B) =SUMIF(D2:D500,">0") - C) =SUBTOTAL(9, D2:D500) - D) =SUBTOTAL(109, D2:D500)

Q20. Subtotal Menu Pre-flight

Before running Data > Subtotal to group sales by Department, what must you do first? - A) Convert the range to an Excel Table. - B) Sort the data by the Department column. - C) Apply a filter to the Department column. - D) Insert a header row.

Q21. AGGREGATE Options for Errors

Your dataset contains some #N/A lookup errors but you still need the running total of column B2:B500, ignoring rows hidden by an AutoFilter AND the error cells. Which formula is correct? - A) =SUM(B2:B500) - B) =AGGREGATE(9, 5, B2:B500) (option 5 = ignore hidden + nested SUBTOTAL/AGGREGATE) - C) =AGGREGATE(9, 7, B2:B500) (option 7 = ignore hidden + errors) - D) =SUMIFS(B2:B500, B2:B500, ">0")

Q22. AGGREGATE Function Number

Which function_num in AGGREGATE lets you compute LARGE, that is, "the Nth-largest value in the visible rows"? - A) 9 - B) 12 - C) 14 - D) 17

Q23. AGGREGATE vs. SUBTOTAL

Which capability does AGGREGATE offer that SUBTOTAL does not? - A) Ignoring rows hidden by an AutoFilter. - B) Ignoring nested SUBTOTAL rows. - C) Computing LARGE, SMALL, and PERCENTILE while ignoring errors. - D) Returning a Total Row at the bottom of an Excel Table.

Q24. Remove Duplicates Behaviour

In the Remove Duplicates dialog, you check only the Customer ID column. What does Excel do? - A) It deletes any row whose Customer ID matches another row, ignoring all other columns. - B) It deletes any row where Customer ID matches AND every other column also matches. - C) It returns a new list of unique Customer IDs in a different range. - D) Nothing — at least two columns must be checked.

Q25. Group and Outline

You manually group rows 5-15 via Data > Group. Which keyboard shortcut collapses every grouped section on the sheet to its top-most outline level? - A) Ctrl + 1 - B) Alt + Shift + Left - C) The numbered outline buttons (1, 2, 3) at the top-left corner of the grid. - D) Ctrl + 8 (toggles the outline symbols on/off, not the level).

Section D: Lessons 2.9 – 2.11 — Conditional Formatting

Q26. Format Entire Row

You want to highlight the entire row A2:E2 red whenever $E2 (the status column) equals "Overdue". The selected range is A2:E100. Which formula belongs in the Use a formula rule? - A) =E2="Overdue" - B) =$E$2="Overdue" - C) =$E2="Overdue" - D) =E$2="Overdue"

Q27. Stop If True

Two conditional formatting rules apply to the same cells. With Stop If True enabled on the top rule, what happens when that top rule evaluates to TRUE? - A) Both rules' formats are applied and stacked. - B) Only the top rule's format applies; the lower rule is skipped. - C) Only the lower rule's format applies. - D) Excel raises an error.

Q28. Manage Rules — Scope

In Conditional Formatting > Manage Rules, the Show formatting rules for dropdown defaults to which scope? - A) This Worksheet - B) This Workbook - C) Current Selection - D) Selected PivotTable

Q29. Icon Set Reverse Order

You apply a 3-arrow Icon Set where green/up should mean "good" but your data uses lower = better (e.g., expense ratio). What setting fixes the icons without changing the data? - A) Right-click an icon and pick Flip Icons. - B) Open the rule and tick Reverse Icon Order. - C) Re-apply the rule with the range entered backwards. - D) Convert the column to a Data Bar instead.

Section E: Lessons 2.12 – 2.14 — Tables, Advanced Filter, Power Query

Q30. Tables Make PivotTables Auto-Grow

You build a PivotTable on a static range A1:F500. Two weeks later, ten new rows are added at row 501. How do you make the PivotTable include the new rows automatically going forward? - A) Right-click the PivotTable and pick Refresh. - B) Convert the source range to an Excel Table, then rebuild the PivotTable on the Table. - C) Resize the PivotTable manually each refresh. - D) Use Change Data Source every time data is added.

Q31. Structured References

Inside an Excel Table named Sales, what does Sales[@Amount] refer to? - A) The header cell of the Amount column. - B) The total of the Amount column. - C) The Amount value on the current row. - D) The entire Amount column including header.

Q32. Total Row Formula

You tick Total Row on a Table named Orders and pick Sum from the Revenue column dropdown. Which formula does Excel actually write into the Total Row cell? - A) =SUM(Orders[Revenue]) - B) =SUBTOTAL(9, Orders[Revenue]) - C) =SUBTOTAL(109, [Revenue]) - D) =AGGREGATE(9, 7, Orders[Revenue])

Q33. Convert to Range

You click Table Design > Convert to Range on a styled Table. What survives the conversion? - A) Only the data — banded-row formatting is removed. - B) Data and the Table name; structured references continue to work. - C) Data and the banded-row formatting (as plain shading); the Table object and structured references are lost. - D) Nothing — Excel deletes the data along with the Table.

Q34. Advanced Filter — Logic

A criteria range looks like this:

Region	Amount
North
	>5000

What does Advanced Filter return? - A) Rows where Region = North AND Amount > 5000. - B) Rows where Region = North OR Amount > 5000. - C) Rows where Region <> North AND Amount > 5000. - D) An error — the criteria range is malformed.

Q35. Power Query Merge — Anti Join

You need to find every customer in the master Customers query who placed no orders in the Orders query. Which Join Kind in Merge Queries is the cleanest choice? - A) Left Outer - B) Inner - C) Left Anti - D) Full Outer

Q36. Append vs. Merge

You have twelve monthly sales tables (Sales_Jan … Sales_Dec), each with the same column layout. Which Power Query command stacks them into a single year-to-date table? - A) Merge Queries with Inner join. - B) Append Queries. - C) Group By with Sum aggregation. - D) Pivot Column on Month.

Q37. Power Query Refresh

A query loaded as a Table is showing yesterday's data even though the source CSV was updated this morning. What does the user need to do? - A) Convert the Table to a Range, then back to a Table. - B) Right-click the Table > Refresh, or click Data > Refresh All (Ctrl + Alt + F5). - C) Re-import the CSV from scratch — queries cannot be re-run. - D) Wait — Excel refreshes external queries every 60 seconds automatically.

Q38. Only Create Connection

You build a staging query that exists only to be merged into another query. Which Close & Load To... option is correct? - A) Table on a new worksheet. - B) PivotTable Report. - C) Only Create Connection. - D) Add to the Data Model + Table.

Part 2: True or False

Q39. RANDARRAY() is a volatile function — its values recalculate every time the workbook recalculates. (True / False)

Q40. A custom number format can use up to 5 condition-tested sections. (True / False)

Q41. Home > Find & Select > Data Validation highlights every cell in the active sheet that has any data validation rule applied. (True / False)

Q42. The Data > Subtotal menu feature works on Excel Tables exactly the same as on plain ranges. (True / False)

Q43. When you convert an Excel Table back to a range via Table Design > Convert to Range, the banded-row formatting is removed automatically. (True / False)

Q44. In an Advanced Filter criteria range, criteria placed on the same row are combined with AND, and criteria on different rows are combined with OR. (True / False)

Q45. Editing a name in the Name Manager to change its spelling automatically rewrites every formula in the workbook that referenced the old name. (True / False)

Q46. The headers in a Power Query Append Queries operation must be in identical order across the source queries; mismatched column orders cause #REF! errors. (True / False)

Q47. Power Query's Applied Steps can be reordered by dragging — the same way you might rearrange commits in a feature branch. (True / False)

Q48. A Linked Data Type cell continues to display its enriched value when opened in Excel 2019 perpetual. (True / False)

Part 3: Hands-On Micro-Tasks

Treat each task like a real exam project file. Steps are listed in the answer key. The reference workbook is practice/module-2-sales-data.xlsx.

T1. Define a workbook-scoped name. On the Sales sheet, select D2:D500 (the Amount column). Define a new workbook-scoped named range called Amounts using Formulas > Define Name and add the comment "Sales amount column for module-2 quiz." Verify it appears in the Name Manager (Ctrl + F3) under Workbook scope.

T2. Custom number format with three branches. On the Sales sheet, in the Amount column (D2:D500), apply a custom number format that: - Displays positive values in green with 2 decimals and a thousands separator. - Displays negative values in red, in parentheses, with 2 decimals. - Displays zero as a single dash (-).

T3. Cascading dropdowns. On the Form sheet, build a cascading dropdown: - Cell B2 is a Country picker (USA, France, Germany). - Cell C2 lists only the cities of the selected country. - City lists are on the Lookups sheet, one column per country, with the column header used as the named range name.

T4. Excel Table + PivotTable that auto-grows. On the Orders sheet, convert A1:F500 to an Excel Table named Orders. Add a Total Row that sums the Revenue column. Build a PivotTable from the Table on a new sheet showing Revenue by Region.

T5. Subtotals. On the Expenses sheet, insert automatic subtotals that sum the Amount column at every change in Department. Make sure the source data is sorted correctly first.

T6. AGGREGATE with errors. On the Inventory sheet, the Cost column contains scattered #N/A errors. In cell H2, write a formula that returns the average of Cost ignoring those errors. The result should match the cleaned mean.

T7. Advanced Filter — Copy Unique to Another Location. On the Customers sheet, copy every row where Region = "East" OR Sales > 10000 into a new area starting at H1. Output unique records only.

T8. Formula-based conditional formatting. On the Tasks sheet, format the entire row A2:E2 (and down through row 100) with a light yellow fill whenever the value in column D (Status) equals "In Progress".

T9. Validation audit and clear. On the Inventory sheet, find every cell that currently has a data validation rule, then clear all data validation from the entire sheet without affecting the cell values.

T10. Power Query — Unpivot months. On the MonthlyMatrix sheet you have one row per region with twelve month columns (Jan … Dec). Use Power Query From Table/Range to Unpivot the month columns into a long-format table with three columns: Region, Month, Amount. Load the result as a new Table on a sheet named MonthlyLong.

Show answers

## Section A — Lessons 2.0–2.3 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q1** | **B** | Names created from the Name Box are always **workbook-scoped**. Use **Define Name** if you need a sheet-scoped name. | | **Q2** | **B** | With **Left column** ticked, Excel creates one name per row using the left-column label, each pointing at the value cell on its right. | | **Q3** | **C** | `Q1` collides with the cell-address rule — `Q1` is a real cell address. Letters `R`, `r`, `C`, `c` alone are also forbidden. | | **Q4** | **C** | The **Names with Errors** filter exposes named ranges whose `Refers To` evaluates to an error — perfect for inherited workbook hygiene. | | **Q5** | **B** | `F3` opens the **Paste Name** dialog mid-formula. `F2` edits the cell; `Ctrl + F3` opens Name Manager. | | **Q6** | **B** | `Ctrl + E` is the dedicated Flash Fill shortcut. | | **Q7** | **B** | Flash Fill output is purely static values. Use `LEFT/RIGHT/MID` or `TEXTSPLIT` for live, formula-based transformations. | | **Q8** | **C** | **Growth** uses multiplication (compound) rather than addition (Linear) — `100 * 1.10 = 110`, `110 * 1.10 = 121`, etc. | | **Q9** | **A** | The dot operator on a linked-data-type cell pulls a specific field. Field names with spaces use `[ ]`: `=A2.[52 week high]`. | | **Q10** | **B** | `RANDARRAY(rows, columns, min, max, integer)` — `4, 2, 1, 100, TRUE` produces a 4×2 grid of integers from 1 to 100. | ## Section B — Lessons 2.4–2.5 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q11** | **C** | The format-code section order is **Positive ; Negative ; Zero ; Text**. | | **Q12** | **B** | Bracketed conditions `[>=N]` override default sign-based routing. The trailing comma in `0.0,,` scales by millions. | | **Q13** | **B** | The format `;;;` defines four empty sections — every value type (positive, negative, zero, text) renders blank, but the underlying value is unchanged. | | **Q14** | **B** | Colour codes such as `[Red]` and `[Blue]` must be the **first** bracketed item in their section. | | **Q15** | **B** | **Warning** allows users to proceed past the alert. **Stop** is the only style that strictly enforces the rule. | | **Q16** | **C** | `=INDIRECT(A2)` returns the named range whose name equals the parent value, producing the dependent list. | | **Q17** | **B** | Custom validation formulas evaluate per cell using the top-left reference. `COUNTIF=1` rejects any second occurrence of a value. | | **Q18** | **B** | **Find & Select > Data Validation** highlights every validated cell on the active sheet. **Constants** picks up hard-coded values, not validation rules. | ## Section C — Lessons 2.6–2.8 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q19** | **D** | Function numbers `101–111` exclude rows hidden by filter; `109` is SUM excluding hidden rows. `9` includes filtered rows. | | **Q20** | **B** | The Subtotal feature inserts a subtotal each time the grouping column changes, so the data must be sorted first. | | **Q21** | **C** | Option 7 ignores both hidden rows and error values. Option 5 ignores hidden rows but not errors. AGGREGATE function 9 = SUM. | | **Q22** | **C** | `function_num` 14 = `LARGE`, 15 = `SMALL`, 16/18 = `PERCENTILE.INC/EXC`, 17/19 = `QUARTILE.INC/EXC`. These require the optional `k` argument. | | **Q23** | **C** | `SUBTOTAL` cannot ignore errors and cannot compute `LARGE`/`SMALL`/`PERCENTILE`/`QUARTILE`. `AGGREGATE` adds both capabilities. | | **Q24** | **A** | Only checked columns define uniqueness. With only Customer ID checked, any row whose ID matches another is removed regardless of other column values. | | **Q25** | **C** | The numbered outline buttons (1, 2, 3 …) above the row headings collapse to that outline level. `Ctrl + 8` toggles the outline symbols themselves. | ## Section D — Lessons 2.9–2.11 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q26** | **C** | The column reference must be locked (`$E`), but the row reference must shift down (`E2`, `E3`, …). `$E2` does both. | | **Q27** | **B** | **Stop If True** prevents Excel from evaluating any lower-priority rules when the current rule fires. | | **Q28** | **C** | Manage Rules defaults to **Current Selection**. Switch to **This Worksheet** to see every rule on the active sheet. | | **Q29** | **B** | The **Reverse Icon Order** checkbox in the rule's edit dialog flips icon-to-value mapping without touching the data. | ## Section E — Lessons 2.12–2.14 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q30** | **B** | A PivotTable built on a Table tracks the Table's auto-extending range; static ranges do not grow. | | **Q31** | **C** | The `@` operator in structured references means "this row." | | **Q32** | **C** | The Total Row writes `SUBTOTAL(109, [Column])` so that totals respond to filters automatically. | | **Q33** | **C** | Convert to Range strips the Table object and structured references but **keeps** any cell-level shading the style applied. | | **Q34** | **B** | Rows in a criteria range are joined by **OR**. Region = North on row 1, Amount > 5000 on row 2 = OR. | | **Q35** | **C** | A **Left Anti** join keeps the left rows that have no match on the right — exactly the "customers with no orders" pattern. | | **Q36** | **B** | **Append Queries** is Power Query's `UNION ALL`. Merge is for joins (one column matched against another), not for stacking. | | **Q37** | **B** | Queries are static until refreshed. Right-click > Refresh, or use `Data > Refresh All` (`Ctrl + Alt + F5`). | | **Q38** | **C** | **Only Create Connection** stores the staging query without a Table on the grid — perfect for queries that only feed other queries. | ## Part 2 — True/False Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q39** | **True** | Like `RAND()` and `NOW()`, `RANDARRAY()` recalculates on every workbook calc. Use Paste Special > Values to freeze. | | **Q40** | **False** | The maximum is **3 condition-tested sections + 1 default = 4 sections total**. Use Conditional Formatting for more branches. | | **Q41** | **True** | This is the standard audit shortcut — it highlights every validated cell on the active sheet. | | **Q42** | **False** | The **Data > Subtotal** command is **disabled** for Excel Tables. Convert to a range first, or use the Table's own Total Row. | | **Q43** | **False** | Convert to Range removes the Table object but **keeps** the formatting as plain cell shading. | | **Q44** | **True** | This is the canonical AND-across / OR-down rule for Advanced Filter criteria ranges. | | **Q45** | **True** | Editing a name's spelling rewrites every formula, conditional-formatting rule, and validation source that referenced the old name. VBA string lookups (`Range("Sales")`) are not rewritten. | | **Q46** | **False** | Append matches columns by **name**, not position. Mismatched columns are filled with `null`, not `#REF!`. | | **Q47** | **True** | Applied Steps can be reordered by drag-and-drop — useful when a transform was inserted out of order. | | **Q48** | **False** | Linked Data Types require Microsoft 365. In Excel 2019 perpetual, the icons render as static text and dot-operator formulas resolve to `#FIELD!`. | ## Part 3 — Hands-On Solutions **T1. Define a workbook-scoped name.** 1. Select `D2:D500` on the `Sales` sheet. 2. **Formulas > Define Name**. 3. **Name:** `Amounts`. **Scope:** `Workbook`. **Comment:** `Sales amount column for module-2 quiz`. **Refers to:** confirm `=Sales!$D$2:$D$500`. **OK**. 4. `Ctrl + F3` to open Name Manager — verify `Amounts` appears with Scope = `Workbook`. **T2. Custom number format with three branches.** Select the Amount column → `Ctrl + 1` → **Number** tab → **Custom** → in the **Type** field enter:

[Green]#,##0.00;[Red](#,##0.00);"-"

**T3. Cascading dropdowns.** 1. On `Lookups`, select the row of country labels and use **Formulas > Create from Selection** with **Top row** ticked. This auto-creates names `USA`, `France`, `Germany`, each pointing at the city column below the label. 2. Click `B2` on `Form` → **Data > Data Validation** → **Allow: List** → **Source:** select the row of country labels. 3. Click `C2` → **Data > Data Validation** → **Allow: List** → **Source:** `=INDIRECT(B2)` → **OK**. Excel may warn that the source evaluates to an error if `B2` is currently empty — accept and move on. **T4. Excel Table + PivotTable that auto-grows.** 1. Click any cell in `A1:F500` → `Ctrl + T` → confirm "My table has headers" → **OK**. 2. **Table Design** > **Table Name** → type `Orders` → `Enter`. 3. **Table Design** > tick **Total Row** → click the Revenue total cell → pick **Sum** from the dropdown. 4. With a cell inside `Orders` selected, **Insert > PivotTable** → New Worksheet → **OK**. Drag `Region` to **Rows** and `Revenue` to **Values**. The PivotTable's source is now `Orders` — adding rows to the Table will be picked up on **Refresh** (`Alt + F5`). **T5. Subtotals.** 1. Click anywhere in the data → **Data > Sort** → sort by `Department` ascending. 2. **Data > Outline > Subtotal**. 3. **At each change in:** `Department`. **Use function:** `Sum`. **Add subtotal to:** tick `Amount`. Click **OK**. **T6. AGGREGATE with errors.** In `H2` enter:

=AGGREGATE(1, 6, Cost)

where `Cost` is the structured reference (or use `Inventory!$D$2:$D$N`). Function `1` = AVERAGE, option `6` = ignore errors. Result matches the mean computed after deleting `#N/A` rows. **T7. Advanced Filter — Copy Unique to Another Location.** 1. Build a criteria range, e.g., `K1:L3`: | Region | Sales | | :--- | :--- | | East | | | | >10000 | 2. Click any cell inside the source list. 3. **Data > Sort & Filter > Advanced**. 4. Choose **Copy to another location**. 5. Confirm **List range** auto-detected. Set **Criteria range** to `K1:L3`. Set **Copy to** to `H1`. Tick **Unique records only**. Click **OK**. **T8. Formula-based conditional formatting.** 1. Select `A2:E100`. 2. **Home > Conditional Formatting > New Rule...** 3. Pick **Use a formula to determine which cells to format**. 4. Formula: `=$D2="In Progress"`. 5. Click **Format...** → **Fill** tab → light yellow → **OK** → **OK**. **T9. Validation audit and clear.** 1. **Home > Find & Select > Data Validation** — highlights which cells have rules (audit step). 2. Click the corner triangle (above row 1, left of column A) to select the entire sheet. 3. **Data > Data Validation > Data Validation...** — Excel warns that the selection contains different rule types; click **OK**. 4. In the dialog, click **Clear All** → **OK**. Cell values remain; rules are gone. **T10. Power Query — Unpivot months.** 1. Click any cell in the `MonthlyMatrix` data. 2. **Data > From Table/Range** (convert to Table when prompted). 3. In the Power Query Editor, select the twelve month columns (click `Jan`, `Shift + click` `Dec`). 4. **Transform > Unpivot Columns**. The columns become two new columns named `Attribute` and `Value`. 5. Right-click `Attribute` > **Rename** → `Month`. Right-click `Value` > **Rename** → `Amount`. 6. **Home > Close & Load To...** → **Table** → **New worksheet** → **OK**. Rename the new sheet `MonthlyLong`.

Exam Tip — Module 2 Coverage Map:

Lesson	Most-Tested Concept
2.0	Name Box = workbook scope only; Create from Selection for bulk naming; `F3` for Paste Name; `Ctrl + F3` for Name Manager.
2.1	`Ctrl + E` for Flash Fill; output is static values; Linked Data Types use the dot operator (`A2.Price`).
2.2	Growth type uses multiplication; Linear uses addition.
2.3	`RANDARRAY(rows, cols, min, max, integer)` is volatile.
2.4	Section order = `Positive ; Negative ; Zero ; Text`; max 3 conditional sections + default; `[Color]` must be first; `;;;` hides everything.
2.5	Stop is the only enforcing Error Alert; cascading dropdowns use `=INDIRECT(parent)`; Find & Select > Data Validation lists every validated cell.
2.6	Numbered outline buttons collapse to a level; `Ctrl + 8` toggles outline symbols.
2.7	`SUBTOTAL` 1–11 vs 101–111; Sort first before Data > Subtotal; AGGREGATE option 7 = ignore hidden + errors; functions 14–19 add `LARGE`/`SMALL`/`PERCENTILE`/`QUARTILE`.
2.8	Remove Duplicates uniqueness is defined by the checked columns only.
2.9–2.11	`=$E2="Overdue"` for whole-row formatting; Stop If True; Manage Rules defaults to Current Selection; Reverse Icon Order for "lower = better" data.
2.12	`Ctrl + T`; Total Row writes `SUBTOTAL(109,…)`; PivotTables on a Table auto-grow; Convert to Range keeps formatting but loses the Table.
2.13	AND across rows, OR down rows; Copy to another location + Unique records only.
2.14	Append = stack (UNION ALL); Merge = join; Left Anti = "no match on the right"; Only Create Connection for staging queries; Applied Steps are draggable.