Column and bar charts in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a visual report showing monthly sales performance by product category to identify which categories perform best.

📊 Data: You have monthly sales data for four product categories: Electronics, Clothing, Home Goods, and Toys. The data shows sales amounts for each category from January to June.

🎯 Deliverable: Create a column chart and a bar chart in Excel to visually compare sales by category and by month.

Sample Data

Month	Electronics	Clothing	Home Goods	Toys
January	12000	8500	7000	4000
February	15000	9000	7500	4500
March	13000	8700	7200	4200
April	16000	9200	7800	4600
May	17000	9500	8000	4800
June	18000	9700	8200	5000

1

Step 1: Select the data range including the headers and all sales data (A1:E7).

No formula needed; just select cells A1 to E7.

Expected Result

The data range is highlighted and ready for chart creation.

2

Step 2: Insert a column chart to show sales by month and category.

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

Expected Result

A clustered column chart appears showing sales for each category side by side for each month.

3

Step 3: Add chart title 'Monthly Sales by Category' to the column chart.

Click on the chart title area and type 'Monthly Sales by Category'.

Expected Result

The chart title updates to 'Monthly Sales by Category'.

4

Step 4: Create a bar chart to compare total sales by category over six months.

Calculate total sales per category using SUM formula in cells below data: =SUM(B2:B7) for Electronics, =SUM(C2:C7) for Clothing, =SUM(D2:D7) for Home Goods, =SUM(E2:E7) for Toys.

Expected Result

Totals: Electronics=91000, Clothing=54600, Home Goods=45700, Toys=27100.

5

Step 5: Select the category headers (B1:E1) and totals (B8:E8) and insert a bar chart.

Hold Ctrl to select cells B1:E1 (category headers) and B8:E8 (totals), then Insert tab > Charts > Insert Column or Bar Chart > Choose 'Clustered Bar'.

Expected Result

A clustered bar chart appears showing total sales for each category.

6

Step 6: Add chart title 'Total Sales by Category (Jan-Jun)' to the bar chart.

Click the chart title and type 'Total Sales by Category (Jan-Jun)'.

Expected Result

The bar chart title updates accordingly.

Final Result

Monthly Sales by Category (Column Chart)

Month     | Electronics | Clothing | Home Goods | Toys
-------------------------------------------------------
January   | ████████    | █████   | ████       | ██  
February  | ██████████  | ██████  | █████      | ██  
March     | ████████    | █████   | ████       | ██  
April     | ██████████  | ██████  | █████      | ██  
May       | ███████████ | ██████  | ██████     | ██  
June      | ████████████| ██████  | ██████     | ██  

Total Sales by Category (Bar Chart)

Electronics | ██████████████████████ (91000)
Clothing    | ████████████          (54600)
Home Goods  | █████████             (45700)
Toys       | ████                   (27100)

Bonus Challenge

Create a stacked column chart to show the contribution of each category to total monthly sales.

Show Hint

Select the original data range A1:E7, then insert a 'Stacked Column' chart from the Insert tab.