Excel Advanced - Visualising Data with Charts

Lesson outcomes

By the end of this lesson, you will be able to turn retail data into accurate, updateable charts for comparison, trend, composition, relationship and mixed-scale questions.

Choose intentionally

Match column, bar, line, area, pie, doughnut or scatter charts to the question being asked.

Build accurately

Select source ranges, switch rows and columns, add or remove series, and repair incorrect chart sources.

Communicate clearly

Use purposeful titles, labels, legends, axes, number formats and restrained colour.

Keep charts current

Base charts on an Excel Table so new records are incorporated when the Table expands.

Download and prepare the practice data

Recommended: choose Download Excel. The workbook contains Practice Data, Chart Summaries and Instructions worksheets. CSV contains the raw records only; JSON exposes the embedded source dataset.

Open the data

Open the downloaded workbook and select the Practice Data worksheet.

Confirm the controls

There should be 24 records, RM245,410 total sales and 376 units.

Create a Table

Click in the data, press Ctrl+T, confirm headers, then name the Table tblChartSales.

Save a working copy

Save before creating charts so each activity can be tested safely.

Practice dataset preview

The first eight rows are shown below. All 24 records are embedded in this page and included in each download.

1. Choose the chart that fits the question

Business question	Best starting chart	Why	Common mistake
Which region sold most?	Column or bar	Compares category magnitudes from a shared zero baseline.	Using a pie chart when precise comparison matters.
How did sales change by month?	Line	Emphasises sequence, direction and turning points over time.	Treating month names as an unordered category.
How did regional contributions accumulate?	Stacked area	Shows total volume and contribution through time.	Using it when exact regional comparison is required.
What share came from each category?	Pie or doughnut	Shows part-to-whole composition with few categories.	Too many slices, 3-D effects, or a total that is not meaningful.
Does quantity relate to sales?	Scatter	Both axes are numeric, revealing relationship and outliers.	Using a line chart, which implies an ordered sequence.
How do sales and units move together?	Combo	Combines columns and a line; a secondary axis handles different scales.	Leaving the secondary axis unlabeled.

2. Guided chart activities

Activity 1 — Regional sales comparison

Question: Which region generated the greatest sales?

Open Chart Summaries and select A1:B5.
Choose Insert → Column or Bar Chart → Clustered Column.
Replace the title with Sales by Region.
Add data labels and format them as currency with no decimals.
Remove the legend because a single series is already clear.

Checkpoint: Central is highest at RM72,300; Southern is lowest at RM44,500.

I created the clustered column chart. I added a useful title and currency labels. I removed unnecessary chart clutter.

Activity 2 — Monthly sales trend

Question: When did sales peak, and what happened afterwards?

Select D1:E7 on Chart Summaries.
Insert a Line with Markers chart.
Use the title Monthly Sales Trend — Jan to Jun 2026.
Set the vertical axis to currency and keep light horizontal gridlines.

Checkpoint: Sales peak in June at RM48,940, following a rise from RM40,290 in April and RM44,490 in May.

When should I use an area chart instead?

Use an area chart when the magnitude or cumulative contribution is part of the message. For a clean month-to-month trend, a line chart is usually easier to read.

I created and titled the line chart. I checked that months appear in chronological order. I identified the May peak and June decline.

Activity 3 — Category share

Question: What proportion of sales came from each product category?

Select H1:I5.
Insert a Doughnut chart.
Add category name and percentage labels.
Move the legend to the bottom, or remove it if the labels identify every slice.
Use one accent colour to highlight the largest slice.

Checkpoint: Electronics contributes 37.8% of total sales. No category is below 10%, so all four remain readable.

Avoid: exploded slices, 3-D perspective and both a legend and duplicate labels. These add decoration without improving the answer.

I created a doughnut or pie chart. My percentages total approximately 100%. I highlighted only the key slice.

Activity 4 — Sales versus quantity

Question: Do larger quantities always produce higher sales?

On Practice Data, select the Quantity values, then hold Ctrl and select Sales.
Insert Scatter → Markers Only.
Use Quantity for the horizontal X-axis and Sales for the vertical Y-axis.
Add axis titles: Quantity (units) and Sales (RM).
Optionally add a linear trendline.

Repair an incorrect series

Right-click the chart → Select Data → choose the series → Edit. Set X values to ='Practice Data'!$G$2:$G$25 and Y values to ='Practice Data'!$H$2:$H$25.

Interpretation: Quantity and sales are related, but product mix matters. A smaller Electronics order can exceed a larger Office Supplies order.

My X-axis is Quantity and Y-axis is Sales. Both axes have units in their titles. I can explain why the points do not form a perfect line.

Activity 5 — Sales and units combo chart

Question: Did monthly revenue and unit volume move in the same direction?

Select D1:F7.
Choose Insert → Combo → Custom Combo.
Set Sales to Clustered Column.
Set Quantity to Line with Markers and tick Secondary Axis.
Label the left axis Sales (RM) and the right axis Quantity (units).

Checkpoint: June has the highest sales, while January has the highest quantity. The different peaks demonstrate why both measures are useful.

I assigned the correct chart type to each series. Quantity uses the secondary axis. Both axis scales are clearly labeled.

Activity 6 — A chart that expands automatically

Goal: Link a chart to tblChartSales, then prove that the source expands.

Click inside tblChartSales and create a chart using the Month and Sales fields.
Confirm the chart source refers to the Table rather than a fixed range.
Add a new record directly below the last Table row.
Confirm the Table formatting and chart source expand to include the row.
If the chart is based on a summary, refresh the PivotTable or formula summary that feeds it.

Important distinction: A chart based directly on a Table expands with new rows. A chart based on a separate fixed summary does not automatically acquire new categories unless that summary is also dynamic.

My source data is named tblChartSales. I added a test row directly below the Table. I confirmed or refreshed the chart update.

3. Source ranges and data series

Select Data

Right-click a chart and choose Select Data to add, edit, remove and reorder series, change category labels, or switch rows and columns.

Chart filters

Use the chart filter button to hide a category temporarily. This changes the view, not the underlying data.

Hidden and empty cells

Choose whether empty cells appear as gaps, zero values or connected points. Never hide a reporting issue without explaining it.

Structured sources

Excel Tables use field names such as tblChartSales[Sales], making the source easier to understand and extend.

Formula examples for a dynamic summary

If your Excel version supports structured references, regional sales can use:

=SUMIFS(tblChartSales[Sales],tblChartSales[Region],A2)

A fixed-range alternative for mixed or older versions is:

=SUMIFS('Practice Data'!$H$2:$H$25,'Practice Data'!$D$2:$D$25,A2)

4. Business-chart formatting checklist

A good chart should answer one sentence: write that sentence first, then keep only elements that help the reader see the answer.

Title

State the message or measure and period, such as “Monthly Sales Trend — Jan to Jun 2026”.

Axes

Start comparison axes at zero unless a different baseline is clearly justified. Add units and sensible bounds.

Labels

Use direct labels when they reduce eye movement. Avoid labeling every point on a dense trend.

Colour

Use neutral colours for context and one accent for the key finding. Maintain sufficient contrast.

Legend

Keep it only when multiple series cannot be identified directly. Use the same order as the visual.

Number format

Use RM, %, thousands separators and consistent decimals. Do not show precision the business question does not need.

5. Knowledge check

1. Which chart is best for examining the relationship between Quantity and Sales?

Pie Scatter Stacked area

2. Why use a secondary axis in the combo activity?

To add a second title Sales and quantity use different units and scales To reverse the month order

3. What makes a chart source expand when new rows are added?

Base it on an Excel Table Add a 3-D effect Move the legend

Final challenge

Build a one-page retail chart report

Create a clean report containing exactly three charts:

A regional comparison.
A monthly trend.
A sales-versus-quantity relationship chart.

Add a single-sentence insight beneath each chart. Align the charts, apply one colour palette, remove redundant legends, and make sure every measure has units.

Suggested insights

Central produced the highest regional sales at RM72,300.
Monthly sales peaked in June at RM48,940 after rising for three consecutive months.
Quantity generally supports higher sales, but product category and price cause substantial variation.