Excel Combo Charts
Learn how to combine column and line charts, use a secondary axis, and compare different types of data in one Excel chart.
1. What Is a Combo Chart?
A Combo Chart, also called a Combination Chart, displays two or more data series using different chart types in a single chart. It is useful when you want to compare values that have different scales or show different types of information together.
- Column + Line — compare monthly sales and profit percentage.
- Column + Line with Secondary Axis — compare data with different scales.
- Column + Area — compare totals and trends.
2. Where Is the Combo Chart Option in Excel?
- Select your data range, including column headings.
- Go to the Insert tab.
- Choose Recommended Charts or the chart options available in your Excel version.
- Open the Combo chart category, if available.
- Choose a chart type for each data series.
- Enable Secondary Axis for a series if its scale is different.
- Click OK.
Excel Insert Tab — Illustrative Diagram
3. Sample Data for a Combo Chart
Create a worksheet named MonthlySales and enter the following sample data in cells A1:D7.
| Month | Sales (₹) | Profit (₹) | Profit % |
|---|---|---|---|
| Jan | 50000 | 8000 | 16% |
| Feb | 62000 | 10000 | 16.13% |
| Mar | 48000 | 7000 | 14.58% |
| Apr | 75000 | 14000 | 18.67% |
| May | 68000 | 12000 | 17.65% |
| Jun | 82000 | 16000 | 19.51% |
If Profit % is not already available, calculate it using Profit divided by Sales.
=C2/B2
Enter the formula in D2, format the cell as Percentage, and copy it down through D7. This calculates the profit percentage for each month.
4. Understand the Combo Chart
Monthly Sales and Profit Percentage
Illustrative chart for understanding the layout. Build the actual chart from the sample data to obtain exact plotted values.
- Blue columns: Sales in rupees.
- Orange line: Profit percentage.
- Primary axis: Displays sales values.
- Secondary axis: Displays profit percentages.
5. Step-by-Step: Create a Combo Chart
Step 1: Select the Data
Select the Month, Sales, and Profit % columns, including their headings. If the columns are not adjacent, select the required series using the method supported by your Excel version.
Step 2: Insert a Combo Chart
- Open the Insert tab.
- Click Recommended Charts or the Combo Chart option.
- Choose the Combo category in the chart dialog.
- Set Sales to Clustered Column.
- Set Profit % to Line.
- Enable Secondary Axis for Profit %.
- Click OK.
Step 3: Check the Result
The chart should display sales as columns and profit percentage as a line. Verify that the left axis represents sales and the right axis represents the percentage series.
Step 4: Customize the Chart
- Set the chart title to Monthly Sales and Profit Percentage.
- Add axis titles for Sales (₹) and Profit (%).
- Use distinct colors for the two series.
- Display data labels if they improve readability.
- Resize and position the chart neatly in the worksheet.
6. What Is a Secondary Axis?
A secondary axis is an additional value axis used when two data series have different scales or units. For example, sales might be measured in tens of thousands of rupees, while profit percentage ranges from 0% to 25%.
| Data Series | Unit | Recommended Display |
|---|---|---|
| Sales | Rupees | Column chart on the primary axis. |
| Profit % | Percentage | Line chart on the secondary axis. |
7. Other Combo Chart Examples
| Combination | Use Case |
|---|---|
| Sales Column + Profit Line | Compare monthly sales and profitability. |
| Revenue Column + Growth % Line | Compare revenue with growth over time. |
| Actual Column + Target Line | Compare actual performance with a target. |
| Units Sold Column + Margin % Line | Compare product volume and margin. |
8. Common Problems and Solutions
| Problem | Possible Solution |
|---|---|
| Combo option is not visible. | Check the chart options available in your Excel version. |
| Line series appears too flat. | Consider using a secondary axis when the series uses a different scale. |
| Months appear in the wrong order. | Use real dates or arrange the month labels in chronological order. |
| Chart labels are confusing. | Add a chart title, axis titles, and a clear legend. |
| Profit percentage is incorrect. | Check that Profit is divided by Sales and the result is formatted as a percentage. |
9. Practical Exercises
Task 1: Sales and Profit
Create a Combo Chart showing Sales as columns and Profit % as a line.
Task 2: Add a Secondary Axis
Display Profit % on the secondary axis and label both axes clearly.
Task 3: Actual vs Target
Create sample monthly actual sales and sales targets, then display actual sales as columns and target sales as a line.
Task 4: Dashboard Chart
Move the finished chart to your Dashboard worksheet and format it to match your other dashboard visuals.
Important Points to Remember
- Combo Charts combine different chart types in one chart.
- Use columns for category comparisons and lines for trends.
- A secondary axis is useful when series have different units or scales.
- Calculate percentages correctly before creating the chart.
- Always label axes and use a meaningful chart title.
- Verify that the chart accurately represents the underlying data.
Summary
Excel Combo Charts combine two or more chart types to show related data together. A common example is displaying monthly sales as columns and profit percentage as a line.
A secondary axis helps display series with different scales. By selecting the right chart types, adding clear labels, and checking the underlying calculations, you can create effective charts for reports and dashboards.