Chart Staging Ranges, Composition Shares & Column/Bar/Doughnut Charts

Who this is for

Stage 7: Chart-Ready Staging Tables & Visual Analytics — Designed for learners mastering sumif staging summaries & grand-total exclusion rules, sorted horizontal bar charts, 100% doughnut shares & tail grouping, multi-series plan vs actual, $k axis scaling & dynamic text titles, filter zero-free series, quarter cross-tabs & peak/pass-fail helpers through interactive spreadsheet examples.

What you will practise

• Aggregate channel sales in E2:E4 with SUMIF from locked $A$2:$B$8, then insert a Column chart over D1:E4.
• Compute Grand Total in B6 with SUM(B2:B5), then insert a Column chart over A1:B5 excluding row 6.
• Sort A1:B5 descending by Resolved Tickets (column B, keeping headers), then insert a horizontal Bar chart on A1:B5.
• Calculate Share % in C2:C5 locking $B$6, verify the 1.0 total share in C6, and insert a Doughnut chart on A1:B5.
• Calculate the "Other (<10k)" bucket in E5 using SUMIF(B2:B8, "<10000"), then insert a Pie chart over D1:E5.
• Aggregate Actual regional bookings in F2:F4 with SUMIF, then insert a multi-series Column chart over D1:F4.
• Convert Raw Plan and Raw Actual (B2:C5) into $ Thousands rounded to 1 decimal in F2:G5, then insert a Column chart on E1:G5.
• Compute Total Variance % rounded to 4 decimals in D6, then construct the formatted dynamic headline in B1 using TEXT.

Your learning path

1. SUMIFS & TEXTJOIN+FILTER vs XLOOKUP When Duplicate Keys Exist

   Choose deliberately between one matching row, the sum of every matching amount, and a joined list of descriptions. Test literal invoice IDs, missing keys and zero totals.

2. Sum If Cells Contain Text from Another Cell ("*" & Keyword & "*")

   Total invoices or bank transactions whose description contains a dynamic search keyword in E2 using wildcard concatenation "*" & E2 & "*"—with an empty-search-box ISBLANK guard so blank inputs do not sum the entire sheet!

3. SUMIFS & SUMIF across horizontal columns (multi-row column headers)

   Sum wide horizontal budget/actual rows left-to-right where Row 1 Quarter and Row 2 Scenario headers match criteria—without transposing—using horizontal SUMIFS.

4. Sum If Not Equal To ("<>") Single or Multiple Exclusions (SUMIFS)

   Total ledger amounts while excluding cancelled, refunded, or intercompany categories using SUMIF(Cat, "<>" & Excl, Amt) and multi-exclusion SUMIFS(Amt, Cat, "<>" & Excl1, Cat, "<>" & Excl2).

5. Sum If Begins With Prefix or Ends With Suffix (Wildcard SUMIFS Formula)

   Total general-ledger accounts or SKU codes starting with a prefix (Prefix & "*") or ending with a regional/currency suffix ("*" & Suffix)—or matching BOTH simultaneously—using wildcard SUMIFS.

6. Sum If Blank ("") vs. Not Blank ("<>") With SUMIF & SUMIFS

   Total open accounts-receivable invoices where Paid_Date is blank using SUMIF(D2:D6, "", C2:C6), settled invoices where Paid_Date is non-blank using "<>", and multi-criteria open region balances with SUMIFS.

7. Filter and Sum Records from the Last N Weeks (FILTER & SUMIFS Rolling Window)

   Extract and total transactions falling within a rolling N-week lookback window anchored to a report cutoff date, excluding future-dated entries beyond the cutoff.

8. SUMIFS time totals: add shift durations past 24 hours without losing a day

   Spreadsheets store time durations as fractions of a 24-hour day. Sum shifts by team with SUMIFS and multiply by 24 for decimal hours—avoiding the classic clock-rollover bug where 27 hours looks like 3:00.