Same clean MYMart data, more chart types. This week: trends over time, comparisons, distributions, and data on a map — each built with a pivot table + the Chart editor.
By the end of this week you can…
Summarise data with a pivot table to feed any chart.
Build a line chart (trend), a bar chart (comparison), and stacked / clustered bars.
Show a distribution with a histogram and a boxplot (the five-number method).
Reuse your clean data. Work on the Clean sheet from Week 5. Every chart below starts the same way: build a small pivot table (Insert ▸ Pivot table) to get the numbers, then Insert ▸ Chart.
6.1 Line chart — monthly sales trend
Lines are for change over time. We want total Sales per Month.
Insert ▸ Pivot table → Rows = Month, Values = SUM of Sales.
Months sort alphabetically by default (Apr, Aug…). Fix the order: sort the Rows by a custom month order, or add a Month No. helper column (Jan=1…Dec=12) and sort by it.
Select the pivot output → Insert ▸ Chart → Line chart. Add a title and label the axis “Sales (RM)”.
Expected result — MYMart sales by month (RM ’000)
A February dip and a strong December peak (RM47k) — the year-end shopping season shows up clearly.
6.2 Bar chart — sales by state
Bars compare categories. State names are long, so a horizontal bar reads best (Chapter 2).
Pivot: Rows = State, Values = SUM of Sales; sort descending.
Select it → Insert ▸ Chart → choose Bar chart (horizontal). Keep the zero baseline (it's on by default — don't move it).
Expected result — sales by state (RM ’000)
Selangor (RM104k) and KL together are the powerhouse — the Klang Valley dominates MYMart's sales.
● Session 1 recap
Every chart starts with a pivot table to summarise, then the Chart editor. Use a line for a trend over time (mind the month order), and a bar to compare categories (horizontal for long names, and always a zero baseline).
6.3 Stacked & clustered bars — category by quarter
To show two things at once — quarter and category — pivot with both.
Pivot: Rows = Quarter, Columns = Product Category, Values = SUM of Sales. (For a clean demo, filter to the top 3 categories.)
Chart it as a Column chart. In Customize ▸ Chart style ▸ Stacking: None = clustered (compare categories within a quarter), Standard = stacked (see the quarter total and its parts).
Expected result — clustered: sales by quarter & category (RM ’000)
Fashion
Baby & Kids
Electronics
Clustered lets you compare the three categories within each quarter — Baby & Kids surges in Q4 (year-end), overtaking Fashion.
6.4 Histogram — customer age distribution
Select the Age column → Insert ▸ Chart → Histogram.
In Customize ▸ Histogram set a sensible Bucket size (e.g. 10) so bins read as 18–25, 25–35, …
Expected result — customer age (count per bin)
144118–25
213925–35
192535–45
195645–55
209155–65
Fairly even across adult ages, with a slight bulge at 25–35 — MYMart serves a broad customer base.
6.5 Boxplot — age spread by gender
Google Sheets has no built-in boxplot, so we build the five-number summary and draw a candlestick chart.
For each gender compute: =MIN(range), =QUARTILE(range,1), =MEDIAN(range), =QUARTILE(range,3), =MAX(range).
Lay them out as a small table, then Insert ▸ Chart → Candlestick chart — it draws the box (Q1–Q3) and whiskers (min/max) for you.
Expected result — customer age by gender
The boxes are almost identical — median age 41 for both genders, same spread. A boxplot makes “no real difference” obvious at a glance.
6.6 Geo chart — sales on a map
Pivot: Rows = State, Values = SUM of Sales.
Select it → Insert ▸ Chart → Geo chart. In Customize ▸ Geo set Region = Malaysia so it shades states, not countries.
Expected result — MYMart sales by state, Malaysia
fewermore sales
Darker = more sales. Selangor and KL glow as the hotspot — the same story as the bar chart, but instantly tied to place.
● Watch out — Google Geo charts
Google's geo charts are happiest at country level; sub-country (Malaysian states) can be patchy and needs Region = Malaysia set correctly. If a state won't shade, check the spelling matches Google's names — this is exactly why we cleaned the State column first. Power BI (Week 11) handles Malaysian state maps far better.
● Practice — apply your skills
Open Week 6 Practice.xlsx:
Monthly tab — build a line of café sales, then a bar of the same. Which month peaks?
Members tab — build a histogram of member ages and a boxplot (five-number method). Where do most members sit?
By State tab — build a geo chart of sales by state.
Key terms & definitions
Pivot table
Summarises rows into group totals (e.g. Sales per Month) — the source for almost every chart.
Stacking (None / Standard)
None = clustered bars (compare parts); Standard = stacked bars (see the total and its parts).
Bucket size
The width of each histogram bin (e.g. 10 years).
Five-number summary
Min, Q1, Median, Q3, Max — computed with MIN, QUARTILE, MEDIAN, MAX and drawn as a candlestick/boxplot.
Geo chart
A map that shades regions by value; set Region to control the level (country vs state).
✅ Quick self-check
1. Your monthly line chart shows Apr, Aug, Dec… in the wrong order. Why?
2. To compare 3 categories within each quarter, set Stacking to…