Combo charts in Excel - Real Business Scenario

Scenario Mode

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

📋 Request: Your manager wants a clear visual report showing monthly sales revenue and the number of units sold side by side to understand sales performance better.

📊 Data: You have monthly sales data for the first half of the year. The data includes the month, total sales revenue in dollars, and the number of units sold each month.

🎯 Deliverable: Create a combo chart that shows sales revenue as columns and units sold as a line on the same chart for easy comparison.

Sample Data

Month	Sales Revenue ($)	Units Sold
January	12000	300
February	15000	350
March	13000	320
April	17000	400
May	16000	380
June	18000	420

1

Step 1: Select the data range including the headers and all rows (A1:C7).

Select cells A1 to C7.

Expected Result

The data range with months, sales revenue, and units sold is selected.

2

Step 2: Insert a combo chart by going to the Insert tab, clicking on 'Insert Combo Chart', and choosing 'Custom Combination Chart'.

Insert > Combo Chart > Custom Combination Chart.

Expected Result

A dialog box opens to customize the combo chart.

3

Step 3: Set 'Sales Revenue ($)' to be displayed as a Clustered Column chart type.

In the combo chart dialog, set 'Sales Revenue ($)' chart type to 'Clustered Column'.

Expected Result

Sales revenue data will appear as columns in the chart.

4

Step 4: Set 'Units Sold' to be displayed as a Line chart type and check the box to plot it on the Secondary Axis.

Set 'Units Sold' chart type to 'Line' and check 'Secondary Axis'.

Expected Result

Units sold data will appear as a line on the secondary vertical axis.

5

Step 5: Click OK to insert the combo chart into the worksheet.

Click OK in the combo chart dialog.

Expected Result

The combo chart appears showing sales revenue as columns and units sold as a line with two vertical axes.

6

Step 6: Add chart title 'Monthly Sales Revenue and Units Sold' by clicking on the chart title area and typing the new title.

Click chart title > Type 'Monthly Sales Revenue and Units Sold'.

Expected Result

The chart has a clear title describing the data.

7

Step 7: Format the secondary vertical axis to have a suitable scale if needed by right-clicking the axis and choosing 'Format Axis'.

Right-click secondary vertical axis > Format Axis > Adjust minimum and maximum values.

Expected Result

The units sold line fits well within the chart area for easy reading.

Final Result

Monthly Sales Revenue and Units Sold

Month     | Jan | Feb | Mar | Apr | May | Jun |
-------------------------------------------------
Sales $   | ||| | ||||| | |||| | ||||||| | |||||| | |||||||
Units Sold|  --  --  --  --  --  --  (line over months)

Legend:
||| = Sales revenue columns
-- = Units sold line on secondary axis

✓Sales revenue generally increases from January to June.

✓Units sold follow a similar upward trend, confirming sales growth.

✓The combo chart clearly shows the relationship between revenue and units sold.

Bonus Challenge

Add data labels to both the columns and the line points to show exact values on the chart.

Show Hint

Click on the chart elements, select data labels, and choose to display them for both series.

Practice

1. What is the main purpose of a Combo Chart in Excel?

easy

A. To display data in a pie chart format

B. To combine two or more different chart types in one chart

C. To create a chart with only one data series

D. To sort data automatically in a table

Solution

1. Step 1: Understand what a combo chart is

   A combo chart combines different chart types like column and line in one chart to show related data.

2. Step 2: Identify the correct purpose

   Since combo charts mix chart types to compare data sets, To combine two or more different chart types in one chart matches this purpose.

3. Final Answer:

   To combine two or more different chart types in one chart -> Option B

4. Quick Check:

   Combo chart = mix chart types [OK]

Hint: Combo charts mix chart types to compare data sets [OK]

Common Mistakes:

• Thinking combo charts only show one data series
• Confusing combo charts with pie charts
• Assuming combo charts sort data automatically

2. Which Excel menu path correctly leads you to create a combo chart?

easy

A. Data > Sort & Filter

B. Formulas > Insert Function

C. Insert > Combo Chart

D. Review > Protect Sheet

Solution

1. Step 1: Recall where combo charts are created

   Combo charts are created from the Insert tab in Excel, under the Combo Chart option.

2. Step 2: Match the correct menu path

   Insert > Combo Chart shows the correct path: Insert > Combo Chart.

3. Final Answer:

   Insert > Combo Chart -> Option C

4. Quick Check:

   Insert tab > Combo Chart [OK]

Hint: Combo charts are under Insert tab in Excel [OK]

Common Mistakes:

• Looking under Data or Formulas tabs
• Confusing chart creation with sorting or filtering
• Trying to find combo charts in Review tab

3. Given sales data for two products over 3 months, which combo chart type best shows Product A as columns and Product B as a line?

medium

A. Column for Product A and Line for Product B

B. Pie chart for both products

C. Line chart for Product A and Pie chart for Product B

D. Scatter plot for both products

Solution

1. Step 1: Understand combo chart types

   Combo charts combine different chart types, like columns and lines, to compare data sets clearly.

2. Step 2: Match chart types to products

   Product A as columns and Product B as a line fits Column for Product A and Line for Product B. Pie and scatter charts do not combine well here.

3. Final Answer:

   Column for Product A and Line for Product B -> Option A

4. Quick Check:

   Combo chart = column + line [OK]

Hint: Use column + line combo to compare two products [OK]

Common Mistakes:

• Choosing pie charts which can't combine well
• Using scatter plots which don't show trends clearly
• Mixing line with pie charts incorrectly

4. You created a combo chart but the line series is not showing. What is the most likely error?

medium

A. The line series is not assigned to the secondary axis

B. The data range is empty

C. The chart type is set to pie for all series

D. The worksheet is protected

Solution

1. Step 1: Check combo chart setup

   In combo charts, line series often need to be on the secondary axis to display properly.

2. Step 2: Identify the missing line issue

   If the line series is not assigned to the secondary axis, it may not appear correctly, causing it to be invisible.

3. Final Answer:

   The line series is not assigned to the secondary axis -> Option A

4. Quick Check:

   Line series needs secondary axis [OK]

Hint: Assign line series to secondary axis to show it [OK]

Common Mistakes:

• Assuming empty data range is the cause without checking axis
• Setting all series to pie chart type
• Thinking worksheet protection hides chart series

5. You want to create a combo chart showing monthly revenue as columns and profit margin as a line on a secondary axis. Which steps should you follow?

hard

A. Select data > Data tab > Sort by revenue and profit margin

B. Select data > Insert > Pie Chart > Add profit margin as line

C. Select data > Insert > Line Chart > Change revenue to pie chart

D. Select data > Insert > Combo Chart > Set revenue as column, profit margin as line on secondary axis

Solution

1. Step 1: Choose correct chart insertion method

   Use Insert > Combo Chart to combine column and line charts for different data series.

2. Step 2: Assign chart types and axes

   Set revenue as columns and profit margin as line, assigning profit margin to the secondary axis for clarity.

3. Final Answer:

   Select data > Insert > Combo Chart > Set revenue as column, profit margin as line on secondary axis -> Option D

4. Quick Check:

   Combo chart with secondary axis for line [OK]

Hint: Use combo chart and assign line to secondary axis [OK]

Common Mistakes:

• Using pie charts which can't combine with lines
• Trying to sort data instead of creating chart
• Mixing chart types incorrectly