Chart-Mechanics Primer

Built-in AI tutor. Stuck on a chart step? Ask the helper on this page. It coaches you through the build, it does not hand you the answer.

Every chart Lab 5 asks for, step by step in Excel, built from the Summit Gear receivables data. Build a real chart object, not a pasted picture. If you can click it and Excel shows the Chart Design tab, it is real. Stuck on a step? Use the Ask-AI helper on this page.

Every chart on this page must clear four things: axes labeled and correct, a clear title, clean axis units (no cents on millions of dollars), and a native Excel object you can click into.
0 · Shape the data 1 · Two pies 2 · January column 3 · Stacked column 4 · Line + trendline 5 · Scatter 6 · Histogram

0The shape charts need: summarize the long invoice table

Your invoice sheet is long: one row per invoice, 2,500 of them. Charts read short summaries, not raw rows. Before you build anything, turn the long table into a few small count-and-dollar tables with a PivotTable.

  1. Fill every amber column on the Invoices sheet first: XLOOKUP customer_name, segment, and net_terms from the Customers sheet (the same pattern three times), then due_date = invoice_date + net_terms, days_past_due = (paid_date if paid, else the snapshot date) − due_date, the aging bucket, and days_to_pay. The pies and the stacked column group by segment, so it has to be on the invoice rows.
  2. Click any cell in the invoice table, then Insert → PivotTable.
  3. For the pies: rows = segment, values = Count of invoice_id in one pivot and Sum of amount in another.
  4. For the aging charts: rows = aging bucket, values = Sum of amount, and drag segment into Columns to split Commercial from Consumer.
  5. Keep the bucket rows in this exact order: Not due, 0-30, 31-60, 61-90, 91-120, 121-150, 151-180, 181+. Every bucket past Not due is measured in days past due, not days since the invoice.
  6. Rename any pivot field by typing over its header cell: click the cell that says Count of invoice_id, type Invoices, press Enter. The pivot keeps working; your chart legend reads like English.
Rule: decide the chart first, then shape the range to match it. A pie by segment needs one row per segment. An aging chart needs one row per bucket.
Net terms feed the due date. Terms are mixed (Commercial net 60/90/180, Consumer net 30), so aging by days since the invoice is not comparable across customers. You XLOOKUP net_terms, add it to invoice_date to get the due date, and age from there. That is what makes net_terms matter: without it you cannot tell who is actually past due.

1Two pie charts: Commercial vs Consumer, by count and by dollars

  1. From the count pivot, select the two-row block (segment and its invoice count). Insert → Charts → Pie (2-D Pie). Title it Invoices by segment, count.
  2. From the dollars pivot, do the same with Sum of amount. Title it Invoices by segment, dollars.
  3. Put both pies on one sheet, side by side, so they read as a pair.
  4. Turn on data labels showing percentage (right-click the pie → Add Data Labels → Format, tick Percentage).
Read the pair, not one pie: by count Consumer dominates, but by dollars Commercial does. Two honest pies of the same data can point opposite directions. That gap is the whole point of this chart.

2Aging column chart, limited to January 2026 billing

  1. Filter the invoices to invoice_date in January 2026 only. A PivotTable filter on the month, or a helper column flagging 2026-01, both work.
  2. Helper-column hint, the first of the month: =EOMONTH([@invoice_date],-1)+1. EOMONTH with -1 goes to the last day of the prior month; add one day and you are standing on the first of the invoice's own month. Flag January by comparing that to DATE(2026,1,1). Try it.
  3. Summarize those January invoices by count per aging bucket (rows = bucket, values = count), where the bucket comes from days_past_due against the due date, including the Not due bucket for invoices still within terms.
  4. Select the block, Insert → Charts → Column (Clustered Column), one column per bucket.
  5. Keep the buckets in age order along the x axis (Not due first, then 0-30 up to 181+). Title it, label the axes, and set the y axis to whole numbers.
  6. Buckets with no January invoices will not appear in your pivot. That is expected, not an error.
Why limit the month: a single billing month makes the buckets comparable. Mixing every month's invoices buries the recent activity the controller is asking about. Aging here is days past due, so a January invoice on Consumer net 30 is past due far sooner than one on Commercial net 180.

3Stacked column: aging dollars by bucket, split by segment

  1. Use the aging-dollars pivot with segment in Columns, so each bucket row has a Commercial dollar value and a Consumer dollar value. The bucket is the days-past-due band from the due date, with Not due first for invoices still within terms.
  2. Select the block including both segment columns. Insert → Charts → Column → Stacked Column.
  3. X axis is the eight buckets in age order (Not due through 181+). Each column stacks Commercial on Consumer.
  4. Title it, label the axes, and format the dollar axis in millions with no cents (Format Axis → Display units → Millions).
Read it: this chart covers all invoices, paid and open, so the bucket height is the total dollars that landed in that lateness band, and the split inside tells you which segment is driving it. Most dollars sit in Not due and 0-30, paid on time or close to it. But from 31-60 outward the columns turn mostly Commercial: the dollars that go seriously late are overwhelmingly Commercial dollars.
Why are paid invoices in an aging chart? Because this chart reads payment behavior, not the open balance. A paid invoice is bucketed by how late it was on the day it was paid; an open invoice by how late it is as of the snapshot, March 31, 2026. One cutoff date, two reference points, every invoice counted once.

4Weekly line chart, then add a linear trendline

  1. Add a helper column that snaps each invoice_date to the start of its week (weeks start on Sunday): =[@invoice_date]-WEEKDAY([@invoice_date])+1. WEEKDAY counts Sunday as 1, so subtracting it walks you back past Sunday and the +1 steps you onto it. A date already on Sunday maps to itself. Try it. Then summarize count of invoices per week (rows = week start, values = count).
  2. Select the week-and-count block. Insert → Charts → Line (2-D Line). Title it and label the axes.
  3. Right-click the line (the data series) → Add Trendline → Linear.
  4. In the Trendline pane, tick Display R-squared value on chart.
  5. Ask the Ask-AI helper, or any free AI tool, what the trendline shows about Summit Gear's weekly invoicing, then write that read in your own words in the Trendline read: cell, the amber cell below the chart box on the template's 4 Trend sheet.
Read it by slope, not by one bumpy week: the trendline is a model of the weekly counts, not the counts themselves. A rising slope means invoicing is trending up. R-squared near 1 means the weeks hug that trend; near 0 means the trend is weak and the line is mostly noise.

5XY scatter: invoice amount vs days to pay (paid invoices only)

  1. Filter to paid invoices only (a non-blank paid_date), since open invoices have no days-to-pay.
  2. Check your days-to-pay column first: it is paid date minus the invoice date. If you see negative values (invoices "paid early"), you measured from the due date; that is days past due, a different metric. Done right, the smallest value here is 1 and the largest is around 400.
  3. Put amount in one column and days to pay in the next, side by side.
  4. Select both columns. Insert → Charts → Scatter (the first, points-only option).
  5. Amount on the x axis, days to pay on the y axis. Title it, label both axes, and format the x axis as dollars with no cents.
Read it: each dot is one paid invoice. If big invoices clustered at high days-to-pay, larger bills would pay slower. If the cloud is shapeless, invoice size and payment speed are not linked. Let the dots tell you, do not assume.

6Histogram: days to pay (default Excel bins)

  1. Filter the invoice table to paid invoices only, then copy the days-to-pay values out to a fresh sheet (Copy, then Paste Values) and chart over that copy. Statistic charts are happier reading a clean single column than a filtered table, and the copy makes it obvious exactly which numbers the histogram saw.
  2. Select that single column. Insert → Charts → Histogram (under the Statistic Chart group).
  3. Leave Excel's default bins as they are. Each bar counts how many invoices fell in that days-to-pay range.
  4. Title it and label the axes (x is days-to-pay ranges, y is number of invoices).
Read the shape: the histogram shows the spread of payment speed. The tall bars are the common ranges, the long right tail is the slow payers. The median days to pay is 37, so half the paid invoices clear faster than that and half slower.
Before you submit any chart, check it again: axes labeled and correct, a clear title, clean axis units, and a native Excel object. A chart that fails one of these is not done.