Dynamic charts with data ranges in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a sales chart that updates automatically when new monthly sales data is added.

📊 Data: You have monthly sales data for different products in a table with columns: Month, Product, and Sales.

🎯 Deliverable: Create a dynamic chart that shows total sales per month and updates automatically as new data is added.

Sample Data

Month	Product	Sales
Jan	Apples	120
Jan	Bananas	90
Feb	Apples	150
Feb	Bananas	110
Mar	Apples	130
Mar	Bananas	95
Apr	Apples	160
Apr	Bananas	120

1

Step 1: Create a summary table that shows total sales per month.

In a new area, list unique months in column G starting at G2: Jan, Feb, Mar, Apr. In H2, enter formula: =SUMIF(A:A, G2, C:C) and copy down to H5.

Expected Result

H2=210, H3=260, H4=225, H5=280

2

Step 2: Convert the summary table into an Excel Table for dynamic range.

Select G1:H5 and press Ctrl+T to create a table. Name it 'SalesSummary'.

Expected Result

Table 'SalesSummary' with columns Month and Sales, automatically expanding when new rows are added.

3

Step 3: Create a dynamic named range for the Months in the summary table.

Go to Formulas > Name Manager > New. Name: DynamicMonths. Refers to: =SalesSummary[Month]

Expected Result

DynamicMonths refers to the Month column of SalesSummary table.

4

Step 4: Create a dynamic named range for the Sales in the summary table.

Go to Formulas > Name Manager > New. Name: DynamicSales. Refers to: =SalesSummary[Sales]

Expected Result

DynamicSales refers to the Sales column of SalesSummary table.

5

Step 5: Insert a line chart using the dynamic named ranges for X and Y axes.

Insert > Charts > Line Chart. Right-click chart > Select Data > Add Series. Series name: 'Total Sales'. Series values: =Sheet1!DynamicSales. Horizontal Axis Labels: =Sheet1!DynamicMonths.

Expected Result

Line chart showing total sales per month that updates automatically when new data is added to the summary table.

6

Step 6: Test the dynamic chart by adding new data for May in the original data and updating the summary table.

Add rows: May, Apples, 170 and May, Bananas, 130. Add 'May' in G6 and formula =SUMIF(A:A, G6, C:C) in H6.

Expected Result

Summary table updates with May total sales 300. Chart updates automatically to include May.

Final Result

Month | Sales
Jan   | 210
Feb   | 260
Mar   | 225
Apr   | 280
May   | 300

Line Chart:
  Sales
300|       *
280|     *
260|    *
225|   *
210|  *
    Jan Feb Mar Apr May

✓Total sales increase steadily from January to May.

✓Dynamic chart updates automatically when new monthly data is added.

✓Using Excel Tables and named ranges makes charts flexible and easy to maintain.

Bonus Challenge

Create a dynamic chart that breaks down sales by product per month using a stacked column chart.

Show Hint

Use a pivot table to summarize sales by month and product, then create a chart based on the pivot table that updates when data changes.