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.

Practice

1. What is the main difference between a column chart and a bar chart in Excel?

easy

A. Column charts can only show numbers, bar charts can show text.

B. Column charts display vertical bars, bar charts display horizontal bars.

C. Column charts are used for dates, bar charts are used for categories.

D. Column charts require formulas, bar charts do not.

Solution

1. Step 1: Understand chart orientation

   Column charts show data with vertical bars, making it easy to compare values up and down.

2. Step 2: Compare with bar charts

   Bar charts show data with horizontal bars, useful when category names are long or many.

3. Final Answer:

   Column charts display vertical bars, bar charts display horizontal bars. -> Option B

4. Quick Check:

   Chart orientation = vertical vs horizontal [OK]

Hint: Remember: columns go up/down, bars go left/right [OK]

Common Mistakes:

• Confusing chart orientation
• Thinking bar charts show text only
• Believing formulas are needed for charts

2. Which of these is the correct way to insert a column chart in Excel?

easy

A. Select data, go to Insert tab, click Column Chart icon, choose style.

B. Type =COLUMNCHART() in a cell and press Enter.

C. Right-click a cell and select 'Create Chart' from the menu.

D. Use the Data tab and select 'Add Column Chart'.

Solution

1. Step 1: Locate Insert tab

   In Excel, charts are inserted from the Insert tab on the ribbon.

2. Step 2: Select data and choose chart

   After selecting your data, click the Column Chart icon and pick a style to create the chart.

3. Final Answer:

   Select data, go to Insert tab, click Column Chart icon, choose style. -> Option A

4. Quick Check:

   Insert tab + Column Chart icon = correct method [OK]

Hint: Use Insert tab, not formulas, to add charts [OK]

Common Mistakes:

• Trying to create charts with formulas
• Looking in wrong tabs like Data
• Right-click menu does not have 'Create Chart'

3. Given this data in cells A1:B4:
A1: Month, B1: Sales
A2: Jan, B2: 100
A3: Feb, B3: 150
A4: Mar, B4: 120
If you create a column chart from this data, what will the chart show?

medium

A. Vertical bars for Jan=100, Feb=150, Mar=120 sales values.

B. Horizontal bars for Jan=100, Feb=150, Mar=120 sales values.

C. A pie chart showing percentage of sales per month.

D. A line chart connecting sales values over months.

Solution

1. Step 1: Identify chart type and data

   Data has months and sales numbers. A column chart shows vertical bars representing values.

2. Step 2: Understand chart output

   Each month will have a vertical bar with height matching its sales number.

3. Final Answer:

   Vertical bars for Jan=100, Feb=150, Mar=120 sales values. -> Option A

4. Quick Check:

   Column chart = vertical bars with values [OK]

Hint: Column chart bars go up for each value [OK]

Common Mistakes:

• Confusing column with bar chart orientation
• Expecting pie or line chart from column chart
• Misreading data labels

4. You created a bar chart but the bars are vertical instead of horizontal. What is the likely mistake?

medium

A. You forgot to add data labels.

B. You selected the wrong data range.

C. You inserted a column chart instead of a bar chart.

D. You used a pie chart template by mistake.

Solution

1. Step 1: Understand chart types

   Bar charts show horizontal bars; column charts show vertical bars.

2. Step 2: Identify the cause of vertical bars

   If bars are vertical, you likely inserted a column chart by mistake.

3. Final Answer:

   You inserted a column chart instead of a bar chart. -> Option C

4. Quick Check:

   Vertical bars = column chart, not bar chart [OK]

Hint: Check chart type if bars are wrong direction [OK]

Common Mistakes:

• Confusing bar and column charts
• Blaming data range for bar orientation
• Thinking data labels affect bar direction

5. You have sales data for 12 months in columns A and B. You want to create a bar chart that shows sales by month horizontally and highlight months with sales above 100 in a different color. How can you achieve this in Excel?

hard

A. Create a bar chart, then use conditional formatting on the chart bars to color above 100 differently.

B. Create a pie chart and use slice colors to highlight sales above 100.

C. Create a column chart and manually change bar colors for months above 100.

D. Create a bar chart, then add a helper column with IF formula to separate above 100 sales, and plot both series with different colors.

Solution

1. Step 1: Prepare helper column with IF formula

   Create a new column with formula like =IF(B2>100,B2,NA()) to isolate sales above 100.

2. Step 2: Create bar chart with two data series

   Plot original sales and helper column as two series; Excel will show bars in different colors automatically.

3. Final Answer:

   Create a bar chart, then add a helper column with IF formula to separate above 100 sales, and plot both series with different colors. -> Option D

4. Quick Check:

   Helper column + two series = color highlight [OK]

Hint: Use helper column with IF to color bars differently [OK]

Common Mistakes:

• Trying conditional formatting directly on chart bars
• Using column chart instead of bar chart for horizontal bars
• Using pie chart which doesn't show bars