Mathew K Analytics

Lesson 9 · Excel: Zero to Expert

Advanced Excel Charts Full Course: Waterfall, Funnel, Dynamic Charts & Reports | Excel 109

Sheet: Principles Workbook: excel109charts.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by…

⬇ Download workbook (.xlsx)

What you'll learn

Files for this lesson

Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.

📝 Step-by-step guide

Excel 109: Advanced Charts and Visual Reports#

Sheet: Principles

Workbook: excel109_charts.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step with the video. Each section below lists exactly what to click and type.

What makes a good chart#

  • In A1 type Kookaburra Creek Outdoors: sales by category

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Category | Sales | |---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Kayaks | 88400 | |---|---| | Rain Jackets | 132600 | | Tents | 182300 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Sleeping Bags | 96100 | |---|---| | Hiking Boots | 154800 | | Backpacks | 118200 |

  • Format B4:B9: number format $#,##0
  • Format A3:B3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A:A: column width 14
  • AutoFit the width of column B:B (double-click its right edge)

The status bar shows a sum of seven hundred and seventy two thousand four hundred dollars. Notice the categories are in no particular order. That's how data usually arrives, straight from a system.

  • Select A3:B9 and insert a column chart titled “Sales”

Look at it as a reader would. The title Sales tells us nothing we didn't know. The bars jump up and down in random order, so to find the best seller you have to compare every bar with every other. And the gridlines and the axis make you read numbers off a scale. It isn't wrong. It's just hard work for the reader. Let's fix it, one rule at a time.

  • Sort: range A3:B9, by Sales, order desc

The table now runs from tents at the top, a hundred and eighty two thousand three hundred, down through hiking boots, rain jackets, backpacks and sleeping bags, to kayaks at the bottom with eighty eight thousand four hundred. And look at the chart. It re-sorted itself, because a chart always follows the order of its source cells. Now it reads like a ranking, tallest bar on the left.

  • Select A3:B9 and insert a column chart titled “Tents are our top seller”
  • On the ribbon choose Chart Design > Add Chart Element > Gridlines > Primary Major Horizontal
  • On the ribbon choose Chart Design > Add Chart Element > Axes > Primary Vertical
  • Click A12

Compare the two charts. The bottom one says its message out loud in the title. The bars are ranked. Every bar carries its exact value, so we don't need gridlines or an axis, and without them the chart is calm and easy to read. That's the same data and the same chart type. The difference is purely the choices we made.

Chart elements in depth#

Sheet: Elements

  • In A1 type Quarterly sales by state ($ thousands)

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | State | Q1 | Q2 | Q3 | Q4 | |---|---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | NSW | 182 | 164 | 151 | 213 | |---|---|---|---|---| | VIC | 147 | 139 | 128 | 176 | | QLD | 121 | 133 | 142 | 168 |

  • Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1

  • Starting in A8, type these rows (one value per cell, moving right):

    | Chart title | ="Quarterly sales by state: $"&TEXT(SUM(B4:E6),"#,##0")&"k in total" | |---|---|

  • Format A8: bold, font size 11

B8 reads, Quarterly sales by state: one thousand eight hundred and sixty four k in total. That's a title that knows its own numbers. In a moment we'll connect the chart's title to this cell.

  • Select A3:E6 and insert a column chart titled “Quarterly sales”

The chart title now reads exactly what B8 says: Quarterly sales by state, one thousand eight hundred and sixty four k in total. Two things to remember. You must select the title itself before you type, otherwise you'll type into a cell. And the reference has to include the sheet name, even when the cell is on the same sheet.

  • On the ribbon choose Chart Design > Switch Row/Column

Now the states are along the bottom, and each quarter is a colour. Same numbers, different question. The first layout answered, how did each quarter compare across the states? This one answers, within each state, how did the year unfold? You can see New South Wales jumping to two hundred and thirteen in the fourth quarter. Excel's rule is simple: whichever there are more of, rows or columns, goes along the bottom. We had four quarters and three states, so quarters went along the bottom. When the chart comes out the wrong way round, this button is your fix.

  • On the ribbon choose Chart Design > Switch Row/Column
  • On the ribbon choose Insert > ChartTypeColumnInsertGallery > Clustered Bar

The same chart, now with horizontal bars. The title link and the data all came with it. Changing type doesn't mean starting again. You can also use Chart Design, Change Chart Type, which shows a preview of every type with your data.

  • On the ribbon choose Insert > ChartTypeColumnInsertGallery > Clustered Column
  • On the ribbon choose Chart Design > Add Chart Element > Legend > Top
  • On the ribbon choose Chart Design > Add Chart Element > Data Labels > Outside End
  • On the ribbon choose Chart Design > Add Chart Element > Gridlines > Primary Major Horizontal
  • Click A10

Now the chart has a legend at the top, a label on all twelve columns, an axis title explaining the units, and a title that comes from a cell. The gridlines are gone. Twelve labels is about the limit. With more series than this, labels everywhere become clutter, and you're better off labelling only the last or the most important values.

  • In E6 type 198

The Queensland column grew, its label says one ninety eight, and the title now says one thousand eight hundred and ninety four k in total. The title updated itself, because it's linked to B8.

Elements: tips and common mistakes#

Actual versus target: a combo chart#

Sheet: Target

  • In A1 type Online sales: actual vs target ($ thousands)

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Month | Actual | Target | Variance | % of target | |---|---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Jul | 96 | 100 | |---|---|---| | Aug | 104 | 100 | | Sep | 99 | 105 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Oct | 118 | 115 | |---|---|---| | Nov | 131 | 125 | | Dec | 162 | 150 |

Six months of actuals and targets. Before we chart anything, let's add two helper columns that put the comparison into numbers.

  • Starting in D4, type these rows (one value per cell, moving right):

    | =B4-C4 | =B4/C4 | |---|---|

  • Fill D4:E9 down (drag the fill handle, or select it and press Ctrl+D)

  • Format E4:E9: number format percent

  • Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1

  • AutoFit the width of column B:E (double-click its right edge)

July missed by four, ninety six percent of target. August beat it by four. September missed by six, ninety four percent. Then October, November and December all beat target, by three, six and twelve. December hit a hundred and eight percent.

  • Starting in A11, type these rows (one value per cell, moving right):

    | Months on target | =COUNTIF(D4:D9,">=0") | |---|---|

  • Format A11: bold, font size 11

Four months out of six were on target. Now let's see if the chart tells the same story faster.

  • Select A3:C9 and insert a column chart titled “Actual sales vs target, July to December”
  • Python: code co = ws.ChartObjects(ws.ChartObjects().Count) s = co.Chart.SeriesCollection(2) s.ChartType = 65

Now the target is an orange line stepping up through the year, and the blue columns are the actual sales. Where a column pokes above the line, we beat target. Where it falls short, we missed. You can see it instantly: short in July and September, above the line from October on, and well clear in December. That's the four out of six months from our COUNTIF, but the reader got it in one second without reading a single number.

Waterfall charts: the profit bridge#

Sheet: Waterfall

  • In A1 type Profit bridge, FY 2025-26

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Item | Amount | |---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Revenue | 1250000 | |---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Cost of Goods | -687500 | |---|---| | Wages | -238000 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Rent | -96000 | |---|---| | Marketing | -64500 | | Other Costs | -31000 | | Net Profit | =SUM(B4:B9) |

  • Format B4:B10: number format $#,##0
  • Format A3:B3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A:A: column width 14
  • AutoFit the width of column B:B (double-click its right edge)

Net profit is a hundred and thirty three thousand dollars. That's ten point six four percent of revenue. The costs show with a minus sign in front, which is exactly what a waterfall needs: positive numbers go up, negative numbers come down.

  • Select A3:B10 and insert a waterfall chart titled “From revenue to net profit”

Here's our waterfall. Revenue is a tall bar, then each cost steps down from where the last one ended. Cost of goods takes the biggest bite, down to five hundred and sixty two thousand five hundred. Wages take it to three hundred and twenty four thousand five hundred, rent to two hundred and twenty eight thousand five hundred, marketing to a hundred and sixty four thousand, and other costs to a hundred and thirty three thousand. But look at the last bar. Net profit is floating up in the air, as if it were another increase on top of the hundred and thirty three thousand. Excel doesn't know it's a total.

  • Python: code co = ws.ChartObjects(ws.ChartObjects().Count) try: s = co.Chart.FullSeriesCollection(1) except Exception: s = co.Chart.SeriesCollection(1) s.Points(7).IsTotal = True

Now net profit stands on the ground, a solid bar of a hundred and thirty three thousand dollars, and it lines up exactly with the bottom of the last step. The bridge is complete. The legend explains the colours: one colour for increases, one for decreases and one for totals. Revenue counts as an increase from zero, which is why it looks just like a total.

Funnel charts for a sales pipeline#

Sheet: Funnel

  • In A1 type Online sales pipeline, last quarter

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Stage | Customers | % of visitors | Step conversion | |---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Website Visit | 12000 | |---|---| | Viewed Product | 5400 | | Added to Cart | 1620 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Started Checkout | 860 | |---|---| | Placed Order | 540 |

  • In C4 type the formula =B4/$B$4
  • Fill C4:C8 down (drag the fill handle, or select it and press Ctrl+D)
  • In D5 type the formula =B5/B4
  • Fill D5:D8 down (drag the fill handle, or select it and press Ctrl+D)
  • Format C4:D8: number format 0.0%
  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A:A: column width 17
  • AutoFit the width of column B:D (double-click its right edge)

Of every hundred visitors, forty five viewed a product, thirteen and a half added to cart, seven point two started checkout and four and a half ordered. The step conversion tells a sharper story. Forty five percent go from visit to product, but only thirty percent go from product to cart. After that it's better: fifty three point one percent of carts reach checkout, and sixty two point eight percent of checkouts become orders.

  • Select A3:B8 and insert a funnel chart titled “Where our online customers drop out”

Each stage is a centred bar with its number on it, twelve thousand at the top down to five hundred and forty at the bottom. The narrowing shape makes the biggest drop obvious: between viewing a product and adding it to the cart. And that's the message for the business. Before spending more on ads to get visitors, fix the product pages. Better photos, clearer prices, or delivery information might move that thirty percent.

Treemaps for product categories#

Sheet: Treemap

  • Paste: at A3, rows [['Category', 'Product', 'Sales'], ['Camping', 'Tents', 182000], ['Camping', 'Sleeping Bags', 96000], ['Camping', 'Camp Stoves', 41000], ['Hiking', 'Hiking Boots', 154000], ['Hiking', 'Backpacks', 118000], ['Hiking', 'Trekking Poles', 23000], ['Clothing', 'Rain Jackets', 132000], ['Clothing', 'Thermals', 47000], ['Clothing', 'Hats', 18000], ['Water', 'Kayaks', 88000], ['Water', 'Paddles', 21000]]
  • Format C4:C14: number format $#,##0
  • Format A3:C3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A:A: column width 10
  • AutoFit the width of column B:C (double-click its right edge)

With the three camping rows selected, the status bar sums them to three hundred and nineteen thousand dollars. Hiking adds up to two hundred and ninety five thousand, clothing a hundred and ninety seven thousand and water a hundred and nine thousand. Nine hundred and twenty thousand dollars in total.

  • Select A3:C14 and insert a treemap chart titled “Sales by category and product”

Each colour is a category, and the size of every rectangle is its sales. Camping is the biggest block, about thirty five percent of the total, with tents as the single largest rectangle. Hiking is close behind at about thirty two percent, led by hiking boots. Clothing is about twenty one percent and water sports about twelve, and the tiny rectangles, like hats and paddles, show what barely matters.

Histogram versus column chart#

Sheet: Histogram

  • Paste: at A3, rows [['Order Value'], [110], [157], [142], [58], [62], [89], [85], [151], [216], [92], [50], [65], [165], [141], [80], [181], [88], [126], [338], [76], [162], [182], [98], [71], [241], [225], [212], [154], [125], [205], [97], [58], [94], [84], [60], [65], [200], [94], [42], [90]]
  • Format A3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Select A4:A43

The average order is a hundred and twenty five dollars seventy eight cents, the count is forty, and the sum is five thousand and thirty one dollars.

  • Select A3:A43 and insert a histogram chart titled “Order values, automatic bins”

Excel chose the bins for us. Along the bottom, each label shows a range, with a square bracket meaning including and a round bracket meaning up to. The bars are counts of orders, not dollars. The shape is the message: lots of smaller orders on the left and a long tail of a few big orders to the right. That's very typical of sales data.

  • Starting in C3, type these rows (one value per cell, moving right):

    | Band | From | To | Orders | |---|---|---|---| | Under $100 | 0 | 100 | | | $100 to $199 | 100 | 200 | | | $200 to $299 | 200 | 300 | | | $300 to $399 | 300 | 400 | |

  • In F4 type the formula =COUNTIFS($A$4:$A$43,">="&D4,$A$4:$A$43,"<"&E4)
  • Fill F4:F7 down (drag the fill handle, or select it and press Ctrl+D)
  • Format C3:F3: bold, font colour White, Background 1, fill Blue, Accent 1
  • AutoFit the width of column C:F (double-click its right edge)

Twenty one orders under a hundred dollars, twelve from one hundred to one ninety nine, six in the two hundreds, and just one above three hundred. The status bar sum is forty, so every order is counted exactly once.

  • Select C3:C7,F3:F7 and insert a column chart titled “Orders by value band”

The same story, with labels we chose and exact counts: twenty one, twelve, six and one. So here's the rule. A column chart of categories you define is perfect for a report. The histogram chart is perfect for exploring, when you just want to see the shape of a column of numbers quickly.

Which special chart, and when?#

Practice break: special charts#

A dynamic chart with a dropdown#

Sheet: Dynamic

  • Paste: at A3, rows [['Month', 'Camping', 'Hiking', 'Clothing', 'Water'], ['Jul', 148, 163, 237, 35], ['Aug', 150, 179, 210, 35], ['Sep', 197, 229, 164, 50], ['Oct', 224, 238, 121, 81], ['Nov', 279, 192, 105, 114], ['Dec', 354, 158, 102, 160], ['Jan', 333, 144, 93, 163], ['Feb', 221, 152, 108, 128], ['Mar', 214, 201, 152, 84], ['Apr', 171, 242, 199, 57], ['May', 126, 214, 227, 37], ['Jun', 134, 189, 250, 27]]
  • In A1 type Website orders by category
  • Format A1: bold, font size 14
  • Format A3:E3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Starting in G2, type these rows (one value per cell, moving right):

    | Category | Camping | |---|---|

  • Validation: range H2, list =$B$3:$E$3, input_message Choose a category

  • Format H2: bold, fill Yellow

H2 now has a dropdown arrow and a yellow fill. The list comes straight from the table headings, so if someone renames a category in the table, the dropdown follows.

  • Starting in G4, type these rows (one value per cell, moving right):

    | Month | =H2 | |---|---|

  • In G5 type the formula =A4:A15

  • In H5 type the formula =XLOOKUP(H2,B3:E3,B4:E15)

H5 to H16 now hold the camping orders: a hundred and forty eight in July, rising to three hundred and fifty four in December, then falling to a hundred and twenty six in May. The status bar sum is two thousand five hundred and fifty one.

  • In G18 type the formula ="Website orders: "&H2&", "&TEXT(SUM(H5#),"#,##0")&" for the year"
  • Select G4:H16 and insert a column chart titled “Website orders”
  • Click G20

The chart shows camping orders by month, with the summer peak in December and January, and the title reads Website orders: Camping, two thousand five hundred and fifty one for the year.

  • Click H2
  • Press Alt+Down
  • Press Esc
  • In H2 type Water

The whole chart changed. Water sports are tiny in winter, just twenty seven orders in June, and peak at a hundred and sixty three in January. The title now says Water, nine hundred and seventy one for the year.

  • In H2 type Clothing

Clothing is the opposite pattern. It peaks in winter, two hundred and fifty orders in June, and dips to ninety three in January. A total of one thousand nine hundred and sixty eight. One chart, four stories, and the reader is in control. That's the heart of an interactive report.

Highlighting the biggest bar automatically#

Sheet: Highlight

  • Paste: at A3, rows [['Month', 'Sales ($k)'], ['Jan', 84], ['Feb', 79], ['Mar', 92], ['Apr', 88], ['May', 101], ['Jun', 97], ['Jul', 110], ['Aug', 126], ['Sep', 119], ['Oct', 105], ['Nov', 132], ['Dec', 158]]
  • In A1 type Monthly sales, calendar year
  • Format A1: bold, font size 14
  • Starting in C3, type these rows (one value per cell, moving right):

    | Others | Best Month | |---|---|

  • Starting in C4, type these rows (one value per cell, moving right):

    | =IF(B4=MAX($B$4:$B$15),"",B4) | =IF(B4=MAX($B$4:$B$15),B4,"") | |---|---|

  • Fill C4:D15 down (drag the fill handle, or select it and press Ctrl+D)

Every month has its number in the Others column, except December, the best month at a hundred and fifty eight thousand, which sits alone in the Best Month column.

  • Select A3:A15,C3:D15 and insert a stacked_column chart titled “Monthly sales ($ thousands)”
  • Format C3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format D3: bold, font colour White, Background 1, fill Orange, Accent 2

Eleven blue bars and one orange bar, December. The eye goes straight to it. Why stacked? In a clustered chart each month would have two slots side by side, one of them empty, so every bar would be off-centre. Stacking puts both series in the same slot, and since one of them is always empty, each month shows exactly one bar.

  • In B11 type 171

The orange bar jumped from December to August, and December turned blue. Nobody touched the chart. The MAX changed, the IF formulas moved the numbers between columns, and the colours followed.

Colours from the palette#

In-cell charts with conditional formatting#

Sheet: InCell

  • In A1 type Product scorecard

  • Format A1: bold, font size 14

  • Starting in A3, type these rows (one value per cell, moving right):

    | Product | Units | Margin | Units bar | |---|---|---|---|

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Tents | 412 | 38% | |---|---|---| | Hiking Boots | 538 | 44% | | Backpacks | 467 | 41% |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Rain Jackets | 395 | 52% | |---|---|---| | Sleeping Bags | 301 | 36% | | Kayaks | 64 | 22% | | Camp Stoves | 226 | 31% | | Headlamps | 689 | 58% |

  • Format A3:D3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A:A: column width 14
  • AutoFit the width of column B:C (double-click its right edge)
  • Conditional formatting on B4:B11: highlight data_bar

Each cell now has a bar behind its number, scaled to the largest value. Headlamps, at six hundred and eighty nine, fills the cell. Kayaks, at sixty four, is just a sliver. You read the table and the chart at the same time.

  • Conditional formatting on C4:C11: highlight icon_set

By default Excel splits the range from the smallest to the largest value into thirds. The top third gets a green up arrow, the middle a yellow sideways arrow, and the bottom a red down arrow. Rain jackets at fifty two percent and headlamps at fifty eight get green arrows. Tents, hiking boots, backpacks and sleeping bags get yellow. Camp stoves at thirty one percent and kayaks at twenty two get red.

  • In D4 type the formula =REPT("|",B4/50)
  • Fill D4:D11 down (drag the fill handle, or select it and press Ctrl+D)
  • Format D4:D11: bold, font colour Blue, Accent 1
  • AutoFit the width of column D:D (double-click its right edge)

A little bar chart made of text. Headlamps get thirteen bars, hiking boots ten, backpacks nine, tents eight, rain jackets seven, sleeping bags six, camp stoves four, and kayaks just one. REPT ignores the decimals, so four hundred and twelve divided by fifty, eight point two four, gives eight bars.

Practice break: in-cell charts#

Project: a one-page sales report#

Sheet: Data

  • Paste: at A3, rows [['Month', 'NSW', 'VIC', 'QLD', 'WA'], ['Jul', 40500, 33800, 33000, 22700], ['Aug', 40400, 35900, 31800, 21500], ['Sep', 46000, 40500, 35000, 24600], ['Oct', 48000, 42900, 37600, 26300], ['Nov', 56200, 47600, 41700, 29700], ['Dec', 77900, 59200, 53100, 39700], ['Jan', 68100, 55000, 47900, 35800], ['Feb', 54700, 47700, 43200, 26800], ['Mar', 53800, 45200, 38500, 26900], ['Apr', 49100, 42400, 36600, 25900], ['May', 45500, 37900, 35000, 24800], ['Jun', 47100, 39300, 35900, 24700]]

  • Starting in A16, type these rows (one value per cell, moving right):

    | Total | =SUM(B4:B15) | |---|---|

  • Fill B16:E16 right (Ctrl+R)

  • In A1 type Kookaburra Creek Outdoors: data, FY 2025-26

  • Format A1: bold, font size 14

New South Wales sold six hundred and twenty seven thousand three hundred dollars, Victoria five hundred and twenty seven thousand four hundred, Queensland four hundred and sixty nine thousand three hundred, and Western Australia three hundred and twenty nine thousand four hundred.

  • Paste: at G3, rows [['Product', 'Sales'], ['Camp Stoves', 121700], ['Tents', 352600], ['Rain Jackets', 226300], ['Kayaks', 164200], ['Hiking Boots', 298400], ['Thermals', 109800], ['Sleeping Bags', 187500], ['Trekking Poles', 64300], ['Backpacks', 241900], ['Headlamps', 186700]]
  • Paste: at J3, rows [['Cost', 'Amount'], ['Cost of Goods', 1074400], ['Wages', 372000], ['Rent', 144000], ['Marketing', 97700], ['Other Costs', 58600]]
  • Format B4:E16,H4:H13,K4:K8: number format $#,##0
  • Format A3:E3,G3:H3,J3:K3: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A16:E16: bold, double bottom border
  • AutoFit the width of column B:K (double-click its right edge)

The product sales add up to one million, nine hundred and fifty three thousand four hundred dollars, the same as the four states. It's always worth checking that two views of the same business agree.

The headline numbers#

Sheet: Report

  • In A1 type Kookaburra Creek Outdoors: Sales Report FY 2025-26

  • Format A1: bold, font size 16

  • In A2 type All figures in Australian dollars. Choose a region in B7 to update the monthly chart.

  • Starting in A4, type these rows (one value per cell, moving right):

    | Total sales | | Net profit | | Net margin | | Best region | | Best product | |---|---|---|---|---|

  • Format A:A: column width 16

  • In A5 type the formula =SUM(Data!B4:E15)
  • In C5 type the formula =A5-SUM(Data!K4:K8)
  • In E5 type the formula =C5/A5

Total sales show as one million, nine hundred and fifty three thousand, four hundred. Net profit is two hundred and six thousand seven hundred, and the margin shows as a long decimal for now. We'll format it in a moment.

  • In G5 type the formula =XLOOKUP(MAX(Data!B16:E16),Data!B16:E16,Data!B3:E3)
  • In I5 type the formula =XLOOKUP(MAX(Data!H4:H13),Data!H4:H13,Data!G4:G13)
  • Format A5,C5: number format $#,##0
  • Format E5: number format 0.0%
  • Format A4:I4: bold, font colour White, Background 1, fill Blue, Accent 1
  • Format A5:I5: bold, font size 14, align left
  • Format B:I: column width 13

Total sales are one million, nine hundred and fifty three thousand four hundred dollars. Net profit is two hundred and six thousand seven hundred dollars, a net margin of ten point six percent. The best region is NSW, and the best product is tents. Five numbers, and the owner already knows how the year went. Everything below is the detail.

The dynamic region chart#

  • Starting in A7, type these rows (one value per cell, moving right):

    | Region | NSW | |---|---|

  • Validation: range B7, list =Data!$B$3:$E$3, input_message Choose a region

  • Format A7:B7: bold

  • Format B7: fill Yellow

B7 has its dropdown, listing NSW, VIC, QLD and WA, straight from the Data sheet.

  • Starting in G9, type these rows (one value per cell, moving right):

    | Month | =B7 | |---|---|

  • In G10 type the formula =Data!A4:A15

  • In H10 type the formula =XLOOKUP(B7,Data!B3:E3,Data!B4:E15)

New South Wales: forty thousand five hundred in July, a peak of seventy seven thousand nine hundred in December, and the status bar sum is six hundred and twenty seven thousand three hundred, matching the Data sheet.

  • In D7 type the formula ="Monthly sales: "&B7&", total "&TEXT(SUM(H10#),"$#,##0")
  • Format G9:H21: font colour Gray, Accent 3

D7 reads, Monthly sales: NSW, total six hundred and twenty seven thousand three hundred dollars.

The profit bridge and top products#

  • Starting in A12, type these rows (one value per cell, moving right):

    | Profit bridge | Amount | |---|---| | Revenue | =A5 | | Cost of Goods | =-Data!K4 | | Wages | =-Data!K5 |

  • Starting in the next empty row, type these rows (one value per cell, moving right):

    | Rent | =-Data!K6 | |---|---| | Marketing | =-Data!K7 | | Other Costs | =-Data!K8 | | Net Profit | =SUM(B13:B18) |

Net profit is two hundred and six thousand seven hundred, exactly the same as the KPI in C5. Two different routes to the same answer, which is a great check that nothing's gone wrong.

  • Starting in D12, type these rows (one value per cell, moving right):

    | Top 5 products | Sales | |---|---|

  • In D13 type the formula =SORT(TAKE(SORT(Data!G4:H13,2,-1),5),2,1)

  • Format B13:B19,E13:E17,H10:H21: number format $#,##0

  • Format A12:B12,D12:E12,G9:H9: bold, font colour White, Background 1, fill Blue, Accent 1

  • Format A19:B19: bold, double bottom border

The top five, from fifth up to first: sleeping bags with a hundred and eighty seven thousand five hundred, rain jackets two hundred and twenty six thousand three hundred, backpacks two hundred and forty one thousand nine hundred, hiking boots two hundred and ninety eight thousand four hundred, and tents three hundred and fifty two thousand six hundred. Headlamps missed out by just eight hundred dollars, with a hundred and eighty six thousand seven hundred.

Building the charts#

At seventy percent we can see the whole page: the KPI band, the selector, and the three helper tables. The empty space underneath is where the charts will go.

  • Select G9:H21 and insert a line_markers chart titled “Monthly sales”
  • Click A21

A line climbing to the summer peak in December and easing off through autumn, with the title Monthly sales: NSW, total six hundred and twenty seven thousand three hundred dollars.

  • Select A12:B19 and insert a waterfall chart titled “From sales to net profit”
  • Python: code co = ws.ChartObjects(ws.ChartObjects().Count) try: s = co.Chart.FullSeriesCollection(1) except Exception: s = co.Chart.SeriesCollection(1) s.Points(7).IsTotal = True

Revenue of one million, nine hundred and fifty three thousand four hundred steps down: cost of goods to eight hundred and seventy nine thousand, wages to five hundred and seven thousand, rent to three hundred and sixty three thousand, marketing to two hundred and sixty five thousand three hundred, and other costs to the net profit of two hundred and six thousand seven hundred, standing on the ground as a total.

  • Select D12:E17 and insert a bar chart titled “Top 5 products by sales”

Tents at the top, then hiking boots, backpacks, rain jackets and sleeping bags. Sorted, labelled, and no legend needed. Three charts, the same height, side by side in one row.

Finishing touches#

  • On the ribbon choose View > Gridlines
  • Click A1

Instantly cleaner. The blue KPI band, the yellow input cell and the three charts now sit on a white page. Our palette is consistent too: blue headings, blue chart series, and one bright colour, yellow, for the one thing the reader can change.

  • In B7 type WA

The monthly chart redrew itself for Western Australia. The title now says Monthly sales: WA, total three hundred and twenty nine thousand four hundred dollars. The low point is twenty one thousand five hundred in August, and the peak is thirty nine thousand seven hundred in December. The KPIs, the bridge and the top five stay the same, because they describe the whole business. Only the chart the owner controls changes.

  • In B7 type VIC

Victoria: five hundred and twenty seven thousand four hundred for the year, from a low of thirty three thousand eight hundred in July to a December peak of fifty nine thousand two hundred.

  • In B7 type NSW
  • Press Ctrl+Home
  • Press Ctrl+S

Report design checklist#

Recap and what's next#

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.