Chart templates in Excel - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants you to create consistent sales charts for monthly reports using a chart template.

📊 Data: You have monthly sales data for three products over six months.

🎯 Deliverable: Create a chart template from a sample chart and apply it to new sales data to produce consistent charts quickly.

Sample Data

Month	Product A	Product B	Product C
January	1200	900	700
February	1300	950	750
March	1250	1000	800
April	1400	1100	850
May	1500	1150	900
June	1600	1200	950

1

Step 1: Select the sales data including headers (A1:D7).

No formula needed.

Expected Result

Cells A1 to D7 are selected.

2

Step 2: Insert a clustered column chart using the selected data.

Go to Insert tab > Charts group > Insert Column or Bar Chart > Clustered Column.

Expected Result

A clustered column chart showing sales for Products A, B, and C from January to June appears.

3

Step 3: Format the chart: add chart title 'Monthly Sales', add data labels, and set colors for each product series.

Click chart title to edit; right-click data series > Add Data Labels; use Chart Tools > Format to set colors.

Expected Result

Chart has title 'Monthly Sales', data labels visible on columns, and distinct colors for each product.

4

Step 4: Save the chart as a template for reuse.

Right-click the chart area > Save as Template > name it 'SalesChartTemplate.crtx'.

Expected Result

Chart template 'SalesChartTemplate.crtx' is saved in the default templates folder.

5

Step 5: Prepare new sales data for July to December in the same format below the original data.

Enter months July to December in A8:A13 and sales data for Products A, B, and C in B8:D13.

Expected Result

New data for July to December is entered in cells A8:D13.

6

Step 6: Insert a new chart using the saved template on the new data.

Select new data A8:D13 > Insert tab > Charts group > Templates > select 'SalesChartTemplate'.

Expected Result

A new chart with the same style and formatting as the template appears, showing sales from July to December.

7

Step 7: Verify the new chart matches the template style and displays correct data.

Check chart title, colors, data labels, and data range.

Expected Result

New chart title is 'Monthly Sales', colors and labels match template, and data reflects July to December sales.

Final Result

Monthly Sales Chart Template Applied

| Month    | Product A | Product B | Product C |
|----------|-----------|-----------|-----------|
| July     | 1650      | 1250      | 1000      |
| August   | 1700      | 1300      | 1050      |
| September| 1750      | 1350      | 1100      |
| October  | 1800      | 1400      | 1150      |
| November | 1850      | 1450      | 1200      |
| December | 1900      | 1500      | 1250      |

[Clustered Column Chart with consistent colors, data labels, and title 'Monthly Sales']

✓Using a chart template saves time by applying consistent formatting to new charts.

✓The sales trend from July to December shows steady growth for all products.

✓Product A consistently leads in sales, followed by Product B and Product C.

Bonus Challenge

Create a dynamic chart template that automatically updates when new monthly sales data is added.

Show Hint

Use Excel Tables for your data range and create the chart based on the table to allow automatic expansion.