Lesson 4 · Excel: Zero to Expert
Excel PivotTables, Charts & Dashboards: Full Course | Excel 104
Sheet: Data Workbook: pivotsdashboards.xlsx. Download it from this page to see the finished result, or start a new blank workbook and build it step by step…
- CourseExcel: Zero to Expert
- Lesson4 of 7
- Video1 h 24 min
- FormatExcel workbook · step-by-step guide
What you'll learn
Files for this lesson
Open the workbook in Excel (Microsoft 365 or Excel 2021) and follow along with the video.
- pivots_dashboards.xlsx101.5 KB
📝 Step-by-step guide
PivotTables, Charts and Dashboards#
Sheet: Data
Workbook: pivots_dashboards.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 you need for this video#
Our sales data#
- In A1 type
Office Products Sales 2025 - Format A1: bold, font size 16
- Paste: at A3, rows [['Date', 'Region', 'Rep', 'Category', 'Product', 'Units', 'Unit Price'], ['1-Jan-2025', 'North', 'Ben', 'Furniture', 'Desk', 2, 420], ['7-Jan-2025', 'West', 'Farid', 'Office Supplies', 'Paper Box', 12, 38], ['24-Jan-2025', 'East', 'Emma', 'Technology', 'Laptop', 2, 1250], ['25-Jan-2025', 'East', 'Emma', 'Office Supplies', 'Paper Box', 27, 38], ['28-Jan-2025', 'South', 'Chloe', 'Technology', 'Laptop', 3, 1250], ['2-Feb-2025', 'North', 'Aisha', 'Office Supplies', 'Ink Cartridge', 34, 45], ['4-Feb-2025', 'South', 'Chloe', 'Technology', 'Printer', 2, 230], ['6-Feb-2025', 'West', 'Farid', 'Furniture', 'Bookcase', 9, 180], ['19-Feb-2025', 'West', 'Farid', 'Technology', 'Laptop', 2, 1250], ['28-Feb-2025', 'North', 'Aisha', 'Furniture', 'Office Chair', 2, 260], ['5-Mar-2025', 'North', 'Aisha', 'Furniture', 'Desk', 5, 420], ['6-Mar-2025', 'North', 'Ben', 'Technology', 'Printer', 4, 230], ['7-Mar-2025', 'East', 'Emma', 'Technology', 'Printer', 4, 230], ['15-Mar-2025', 'South', 'Chloe', 'Office Supplies', 'Ink Cartridge', 9, 45], ['20-Mar-2025', 'East', 'Daniel', 'Furniture', 'Desk', 5, 420], ['27-Mar-2025', 'East', 'Emma', 'Furniture', 'Desk', 5, 420], ['3-Apr-2025', 'South', 'Chloe', 'Technology', 'Printer', 1, 230], ['19-Apr-2025', 'South', 'Chloe', 'Office Supplies', 'Ink Cartridge', 29, 45], ['20-Apr-2025', 'East', 'Daniel', 'Furniture', 'Desk', 5, 420], ['21-Apr-2025', 'West', 'Farid', 'Office Supplies', 'Paper Box', 32, 38], ['27-Apr-2025', 'East', 'Daniel', 'Technology', 'Laptop', 4, 1250], ['1-May-2025', 'East', 'Emma', 'Furniture', 'Desk', 2, 420], ['3-May-2025', 'East', 'Emma', 'Office Supplies', 'Ink Cartridge', 36, 45], ['7-May-2025', 'South', 'Chloe', 'Technology', 'Laptop', 5, 1250], ['10-May-2025', 'West', 'Farid', 'Office Supplies', 'Paper Box', 25, 38], ['16-May-2025', 'South', 'Chloe', 'Furniture', 'Bookcase', 9, 180], ['23-May-2025', 'North', 'Aisha', 'Furniture', 'Office Chair', 3, 260], ['2-Jun-2025', 'North', 'Ben', 'Technology', 'Laptop', 5, 1250], ['10-Jun-2025', 'East', 'Emma', 'Furniture', 'Bookcase', 3, 180], ['22-Jun-2025', 'South', 'Chloe', 'Office Supplies', 'Ink Cartridge', 36, 45], ['24-Jun-2025', 'West', 'Farid', 'Technology', 'Laptop', 2, 1250], ['26-Jun-2025', 'East', 'Daniel', 'Furniture', 'Office Chair', 4, 260], ['28-Jun-2025', 'East', 'Emma', 'Furniture', 'Desk', 5, 420], ['4-Jul-2025', 'East', 'Daniel', 'Furniture', 'Bookcase', 2, 180], ['8-Jul-2025', 'West', 'Farid', 'Furniture', 'Bookcase', 2, 180], ['17-Jul-2025', 'West', 'Farid', 'Furniture', 'Desk', 10, 420], ['27-Jul-2025', 'West', 'Farid', 'Furniture', 'Bookcase', 6, 180], ['28-Jul-2025', 'West', 'Farid', 'Furniture', 'Desk', 10, 420], ['3-Aug-2025', 'East', 'Daniel', 'Technology', 'Laptop', 5, 1250], ['8-Aug-2025', 'West', 'Farid', 'Office Supplies', 'Ink Cartridge', 24, 45], ['12-Aug-2025', 'South', 'Chloe', 'Technology', 'Laptop', 6, 1250], ['14-Aug-2025', 'East', 'Emma', 'Technology', 'Printer', 1, 230], ['24-Aug-2025', 'South', 'Chloe', 'Technology', 'Monitor', 3, 310], ['28-Aug-2025', 'West', 'Farid', 'Technology', 'Monitor', 1, 310], ['2-Sep-2025', 'East', 'Daniel', 'Furniture', 'Office Chair', 3, 260], ['7-Sep-2025', 'West', 'Farid', 'Technology', 'Printer', 4, 230], ['9-Sep-2025', 'East', 'Daniel', 'Furniture', 'Bookcase', 2, 180], ['16-Sep-2025', 'West', 'Farid', 'Technology', 'Laptop', 3, 1250], ['17-Sep-2025', 'West', 'Farid', 'Furniture', 'Desk', 9, 420], ['18-Sep-2025', 'North', 'Ben', 'Technology', 'Printer', 1, 230], ['3-Oct-2025', 'North', 'Ben', 'Technology', 'Laptop', 2, 1250], ['4-Oct-2025', 'North', 'Aisha', 'Furniture', 'Desk', 8, 420], ['6-Oct-2025', 'East', 'Emma', 'Office Supplies', 'Paper Box', 15, 38], ['10-Oct-2025', 'South', 'Chloe', 'Furniture', 'Bookcase', 4, 180], ['19-Oct-2025', 'East', 'Emma', 'Office Supplies', 'Paper Box', 22, 38], ['20-Oct-2025', 'West', 'Farid', 'Office Supplies', 'Paper Box', 38, 38], ['2-Nov-2025', 'North', 'Aisha', 'Office Supplies', 'Paper Box', 26, 38], ['5-Nov-2025', 'South', 'Chloe', 'Office Supplies', 'Paper Box', 17, 38], ['13-Nov-2025', 'West', 'Farid', 'Office Supplies', 'Ink Cartridge', 39, 45], ['18-Nov-2025', 'East', 'Emma', 'Office Supplies', 'Paper Box', 36, 38], ['19-Nov-2025', 'North', 'Aisha', 'Furniture', 'Office Chair', 2, 260], ['23-Nov-2025', 'South', 'Chloe', 'Technology', 'Monitor', 5, 310], ['24-Nov-2025', 'East', 'Emma', 'Furniture', 'Bookcase', 10, 180], ['25-Nov-2025', 'West', 'Farid', 'Technology', 'Monitor', 6, 310], ['2-Dec-2025', 'East', 'Emma', 'Technology', 'Laptop', 5, 1250], ['3-Dec-2025', 'North', 'Ben', 'Technology', 'Monitor', 5, 310], ['11-Dec-2025', 'West', 'Farid', 'Office Supplies', 'Ink Cartridge', 10, 45], ['24-Dec-2025', 'North', 'Aisha', 'Furniture', 'Office Chair', 10, 260], ['27-Dec-2025', 'North', 'Aisha', 'Technology', 'Monitor', 3, 310], ['28-Dec-2025', 'North', 'Aisha', 'Furniture', 'Bookcase', 5, 180]]
- Format A4:A73: number format d-mmm-yyyy
Look at what we've got. Headings in row three, and the data runs from row four all the way down to row seventy three. The very first order is on the first of January, from Ben in the North: two desks at four hundred and twenty dollars each.
- Table: range A3:G73, name Sales
- In H3 type
Revenue - In H4 type the formula
=[@Units]*[@[Unit Price]]
One formula, and Excel filled it down all seventy rows for us. That's a calculated column, one of the best features of tables. And notice I renamed the table to Sales in the Table Name box. Remember that name, because every PivotTable today will use it as its source.
- Format G4:H73: number format currency
- Format B3:H3: AutoFit columns
With the whole revenue column selected, look at the status bar at the bottom. The sum is 128,625 dollars, the count is seventy and the average is 1,837.50 dollars. So our company made just over one hundred and twenty eight thousand dollars across these seventy orders. That's our first fact. Now let's ask better questions.
Is the data pivot-ready?#
What is a PivotTable?#
The four areas of a PivotTable#
Your first PivotTable#
- Pivot: source Sales, sheet Revenue by Region, rows ['Region'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']]
And there it is. Four regions, each with its total revenue, plus a grand total at the bottom. East has 40,690 dollars, North has 26,518 dollars, South has 26,986 dollars and West has 34,431 dollars. And the grand total is 128,625 dollars, exactly what the status bar told us a moment ago. That's a nice way to check a pivot: the grand total should always match your data.
Reading the PivotTable Fields pane#
Starting in D3, type these rows (one value per cell, moving right):
|
East revenue|=GETPIVOTDATA("Total Revenue",$A$3,"Region","East")| |---|---|Starting in D4, type these rows (one value per cell, moving right):
|
All regions|=GETPIVOTDATA("Total Revenue",$A$3)| |---|---|Format E3:E4: number format $#,##0
AutoFit the width of column D:E (double-click its right edge)
E3 shows 40,690 dollars, East's revenue, and E4 shows the grand total, 128,625 dollars.
Why GETPIVOTDATA matters#
- Click B4
And with all four regions selected, the status bar sum is 128,625. Same total again. Everything adds up.
Adding a second dimension: columns#
- Pivot: source Sales, sheet Region x Category, rows ['Region'], columns ['Category'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']]
Now we have a cross-tab: regions down the side, categories across the top, and every cell is the revenue for that region and category combination. Look at Technology, the last category column. East sold 21,150 dollars of technology and South sold 20,670 dollars. South is interesting. It sold only 2,340 dollars of furniture, but over twenty thousand dollars of technology. South is basically a laptop region. The column of grand totals on the right shows each region's total, the same numbers as our first pivot, and the bottom row shows each category's total. Technology is the biggest at 66,040 dollars. And the bottom-right corner is the grand total of everything, 128,625 dollars again.
A tip on rows versus columns#
Pivot layouts and styles#
Several value fields at once#
- Pivot: source Sales, sheet Region Stats, rows ['Region'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0'], ['Units', 'count', 'Orders', '0']]
- Python: code pt = ws.PivotTables(1) df = pt.AddDataField(pt.PivotFields('Revenue'), 'Average Order', -4106) df.NumberFormat = '$#,##0'
Three columns of insight in one small table. East: 40,690 dollars from 22 orders, an average of about 1,850 dollars per order. North: 16 orders, averaging 1,657 dollars. South: only 13 orders, the fewest, but look at the average: 2,076 dollars, the highest of any region. And West: 19 orders, averaging 1,812 dollars. So South doesn't sell often, but when it sells, it sells big. That's exactly the kind of insight a single total would hide. Overall, 70 orders with an average order of about 1,838 dollars. Excel shows one thousand eight hundred and thirty eight because the exact average is 1,837.5 and our format rounds it.
Changing the summary function and number format#
- Double-click B5 to edit it
Excel instantly creates a brand new sheet, showing every single order behind that number. All 16 North orders, as their own little table. This is called drilling down, or Show Details. If a number in a pivot ever looks strange, double-click it and see exactly which rows made it up. Just remember this new sheet is a copy. You can delete it when you're done, and nothing in your data changes.
Max and Min: biggest and smallest orders#
- Pivot: source Sales, sheet Order Extremes, rows ['Rep'], values [['Revenue', 'max', 'Biggest Order', '$#,##0']], style PivotStyleMedium9
- Python: code pt = ws.PivotTables(1) df = pt.AddDataField(pt.PivotFields('Revenue'), 'Smallest Order', -4139) df.NumberFormat = '$#,##0'
Chloe has the biggest single order of anyone: seven thousand five hundred dollars. That was six laptops in August. Ben, Daniel and Emma each have a biggest order of six thousand two hundred and fifty dollars, which is five laptops. Farid's biggest is four thousand two hundred, Aisha's is three thousand three hundred and sixty. The smallest orders are mostly single printers at two hundred and thirty dollars. Aisha's smallest is five hundred and twenty, Daniel's three hundred and sixty, and Farid's three hundred and ten. And the grand total row shows the biggest and smallest orders in the whole company, seven thousand five hundred and two hundred and thirty. Max and Min are great for spotting outliers. If a number here looks impossible, like a ninety thousand dollar order for paper, you've probably found a data entry mistake.
Sorting and top items#
- Pivot: source Sales, sheet Top Products, rows ['Product'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']], sort_by ['Product', 'Total Revenue', 'desc']
Now the products are ranked by revenue. Laptops lead by a mile with 55,000 dollars. Then desks with 27,720 dollars. Then a big gap down to ink cartridges at 9,765 dollars, paper boxes at 9,500 dollars and bookcases at 9,360 dollars. At the bottom are monitors, office chairs, and printers with just 3,910 dollars. And here's a nice thing: this sort is remembered. If the numbers change later, the pivot re-sorts itself automatically.
- Python: code pt = ws.PivotTables(1) pt.PivotFields('Product').PivotFilters.Add2(1, pt.DataFields(1), 3)
Now only three products remain: Laptop, Desk and Ink Cartridge. And notice the grand total changed to 92,485 dollars. A filtered pivot totals only what it shows. That's 71.9 percent of all our revenue, from just three of our eight products. That's a classic pattern in business: a few products make most of the money. Despite the name Top 10, you can pick any number, and you can switch Top to Bottom to find your weakest products instead.
A word of warning about filtered totals#
Practice break#
Filters and the Filters area#
- Pivot: source Sales, sheet Rep Pivot, rows ['Rep'], filters ['Region'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']], sort_by ['Rep', 'Total Revenue', 'desc']
Excel placed the Region filter in the top-left corner, in row one, with the word All next to it, meaning every region is included. Below it are our six reps. Farid leads with 34,431 dollars, then Chloe with 26,986 dollars, Emma with 22,700 dollars, Daniel with 17,990 dollars, Aisha with 14,228 dollars and Ben with 12,290 dollars.
Thinking in percentages#
- Python: code pt = ws.PivotTables(1) df = pt.AddDataField(pt.PivotFields('Revenue'), 'Share of Total', -4157) df.Calculation = 8 df.NumberFormat = '0.0%'
Now each rep has a dollar amount and a share. Farid brings in 26.8 percent of all revenue, more than a quarter of the company on his own. Chloe has 21.0 percent, Emma 17.6 percent, Daniel 14.0 percent, Aisha 11.1 percent and Ben 9.6 percent. And the shares add up to one hundred percent in the grand total row, which is always a good sanity check. Notice something important, though. Farid and Chloe are the only reps in their regions, while the North and East split their region between two people. So a fair comparison needs context. Numbers tell you what, not why.
- Python: code ws.PivotTables(1).PivotFields('Region').CurrentPage = 'North'
Only the North's reps remain: Aisha and Ben. Aisha has 14,228 dollars and Ben has 12,290 dollars, a total of 26,518 dollars. And look at the percentages. They changed! Aisha is now 53.7 percent and Ben 46.3 percent. That's because percent of grand total means percent of what the pivot is showing. With the filter on, the grand total is the North's total, so these are shares within the North. That's the percentage thinking I want you to build: always ask, a percentage of what?
- Python: code ws.PivotTables(1).PivotFields('Region').ClearAllFilters()
All six reps are back, and the shares are of the whole company again.
Percent of row: the category mix#
- Pivot: source Sales, sheet Category Mix, rows ['Region'], columns ['Category'], values [['Revenue', 'sum', 'Revenue Share', '0.0%']]
- Python: code ws.PivotTables(1).DataFields(1).Calculation = 6
Now every row adds up to one hundred percent, and the regions are easy to compare even though their totals are very different. Look at South. Seventy six point six percent of its revenue is technology, and only eight point seven percent is furniture. West is the opposite: the most balanced region, with forty four point three percent furniture, twenty one point three percent office supplies and only thirty four point four percent technology. East and North sit in between, with roughly half their revenue from technology: fifty two percent for East and forty six point seven for North. And the grand total row shows the company mix: thirty three point seven percent furniture, fifteen percent office supplies and fifty one point three percent technology. So if the company launched a new laptop promotion, which region would you expect to benefit most? South already loves technology. West has the most room to grow.
Grouping by month and quarter#
- Click Data!A4
- In I3 type
Month - In I4 type the formula
=TEXT([@Date],"mmm")
The whole column fills instantly: Jan, Jan, Jan, then Feb, and so on down to Dec. TEXT takes a number, and remember a date is really a number, and displays it in whatever format you ask for. But the result is text, which is perfect as a label.
- In J3 type
MonthNum - In J4 type the formula
=MONTH([@Date]) - In K3 type
Quarter
- In K4 type the formula
="Q"&ROUNDUP([@MonthNum]/3,0) - Format I3:K3: AutoFit columns
Let's check the quarter formula. January is month one. One divided by three is a third, rounded up is one, so Q1. March is three, divided by three is exactly one, still Q1. April is four. Four divided by three is one point three three, rounded up is two, so Q2. December is twelve, divided by three is four, Q4. It works for every month. Why have both Month and MonthNum? Because text months sort alphabetically in most places: April, August, December. The number lets us sort and calculate in the right order. You'll see MonthNum earn its keep later in our dashboard.
A monthly revenue pivot#
- Pivot: source Sales, sheet Monthly Trend, rows ['Month'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']]
Look at the order of the months. January, February, March, all the way to December. In the right order, not alphabetical! That's because Excel has built-in custom lists for month names and day names, and pivots use them when sorting. A lovely little bonus. Now the trend. January was 8,572 dollars, and February was the quietest month at 6,630 dollars. Sales built up through autumn and winter: May reached 12,060 dollars, June 14,050 dollars, and August was the best month of the year with 16,300 dollars. Then spring was steadier, around nine to ten thousand a month, and December finished strongly at 12,680 dollars. And because we're in Australia, remember June to August is winter. Perhaps our customers like to spend before the end of the financial year in June, and again once the new financial year begins in July. That's a question worth asking the sales team.
- Pivot: source Sales, sheet Quarterly, rows ['Quarter'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']]
Quarter one: 23,747 dollars. Quarter two: 35,961 dollars. Quarter three: 36,320 dollars. Quarter four: 32,597 dollars. So the first quarter was clearly the weakest, about 18 percent of the year, and the middle of the year was strongest.
Excel's automatic date grouping#
Refreshing a pivot after the data changes#
Click Data!A74
Starting in the next empty row, type these rows (one value per cell, moving right):
|
> 30-Dec-2025|West|Farid|Technology|Laptop|3|1250| |---|---|---|---|---|---|---|Click H74
Look: the table grew to include the new row, and the calculated columns filled in by themselves. Revenue is three thousand seven hundred and fifty dollars, the month is Dec, month number twelve, quarter Q4. This is the second big reason we use tables. Because the pivots point at the table named Sales, not a fixed range like A3 to H73, they'll pick up new rows when we refresh.
- Click Monthly Trend!B15
It still says 12,680 dollars. The pivot hasn't noticed the new order yet. Its grand total still shows 128,625 dollars.
- On the ribbon choose Data > Refresh All
- Click B15
December jumped to 16,430 dollars, three thousand seven hundred and fifty more. And the grand total is now 132,375 dollars. Refresh All refreshed every PivotTable in the workbook at once. So if we visited the Revenue by Region sheet, West would now show 38,181 dollars, and in the Quarterly pivot quarter four would be 36,347 dollars.
Refresh tips#
When a pivot goes wrong#
Slicers: filters you can click#
- Pivot: source Sales, sheet Slicer Demo, rows ['Category'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0'], ['Units', 'sum', 'Total Units', '#,##0']]
Furniture: 43,320 dollars from 142 units. Office Supplies: 19,265 dollars from 467 units. Technology: 69,790 dollars from only 87 units. Office supplies sell the most units by far, but technology makes the most money. Cheap things sell in bulk, expensive things in ones and twos.
- Python: code import pythoncom pt = ws.PivotTables(1) sc = wb.SlicerCaches.Add2(pt, 'Region', 'Slicer_Region_Demo') c = ws.Range('F3') try: sc.Slicers.Add(ws, pythoncom.ArgNotFound, 'Region_Demo', 'Region', c.Top, c.Left, 150, 190) except Exception: sc.Slicers.Add(ws, '', 'Region_Demo', 'Region', c.Top, c.Left, 150, 190)
There's our slicer, with four buttons: East, North, South and West. All of them are highlighted, which means all regions are included.
- Python: code sc = wb.SlicerCaches('Slicer_Region_Demo') for n in ['North', 'South', 'West']: sc.SlicerItems(n).Selected = False
The pivot instantly shows only the East region. East's furniture brought in 14,120 dollars, office supplies 5,420 dollars and technology 21,150 dollars, for a total of 40,690 dollars. And look at the slicer: East is highlighted and the others are greyed out. Nobody needs to ask what they're looking at. To select more than one region, hold the Control key while clicking, or turn on the multi-select button at the top of the slicer.
- Python: code wb.SlicerCaches('Slicer_Region_Demo').ClearManualFilter()
All four regions are back, and the total is 132,375 dollars again.
More slicer tricks#
Charts: choosing the right one#
A column chart#
Sheet: Column Charts
In A1 type
Revenue and units by regionFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Region|Revenue|Units| |---|---|---|
Starting in B4, type these rows (one value per cell, moving right):
|
=SUMIFS(Sales[Revenue],Sales[Region],A4)|=SUMIFS(Sales[Units],Sales[Region],A4)| |---|---|Fill B4:C7 down (drag the fill handle, or select it and press Ctrl+D)
Format B4:B7: number format $#,##0
- Format A3:C3: bold, font colour White, Background 1, fill Blue, Accent 1
- AutoFit the width of column A:C (double-click its right edge)
North 26,518 dollars, South 26,986 dollars, East 40,690 dollars and West 38,181 dollars. These include Farid's late order, because SUMIFS on a table always sees every row, with no refresh needed. The status bar sum is 132,375, our full total.
- Select A3:B7 and insert a column chart titled “Revenue by region”
There's our column chart. Four columns, and East and West clearly tower over North and South. Let me talk you through the extras I added, because they're what separate a quick chart from a professional one. The chart title says exactly what the chart shows. Never leave it as the default word Chart Title, or as the field name Revenue. The axis titles tell the reader the units, regions along the bottom and revenue in dollars up the side. And data labels print the exact value on top of each column, so nobody has to squint at the axis. I also removed the legend. With only one series, a legend just repeats the title.
Chart tools and moving charts#
Recommended Charts and quick changes#
A combo chart#
- Select A3:C7 and insert a column chart titled “Revenue vs units by region”
- Python: code co = ws.ChartObjects(ws.ChartObjects().Count) s = co.Chart.SeriesCollection(2) s.ChartType = 65 s.AxisGroup = 2
Now we can see both measures properly. The columns show revenue on the left axis, and the line shows units on the right axis. And look at the story it tells. West sells the most units, 247, but East makes slightly more money, 40,690 dollars against 38,181 dollars, from only 203 units. Why? West sells lots of cheap paper and ink, while East sells far more laptops: twenty thousand dollars of laptops, against twelve and a half thousand in the West. A combo chart is perfect for showing that kind of relationship. One caution: two axes can confuse people. Always label them clearly, and only use a combo when the comparison really matters.
Bar charts for many items#
Sheet: Bar and Pie
In A1 type
Products and categoriesFormat A1: bold, font size 14
Starting in A3, type these rows (one value per cell, moving right):
|
Product|Revenue| |---|---|
- In B4 type the formula
=SUMIFS(Sales[Revenue],Sales[Product],A4) - Fill B4:B11 down (drag the fill handle, or select it and press Ctrl+D)
- Format B4:B11: number format $#,##0
- AutoFit the width of column A:B (double-click its right edge)
- Sort: range A3:B11, by Revenue, order asc
Printers are at the top with 3,910 dollars and laptops at the bottom with 58,750 dollars, which now includes Farid's late order. Why smallest first? Because a bar chart draws the first row at the bottom. So to get the biggest bar at the top, the data must be sorted smallest to largest. It's backwards, but it works.
- Select A3:B11 and insert a bar chart titled “Revenue by product”
Now we have a ranked bar chart. Laptops dominate at the top, desks second, and everything else is much smaller. The product names read naturally from left to right. If this were a column chart, those long names would be squashed or tilted on an angle.
Pie and doughnut charts, and when not to use them#
Starting in A14, type these rows (one value per cell, moving right):
|
Category|Revenue| |---|---|
- In B15 type the formula
=SUMIFS(Sales[Revenue],Sales[Category],A15) - Fill B15:B17 down (drag the fill handle, or select it and press Ctrl+D)
- Format B15:B17: number format $#,##0
Technology 69,790 dollars, furniture 43,320 dollars and office supplies 19,265 dollars.
- Select A14:B17 and insert a pie chart titled “Share of revenue by category”
Technology is just over half the pie. To be exact it's 52.7 percent of revenue, furniture is 32.7 percent and office supplies 14.6 percent. With three clearly different slices, the message is instant: technology is half our business. A tip: in the data label options you can show percentages instead of dollar values, which is often what people want from a pie. And the doughnut chart, right next to pie in the same gallery, is a pie with a hole in the middle. Many designers prefer it, because the hole is a great place for a total. We'll use a doughnut in our dashboard.
A line chart and PivotCharts#
- Click Monthly Trend!A3
- Select A3:B15 and insert a line_markers chart titled “Monthly revenue 2025”
There's our trend line. The months run along the bottom in the right order, because the chart follows the pivot. You can see the dip in February at 6,630 dollars, the climb to 16,300 dollars in August, then a quieter spring, and a strong finish in December at 16,430 dollars. With Farid's late order, December has just overtaken August as the best month of the year. A line makes the ups and downs obvious in a way a table of twelve numbers never could. Look at the grey buttons on the chart, like Total Revenue and Month. Those are the field buttons. You can filter the chart right from them. If you'd rather hide them for a cleaner look, go to the PivotChart Analyze tab, Field Buttons, and choose Hide All.
Common chart mistakes#
Practice break: charts#
Sparklines: tiny charts in a cell#
Sheet: Sparklines
In A1 type
Monthly revenue by regionFormat A1: bold, font size 14
Starting in A4, type these rows (one value per cell, moving right):
|
Region|1|2|3|4|5|6|7|8|9|10|11|12|Trend| |---|---|---|---|---|---|---|---|---|---|---|---|---|---|
- In B5 type the formula
=SUMIFS(Sales[Revenue],Sales[Region],$A5,Sales[MonthNum],B$4) - Select B5
- Press Ctrl+C
- Select B5:M8
- Press Ctrl+V
- Press Esc
- Format B5:M8: number format #,##0
- Format A4:N4: bold, font colour White, Background 1, fill Blue, Accent 1
Now each cell is one region's revenue in one month. For example, South in August, cell I6, is 8,430 dollars, South's best month. And there are some zeros. Months where a region made no sales at all, like the North in April and July.
Python: code g = ws.Range('N5:N8').SparklineGroups.Add(1, 'B5:M8') try: g.Points.Highpoint.Visible = True except Exception: pass
Format N:N: column width 22
Four little trend lines, one per region, each in its own cell. Each one is scaled to its own row, and the high point marker shows each region's best month. East peaked in April with 7,100 dollars, North in June with 6,250 dollars, South in August with 8,430 dollars and West in July with 9,840 dollars. That's a lot of information in a very small space. Sparklines are wonderful on dashboards and in monthly reports. One thing to know: by default each sparkline has its own scale, so a small wiggle in a quiet region can look as dramatic as a big swing in a busy one. On the Sparkline tab, the Axis button lets you set the same minimum and maximum for the whole group when you need a fair comparison. And to remove sparklines, the Delete key doesn't work. You use the Clear button on the Sparkline tab.
Project: a one-page sales dashboard#
Dashboard design principles#
- Pivot: source Sales, sheet Dashboard, rows ['Region'], values [['Revenue', 'sum', 'Total Revenue', '$#,##0']], sort_by ['Region', 'Total Revenue', 'desc']
- Python: code ws.PivotTables(1).HasAutoFormat = False
- Format A:A: column width 16
Our region table: East 40,690 dollars, West 38,181 dollars, South 26,986 dollars and North 26,518 dollars, with the grand total of 132,375 dollars.
In A1 type
Sales Dashboard 2025Format A1: bold, font size 20, font colour Blue, Accent 1
Starting in D3, type these rows (one value per cell, moving right):
|
Total Revenue| |Units Sold| |Orders| |Average Order| |Best Region| |---|---|---|---|---|---|---|---|---|
The KPI formulas#
Starting in D4, type these rows (one value per cell, moving right):
|
=SUM(Sales[Revenue])| |=SUM(Sales[Units])| |=ROWS(Sales)| |=D4/H4| |=INDEX(A4:A7,MATCH(MAX(B4:B7),B4:B7,0))| |---|---|---|---|---|---|---|---|---|
Total revenue is 132,375 dollars. Units sold, 696. Orders, 71, seventy plus Farid's late one. The average order is about 1,864 dollars. And the best region is East. Let me explain the INDEX MATCH. MAX of B4 to B7 finds the largest revenue. MATCH finds its position in the list, which is one, the first row. And INDEX returns the first item of A4 to A7, which is East. Yes, our pivot is sorted so East is already at the top. But the formula doesn't rely on that. If the sort ever changed, it would still find the right region. You could also write it with XLOOKUP, which we'll meet properly in the next video.
- Format D3:E3: merge & centre
- Format F3:G3: merge & centre
- Format H3:I3: merge & centre
- Format J3:K3: merge & centre
- Format L3:M3: merge & centre
- Format D4:E4: merge & centre
- Format F4:G4: merge & centre
- Format H4:I4: merge & centre
- Format J4:K4: merge & centre
- Format L4:M4: merge & centre
- Format D3:M3: bold, font colour White, Background 1, fill Blue, Accent 1, align center
- Format D4:M4: bold, font size 16, fill #DDEBF7, align center
- Format D4:E4: number format $#,##0
- Format F4:I4: number format #,##0
- Format J4:K4: number format $#,##0
Now that looks like a dashboard header. Five tiles, each with a clear label and a big number you can read from across the room.
The chart data#
Starting in A10, type these rows (one value per cell, moving right):
|
Category|Revenue| |---|---|
- In B11 type the formula
=SUMIFS(Sales[Revenue],Sales[Category],A11) - Fill B11:B13 down (drag the fill handle, or select it and press Ctrl+D)
- Format B11:B13: number format $#,##0
Starting in D7, type these rows (one value per cell, moving right):
|
Month|Revenue| |---|---|In D8 type the formula
=TEXT(DATE(2025,SEQUENCE(12),1),"mmm")In E8 type the formula
=SUMIFS(Sales[Revenue],Sales[MonthNum],SEQUENCE(12))Format E8:E19: number format $#,##0
Look at that! Each formula was typed once, in a single cell, but the results spilled down twelve rows. SEQUENCE of twelve makes the numbers one to twelve. DATE turns each one into the first day of that month, and TEXT turns that into Jan, Feb, Mar and so on. And the SUMIFS runs once for each month number, giving twelve monthly totals, from 8,572 dollars in January to 16,430 dollars in December. Those are called dynamic arrays, and they're one of the most powerful features in modern Excel. We'll go deep on them in video five. For now, just enjoy the magic.
Building the chart grid#
- Select A3:B7 and insert a column chart titled “Revenue by region”
- Select D7:E19 and insert a line_markers chart titled “Monthly revenue”
- Select A10:B13 and insert a doughnut chart titled “Revenue by category”
Three charts in a neat grid, all the same size, lined up along the same rows and columns. The region chart shows East and West leading. The line shows the monthly ups and downs. And the doughnut shows technology as about half of revenue.
- Python: code import pythoncom pt = ws.PivotTables(1) sc = wb.SlicerCaches.Add2(pt, 'Category', 'Slicer_Dash_Category') c = ws.Range('O20') try: sc.Slicers.Add(ws, pythoncom.ArgNotFound, 'Dash_Category', 'Category', c.Top, c.Left, 180, 150) except Exception: sc.Slicers.Add(ws, '', 'Dash_Category', 'Category', c.Top, c.Left, 180, 150)
The slicer has three buttons: Furniture, Office Supplies and Technology. All are selected right now.
- Python: code sc = wb.SlicerCaches('Slicer_Dash_Category') for n in ['Furniture', 'Office Supplies']: sc.SlicerItems(n).Selected = False
The region pivot and its PivotChart now show technology sales only. East 21,150 dollars, South 20,670 dollars, West 15,590 dollars and North 12,380 dollars. Look how South jumped from third place up to second. For technology, South is nearly level with East. And the Best Region KPI still says East, because our INDEX MATCH formula reads the pivot. It recalculates with every slicer click. To be honest with you about the design: the other KPIs and the two other charts use SUMIFS on the whole table, so they always show the full year. That's a deliberate choice here, but in your own dashboards, make it clear to the reader which parts respond to the slicer.
- Python: code wb.SlicerCaches('Slicer_Dash_Category').ClearManualFilter()
And everything is back to the full picture.
Tidying up#
- On the ribbon choose View > Gridlines
- Format A10:B10: bold, font colour White, Background 1, fill Blue, Accent 1
- Format D7:E7: bold, font colour White, Background 1, fill Blue, Accent 1
- Format A3:B8: outline border
Instantly cleaner. The page looks like a designed report now, with white space separating the sections. Notice I reused one blue throughout. Consistent colour is the cheapest way to look professional. Pick one main colour and one highlight colour, and stick to them.
The insight summary#
- In A22 type
Key insights - Format A22: bold, font size 14, font colour Blue, Accent 1
- In A23 type
Revenue 132k from 71 orders; avg order $1,864.
Let me double-check those facts against our numbers. Revenue is 132,375 dollars, rounded to one hundred and thirty two k. The average order is 1,864 dollars. Technology is 52.7 percent, so fifty three percent when rounded. Laptops are 58,750 dollars, or 44.4 percent, so forty four percent. Q1 was the weakest quarter at 23,747 dollars, and December the best month at 16,430 dollars, just ahead of August. And North's technology revenue, 12,380 dollars, is the lowest of the four regions. Every statement is backed by a number on the page.
- Click A1
And there it is: our one-page sales dashboard. Across the top, a clear title and five KPI tiles. Down the left, the region pivot, the category and monthly tables feeding the charts. In the middle and right, a grid of three charts and a slicer. And at the bottom left, the key insights in plain English. And because everything is built on the Sales table, next month we simply paste the new orders at the bottom of the table, click Refresh All, and the whole dashboard updates. The SUMIFS formulas and spilled arrays update on their own, and the refresh takes care of the pivot and its chart.
Sharing and protecting your dashboard#
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.



