Module 4 Test: Manage Advanced Charts and Tables

Course: MO-211: Microsoft Excel Expert Topics covered: 4.2 Advanced charts (Combo, Histogram, Pareto, Box & Whisker, Waterfall, Funnel, Sunburst) · 4.3-4.4 PivotTables · 4.5 Slicers and Timelines · 4.6 PivotTable Grouping · 4.7 Calculated Fields · 4.8 PivotCharts · 4.9 Sparklines · 4.10 Trendlines

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-4-pivots-and-charts.xlsx.

Section A: Lesson 4.2 — Advanced Chart Types

Q1. Combo Chart Convention

On a Combo chart with Revenue in the millions and Margin % as a percentage, what is the standard professional layout? - A) Both as columns on one axis. - B) Revenue as a Line, Margin % as a Column on the secondary axis. - C) Revenue as a Column on the primary axis, Margin % as a Line on the secondary axis. - D) Both as lines, Margin % on a logarithmic axis.

Q2. Pareto vs Histogram

You are asked to "create a Pareto chart of defect counts by category" from a column of category labels and a column of defect counts. How does the resulting chart differ from a regular Histogram inserted from the same data? - A) The Pareto chart uses different colors but the same bar order. - B) The Pareto chart sorts bars in descending order and overlays a cumulative percentage line on a secondary axis. - C) The Pareto chart automatically applies a Box & Whisker overlay. - D) Pareto charts are only available under Insert > Recommended Charts, not under Statistic Charts.

Q3. Histogram Overflow Bin

A task says: "Group every value above 100 into a single right-most bar." Which option in the Format Axis pane do you enable? - A) Number of bins - B) Bin width - C) Overflow bin - D) Underflow bin

Q4. Histogram — By Category

Your source data is a column of department names (Sales, Marketing, Ops, …) with counts. You insert a Histogram and want one bar per department label rather than numeric bins. Which Bin Mode in Format Axis do you pick? - A) Automatic - B) Bin width - C) Number of bins - D) By Category

Q5. Box and Whisker — Median Mode

Right-click a Box and Whisker series and you find Inclusive Median and Exclusive Median options. What do these control? - A) Whether the median line is drawn at all. - B) Which quartile-calculation method the box uses (the boundary of Q1/Q3 differs depending on whether the median value itself is included in each half). - C) Whether outliers are shown. - D) The colour of the whiskers.

Q6. Waterfall "Set as Total"

The last bar of a Waterfall chart is floating above the axis as an "Increase" instead of resting on the baseline as the final total. What do you do? - A) Right-click the chart area > Change Chart Type. - B) Click the bar twice (until only that single point is selected) > right-click > Set as Total. - C) Sort the source data in descending order. - D) Add a new "Total" row to the source data.

Q7. Funnel Chart Source Layout

A funnel chart you inserted is showing only one bar instead of a stepped funnel. The most likely cause is: - A) You forgot to apply a chart style. - B) The source data has multiple value columns instead of a single value column with category labels. - C) The data is sorted ascending instead of descending. - D) You inserted the chart from the wrong ribbon tab.

Q8. Sunburst Hierarchy

A Sunburst chart requires what kind of source data? - A) A single column of values with a single column of dates. - B) At least two levels of hierarchical category labels (e.g., Region → Country → City) plus a value column. - C) An Excel Table with structured references. - D) Exactly three numeric columns.

Section B: Lessons 4.3 – 4.4 — PivotTables

Q9. Defer Layout Update

You are dragging fields into a PivotTable built on a 2-million-row Power Pivot model and every drag causes a long delay. What is the recommended fix? - A) Convert the model to a regular Excel Table. - B) Tick Defer Layout Update at the bottom of the PivotTable Fields pane, build the layout, then click Update. - C) Disable Refresh on Open in PivotTable Options. - D) Switch to Classic PivotTable layout under Display options.

Q10. GETPIVOTDATA Auto-Generation

You type = and click a cell inside a PivotTable on another sheet. Excel writes a verbose GETPIVOTDATA(...) formula instead of =Sheet1!B5. Where do you turn this behavior off? - A) File > Options > Advanced > Editing options - B) Right-click the cell > Format Cells - C) PivotTable Analyze tab > Options dropdown (next to PivotTable Name) > untick Generate GetPivotData - D) Design tab > Report Layout > Show in Tabular Form

Q11. GETPIVOTDATA Syntax

Which GETPIVOTDATA formula correctly returns the West / 2025 cell of a PivotTable rooted at $A$3, with a Sum of Sales value field? - A) =GETPIVOTDATA(Sheet1!$A$3, "Sum of Sales", "Region", "West", "Year", 2025) - B) =GETPIVOTDATA("Sum of Sales", $A$3, "Region", "West", "Year", 2025) - C) =GETPIVOTDATA($A$3, "West", "2025") - D) =GETPIVOTDATA("Region=West, Year=2025")

Q12. Drill-Through (Show Details)

A manager asks "where did this $48,200 number come from?" while looking at a PivotTable. What is the fastest way to surface the underlying source rows? - A) Refresh the PivotTable. - B) Double-click the value cell — Excel inserts a new sheet containing the underlying records. - C) Open Power Query and re-run the source query. - D) Use the GETPIVOTDATA function to look up the value.

Q13. Show Values As — % of Column Total

You need each cell in a PivotTable to display its share of the column total (a percent). Which path is correct? - A) Right-click value > Value Field Settings > Summarize Values By tab > Sum. - B) Right-click value > Value Field Settings > Show Values As tab > % of Column Total. - C) Format the cells as Percent via Ctrl + 1. - D) Insert a calculated field =Sum / TotalSum.

Q14. Renaming a Value Field

You try to rename a value field from Sum of Sales to Sales and Excel rejects the new name. Why? - A) Value field names cannot exceed 8 characters. - B) Sales already exists as a column header in the source data; the new field name must differ. A common workaround is a trailing space: "Sales ". - C) PivotTable field names must contain a function name. - D) PivotTable field names must be lower-case.

Q15. Change Data Source

A PivotTable was built on a static range A1:F500. You added 50 new rows and want the PivotTable to include them. Which action is required (assuming you cannot convert the source to a Table)? - A) PivotTable Analyze > Refresh — that is sufficient. - B) PivotTable Analyze > Change Data Source — point to the new wider range. - C) Re-create the PivotTable from scratch. - D) Use GETPIVOTDATA to pull from the new rows.

Q16. Disable Drill-Through

On a shared dashboard you want to prevent users from double-clicking PivotTable values to see source rows. Where do you turn this off? - A) PivotTable Analyze > Options > Data tab → untick Enable show details. - B) Hide the source worksheet. - C) Convert the PivotTable to a regular range. - D) Drill-through cannot be disabled.

Section C: Lesson 4.5 — Slicers and Timelines

Q17. Timeline vs Date Slicer

Why is a Timeline preferred over a regular Slicer when filtering a PivotTable by a date column? - A) Slicers cannot be applied to date fields at all. - B) Timelines automatically refresh the source data; slicers do not. - C) A Timeline provides a continuous range scrubber with selectable granularity (Years/Quarters/Months/Days), while a date Slicer produces one button per unique date. - D) Timelines support Report Connections; Slicers do not.

Q18. Timeline Granularity

Which dropdown switches a Timeline between Years / Quarters / Months / Days views? - A) The Time Level dropdown in the top-right corner of the Timeline. - B) Slicer Settings > Sort items. - C) PivotTable Analyze > Field Settings. - D) Right-click the Timeline > Group By.

Q19. Insert Timeline Greyed Out

You click PivotTable Analyze > Insert Timeline and the date field you expected does not appear in the dialog. What is the most likely cause? - A) The PivotTable does not have any Row fields. - B) The source date column is being read as Text, not as a real date type. - C) The Timeline feature is disabled in Trust Center. - D) Timelines require Microsoft 365 only.

Q20. Report Connections

You need one Slicer to filter two PivotTables that are both built on the same data source. Where do you wire it up? - A) Right-click the Slicer > Report Connections > tick the second PivotTable. - B) Insert a separate Slicer for each PivotTable. - C) PivotTable Analyze > Insert Slicer twice with the same field. - D) Slicers can only filter one PivotTable; use a Timeline instead.

Q21. Slicer Columns Layout

You want a Slicer to render as a horizontal button bar with 4 buttons across rather than a vertical list. Which control do you change? - A) Slicer tab > Buttons group > Columns = 4. - B) Right-click > Slicer Settings > Multi-Column. - C) Drag the Slicer wider — Excel auto-calculates the column count. - D) Format Slicer > Layout > Direction = Horizontal.

Section D: Lessons 4.6 – 4.7 — Grouping and Calculated Fields

Q22. Group by Quarter

Your PivotTable Row field is OrderDate (daily). You want quarterly totals. What is the fastest method? - A) Insert a new column in the source and write =ROUNDUP(MONTH(date)/3, 0). - B) Right-click any date cell in the PivotTable > Group > pick Quarters. - C) Use PivotTable Options > Auto-group. - D) Use a calculated field =QUARTER(OrderDate).

Q23. "Cannot group that selection"

When you right-click a Date row and pick Group, you get the error "Cannot group that selection." What is the cause? - A) The PivotTable has no Values field. - B) The source date column contains blank cells or text values mixed with dates. - C) Grouping requires Excel for Mac. - D) The PivotTable is built on a Power Pivot model.

Q24. Manual Text Grouping

You want to group France and Germany into a custom bucket called Europe. How? - A) Edit the source data and add a column. - B) Ctrl + click to select France and Germany in the Row labels > right-click > Group, then rename Group 1 to Europe. - C) Use a calculated item =France + Germany. - D) Insert a Slicer with Custom List.

Q25. Calculated Field Restriction

Which of the following CANNOT be used inside a PivotTable Calculated Field formula? - A) +, -, *, / operators. - B) The names of existing PivotTable fields. - C) Cell references like $A$1 or VLOOKUP lookups. - D) Constants like 0.05 or 1000.

Q26. Calculated Field vs Calculated Item

What is the key distinction? - A) Calculated Fields and Calculated Items are aliases for the same thing. - B) A Calculated Field creates a new field-level formula across all rows of the PivotTable; a Calculated Item creates a new row/column member computed from existing items in a single field. - C) Calculated Fields are SUMmed; Calculated Items are AVERAGED. - D) Calculated Items are only available in Power Pivot.

Q27. Order of Operations in Calculated Fields

A PivotTable has a Calculated Field =Revenue / Quantity (intended as Unit Price). What value does the cell actually compute? - A) The average of each row's Revenue/Quantity ratio. - B) Sum(Revenue) / Sum(Quantity) — Excel sums each underlying field first, then applies the formula. - C) The maximum unit price across rows. - D) #VALUE! because PivotTables cannot divide.

Section E: Lessons 4.8 – 4.9 — PivotCharts and Sparklines

Q28. PivotChart Field Buttons

You inserted a PivotChart and want to remove the dropdown arrows from the chart face for a presentation. Where do you toggle them off? - A) Format > Shape Effects > Hide. - B) PivotChart Analyze > Show/Hide group > Field Buttons dropdown > Hide All. - C) Right-click the PivotChart > Convert to Static Chart. - D) Design > Add Chart Element > Field Buttons > None.

Q29. Sparkline Axis: Same for All

Three sparklines compare monthly sales for three products. All three look like steady upward lines, even though Product A averages $1M and Product C averages $20K. What axis setting fixes the misleading visual? - A) Show Axis (zero line). - B) Plot Data Right-to-Left. - C) Vertical Axis Minimum > Same for All Sparklines (and Maximum > Same for All). - D) Horizontal Axis Type > Date Axis Type.

Q30. Sparkline Group Behaviour

You inserted four sparklines as a group. You want only the second one to be red while the others stay blue. What must you do first? - A) Delete and re-insert the second sparkline. - B) Select the group > Sparkline tab > Ungroup, then recolor the individual sparkline. - C) Right-click > Format Cells > change font color. - D) Convert the sparkline to a regular chart.

Q31. Sparkline Type for Sign-Only Tracking

You want a tiny chart that shows whether each month was a profit (positive) or loss (negative), ignoring magnitude. Which sparkline type is appropriate? - A) Line with markers. - B) Column with red negative bars. - C) Win/Loss — encodes the sign only (no magnitude). - D) Pareto sparkline.

Q32. Clearing a Sparkline

You select a sparkline cell and press Delete. The sparkline is still there. How do you actually remove it? - A) Delete the entire row. - B) Sparkline tab > Clear dropdown > Clear Selected Sparklines. - C) Right-click > Format Cells > Clear All. - D) Sparklines cannot be removed once inserted.

Section F: Lesson 4.10 — Trendlines

Q33. Trendline Type Selection

Your data is monthly revenue that grew slowly for a year, then started doubling every quarter. Which trendline type best fits the whole series (all values are positive)? - A) Linear - B) Logarithmic - C) Exponential - D) Moving Average

Q34. Trendline Restrictions

Which trendline type cannot fit data containing zero or negative values? - A) Linear - B) Polynomial Order 2 - C) Exponential - D) Moving Average

Q35. Trendline Forecast Forward

You want a Linear trendline that extends 3 periods past the last data point. Which field do you set? - A) Format Trendline > Forecast > Backward = 3. - B) Format Trendline > Forecast > Forward = 3. - C) Format Trendline > Set Intercept = 3. - D) Format Trendline > Polynomial > Order = 3.

Q36. R-squared Display

Where do you turn on the R² (goodness-of-fit) display for an existing trendline? - A) Chart Design > Add Chart Element > Data Labels. - B) Format Trendline pane > tick Display R-squared value on chart. - C) Right-click the chart > Select Data. - D) PivotTable Analyze > Options > Totals & Filters.

Q37. Moving Average Limitation

Which capability is not available for a Moving Average trendline? - A) Adjusting the period. - B) Customising the line color. - C) Displaying the equation and R-squared on the chart. - D) Removing the trendline.

Part 2: True or False

Q38. A Funnel chart requires the source data to be sorted from largest to smallest before insertion to render correctly. (True / False)

Q39. Defer Layout Update in the PivotTable Fields pane will continue to suspend recalculation even after you close and reopen the workbook. (True / False)

Q40. A single Timeline can be connected to multiple PivotTables via Report Connections, provided they share the same data cache. (True / False)

Q41. The Win/Loss sparkline encodes both the direction and magnitude of each value. (True / False)

Q42. Polynomial Order 6 trendlines always produce a more accurate forecast than Polynomial Order 2 because they have a higher R-squared value. (True / False)

Q43. Drill-through (double-click a PivotTable value) respects the currently active slicer and timeline filters. (True / False)

Q44. Hiding the Field Buttons on a PivotChart also disables the underlying filtering and drill-down behaviour. (True / False)

Q45. A PivotTable Calculated Field formula can call VLOOKUP to enrich its result. (True / False)

Q46. Pareto and Histogram charts both live under Insert > Insert Statistic Chart. (True / False)

Q47. A Sparkline cell can still hold its own text label or formula — the sparkline is drawn as a background graphic. (True / False)

Part 3: Hands-On Micro-Tasks

Open the practice workbook practice/module-4-pivots-and-charts.xlsx (or build a quick mock dataset) and complete each task. Time yourself — exam tasks should each take under 90 seconds.

Task A — Pareto Chart

On the Defects sheet, select the Category and Count columns and create a Pareto chart titled Defect Pareto. Verify that the bars are sorted descending and a cumulative line is visible. Path: Insert > Insert Statistic Chart > Pareto.

Task B — Histogram with Overflow Bin

On the Orders sheet, create a Histogram of Order Value. Set the Bin width to 50 and enable an Overflow bin at 500. Path: Insert > Insert Statistic Chart > Histogram, then right-click X-axis > Format Axis > set Bin width and Overflow bin.

Task C — Waterfall Total

On the P&L sheet, create a Waterfall chart of the bridge from Opening to Closing. Mark the Closing bar as a Total so it sits on the baseline. Path: Single-click bar twice > right-click > Set as Total.

Task D — PivotTable + Date Grouping

On the SalesData sheet, create a PivotTable on a new sheet showing Sum of Revenue by Region (Rows) and Order Date grouped by Years and Quarters (Columns).

Success criteria: the column area shows nested 2024 Q1, 2024 Q2 … 2025 Q4 headers; the Region column is in Rows.

Task E — Calculated Field

On the PivotTable from Task D, add a Calculated Field named Profit defined as = Revenue - Cost. Verify that it appears as a new value column.

Path: PivotTable Analyze > Fields, Items, & Sets > Calculated Field.

Task F — Timeline + Report Connections

On the Sales Dashboard sheet, insert a Timeline on the Order Date field of the Sales by Region PivotTable. Switch the time level to Quarters and connect the Timeline to a second PivotTable named Sales by Product via Report Connections.

Task G — Drill-Through

On the PivotTable from Task D, double-click any cell in the Revenue column. Verify that Excel inserts a new worksheet containing the underlying source rows that produced that value.

Path: Double-click any value cell.

Task H — Sparklines

In column H of the Products sheet, insert Line sparklines for each row's January-December values. Highlight the High Point and Low Point, and set the vertical axis to Same for All Sparklines.

Task I — Linear Trendline with Forecast and R²

On the existing Revenue chart, add a Linear trendline to the Net Revenue series. Set Forward forecast to 3 periods and tick both Display Equation on chart and Display R-squared value on chart.

Task J — Disable GetPivotData

Click anywhere in the PivotTable on the Dashboard sheet, then go to PivotTable Analyze > Options dropdown (left edge of the ribbon, next to the PivotTable Name box) and untick Generate GetPivotData. After this, typing = and clicking a PivotTable cell should yield a plain =Sheet1!B5 reference rather than a GETPIVOTDATA(...) formula.

Show answers

### Section A — Lesson 4.2 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q1** | **C** | Convention: the volume metric (Revenue) is a **Column** on the primary axis; the rate metric (Margin %) is a **Line** on the secondary axis. | | **Q2** | **B** | Pareto = descending bars + cumulative percentage line on a secondary axis. Both charts live under `Insert > Insert Statistic Chart`. | | **Q3** | **C** | **Overflow bin** collapses everything above the threshold into a single right-most bar (`>N`). Underflow does the same on the low side. | | **Q4** | **D** | **By Category** abandons numeric binning entirely and shows one bar per text category — perfect for `Department`-style data. | | **Q5** | **B** | Inclusive vs Exclusive Median changes how Q1 and Q3 are computed when the median value is included or excluded from each half of the data. | | **Q6** | **B** | The two-click sequence selects a single data point; **Set as Total** seats it on the baseline. | | **Q7** | **B** | Funnel charts expect a **single value column with category labels**. Multiple value columns confuse the renderer. | | **Q8** | **B** | Sunburst requires hierarchical category columns (at least two levels) plus a value column to size the rings. | ### Section B — Lessons 4.3-4.4 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q9** | **B** | **Defer Layout Update** suspends recalculation until you click **Update**, ideal for large models. It is a per-session toggle. | | **Q10** | **C** | The toggle lives in the **Options** dropdown of the **PivotTable Analyze** tab (or in `File > Options > Formulas`). | | **Q11** | **B** | `GETPIVOTDATA(data_field, pivot_table_anchor, [field, item], …)`. The data field name is in quotes; the anchor is any cell inside the target PivotTable. | | **Q12** | **B** | Double-click any value cell to spawn a new sheet containing the underlying source rows (drill-through / Show Details). | | **Q13** | **B** | **Show Values As > % of Column Total** displays each cell as its share of the column total. | | **Q14** | **B** | A value field name cannot collide with an existing source-column name. Workarounds include adding a trailing space (`"Sales "`) or a different label entirely. | | **Q15** | **B** | **Change Data Source** is required when the PivotTable's source range is static and has grown. The better long-term fix is to convert the source to an Excel Table. | | **Q16** | **A** | **PivotTable Analyze > Options > Data tab > Enable show details** controls whether double-click drill-through is allowed. | ### Section C — Lesson 4.5 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q17** | **C** | A Timeline gives a continuous scrubber with Years/Quarters/Months/Days; a date Slicer would list every unique date as a button. | | **Q18** | **A** | The Time Level dropdown in the top-right corner of the Timeline switches between Years, Quarters, Months, and Days. | | **Q19** | **B** | Insert Timeline only lists fields with a true Date data type. If your source date column is text, fix the type first. | | **Q20** | **A** | Right-click the Slicer > **Report Connections** > tick every PivotTable that should be filtered. The PivotTables must share the same data cache. | | **Q21** | **A** | The **Columns** spinner on the **Slicer** tab > **Buttons** group reflows the buttons into N columns. | ### Section D — Lessons 4.6-4.7 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q22** | **B** | Right-click any date in the Row area > **Group** > pick **Quarters** (and **Years** to nest). No source-data changes are required. | | **Q23** | **B** | "Cannot group that selection" almost always means the date column has blanks or text mixed in. Clean the source first. | | **Q24** | **B** | `Ctrl + click` to select the items > right-click > **Group** > rename the new `Group 1` label to `Europe`. Excel creates a parallel grouping field. | | **Q25** | **C** | Calculated Fields support only basic math on existing PivotTable fields. **No cell references, no VLOOKUP/SUMIF, no defined names.** | | **Q26** | **B** | A Calculated Field is a new column-level formula across all rows; a Calculated Item is a new row/column member within an existing field (e.g., `Q1+Q2` as a new "H1" item under a Quarter field). | | **Q27** | **B** | The PivotTable engine **sums first, then applies the formula**: `=Revenue/Quantity` becomes `Sum(Revenue) / Sum(Quantity)`. Crucially, this gives a correct weighted unit price even though the formula looks per-row. | ### Section E — Lessons 4.8-4.9 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q28** | **B** | **PivotChart Analyze > Show/Hide group > Field Buttons > Hide All** removes the dropdowns from the chart face. The underlying filter behaviour still works through slicers and the PivotTable. | | **Q29** | **C** | **Same for All Sparklines** forces a shared min/max so magnitude differences become visible. | | **Q30** | **B** | Grouped sparklines share styling. **Ungroup** first, then recolor the individual sparkline. | | **Q31** | **C** | Win/Loss encodes only the sign of each value. A `+1` bar is the same height as a `+1,000,000` bar — perfect for "win or loss?" tracking. | | **Q32** | **B** | Pressing `Delete` removes any cell text but the sparkline graphic remains. Use **Sparkline tab > Clear > Clear Selected Sparklines** (or right-click > Sparklines > Clear). | ### Section F — Lesson 4.10 Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q33** | **C** | Accelerating growth = **Exponential** (provided all values are positive). Linear understates the recent doubling. | | **Q34** | **C** | **Exponential** and **Power** trendlines require strictly positive values. Linear, Polynomial, and Moving Average have no such restriction. | | **Q35** | **B** | **Forward = 3** projects the trendline 3 periods past the last data point. **Backward** projects before the first point. | | **Q36** | **B** | Tick **Display R-squared value on chart** in the **Format Trendline** pane. | | **Q37** | **C** | Moving Average is a smoothing line, not a regression. Excel does **not** offer Equation or R-squared display for it. | ### Part 2 — True/False Answer Key | # | Answer | Explanation | | :--- | :--- | :--- | | **Q38** | **True** | A funnel renders top-to-bottom in source order. Largest-first gives the standard tapered shape. | | **Q39** | **False** | **Defer Layout Update** is a per-session toggle and is reset when you close the workbook. | | **Q40** | **True** | Timelines support **Report Connections** identically to Slicers, provided the PivotTables share the same data cache. | | **Q41** | **False** | Win/Loss only encodes the **sign** of each value. A `+1` bar is the same height as a `+1,000,000` bar. | | **Q42** | **False** | Higher-order polynomials almost always overfit and project wildly outside the data range. Higher R² on training data is not a forecast guarantee. | | **Q43** | **True** | Show Details respects all active filters, slicers, and timelines — the drill-through sheet matches the visible slice. | | **Q44** | **False** | Hiding field buttons is **cosmetic only** — the filters and drill-down behaviour still work through the connected PivotTable and any linked slicers. | | **Q45** | **False** | Calculated Fields support only basic operators on existing PivotTable fields. `VLOOKUP`, `SUMIF`, cell references, and named ranges are not allowed. | | **Q46** | **True** | Both live under **Insert > Insert Statistic Chart**. Pareto is the right-most icon in the Histogram subgroup. | | **Q47** | **True** | A sparkline is drawn as a background graphic — the cell itself can still display a number, label, or formula on top of the sparkline. | ### Part 3 — Hands-On Solutions | Task | Key Steps / Verification | | :--- | :--- | | **A** | `Insert > Insert Statistic Chart > Pareto`. Check: bars descend left-to-right, secondary-axis line ends at 100%. | | **B** | After insertion, right-click the X-axis > **Format Axis** > under **Axis Options** set **Bin width = 50** and tick **Overflow bin** with value `500`. | | **C** | Click the closing bar to select the series, click again to select just that point, right-click > **Set as Total**. The bar should drop to the baseline. | | **D** | `Insert > PivotTable` on a new sheet. Drag `Region` to **Rows**, `Revenue` to **Values**, `Order Date` to **Columns**. Right-click any date > **Group** > tick **Years** and **Quarters** > **OK**. The column header now shows `2024 > Q1, Q2, Q3, Q4 > 2025 …`. | | **E** | **PivotTable Analyze > Fields, Items, & Sets > Calculated Field…** → **Name:** `Profit` → **Formula:** `= Revenue - Cost` (double-click the field names to insert) → **Add** → **OK**. The new `Sum of Profit` column appears alongside `Sum of Revenue`. | | **F** | **PivotTable Analyze > Insert Timeline** > tick `Order Date` > switch the Time Level dropdown to **Quarters** > **Report Connections** > tick the second PivotTable. | | **G** | Double-click a Revenue cell. Excel creates a new worksheet containing an Excel Table of the source rows that contributed to that value. The new sheet respects any active slicers/timelines. | | **H** | Select `H2:Hn` > `Insert > Sparklines > Line` > Data Range = `B2:Gn` (one row per destination cell). **Sparkline** tab > tick **High Point** and **Low Point** > **Axis** > Vertical Axis Minimum > **Same for All Sparklines** (and Maximum > Same for All). | | **I** | Click the Net Revenue series > **Chart Elements (+)** > Trendline arrow > **More Options** > pick **Linear** > **Forecast Forward = 3** > tick **Display Equation on chart** + **Display R-squared value on chart**. | | **J** | Click any PivotTable cell > **PivotTable Analyze** tab > **Options** dropdown (left edge, beside the PivotTable Name box) > untick **Generate GetPivotData**. After this, typing `=` and clicking a value cell yields `=Sheet1!B5` instead of `GETPIVOTDATA(...)`. |

Exam Tip — Module 4 Coverage Map:

Lesson	Most-Tested Concept
4.2	Combo charts: Volume = Column primary, Rate = Line secondary; Pareto = descending bars + cumulative line; Histogram Overflow/Underflow/By Category; Waterfall Set as Total; Funnel = single value column with labels; Sunburst needs hierarchical categories.
4.3-4.4	Defer Layout Update for huge models; PivotTable Analyze > Options dropdown for Generate GetPivotData; double-click for Show Details; Show Values As > % of Column Total; PivotTable Analyze > Options > Data to disable drill-through.
4.5	Timelines for date scrubbing; Time Level dropdown for Years/Quarters/Months/Days; Report Connections drives multiple PivotTables; greyed-out Insert Timeline = source date is text.
4.6	Right-click date > Group > tick Years + Quarters; "Cannot group" = blanks/text in date column; manual text grouping with `Ctrl + click`.
4.7	Calculated Fields cannot use cell references, named ranges, or `VLOOKUP/SUMIF`. PivotTable engine sums first, then applies the formula. Calculated Item ≠ Calculated Field.
4.8	PivotChart Analyze > Field Buttons > Hide All is cosmetic only; PivotCharts auto-update with their PivotTable.
4.9	Same for All Sparklines for cross-row magnitude comparison; Ungroup before per-cell styling; Win/Loss encodes sign only; Sparkline tab > Clear to actually remove.
4.10	Trendline types: Exponential/Power need positive values; Forward = N for forecast; tick R² in Format Trendline; Moving Average has no equation/R².