Creating Combination Charts with Multiple Data Series in Microsoft Excel

Course: Advanced Excel

Topic: Creating Combination Charts with Multiple Data Series

Course Link: Advanced Excel Course

Demo Link: Register for a Course Demo

Website: JustAcademy

1. Introduction to Combination Charts

A combination chart in Microsoft Excel combines two or more chart types in a single chart. It is useful when a dataset contains multiple data series that are easier to understand when displayed using different visual formats. For example, a business report may show monthly sales as columns and profit margin as a line.

Combination charts help users compare values, recognize trends, and analyze relationships between different business metrics. When the data series have significantly different scales or units, a secondary axis can be used to make the chart easier to read.

For example, a company may want to compare monthly revenue in rupees with the percentage of sales converted into profit. Revenue can be displayed using columns, while profit margin can be represented by a line.

2. Learning Objectives

• Understand the meaning and purpose of combination charts.
• Identify situations where multiple chart types are useful.
• Prepare data for a combination chart.
• Create a combination chart using Excel's Insert Chart tools.
• Assign different chart types to individual data series.
• Use a secondary axis when data scales differ.
• Format chart titles, axes, legends, labels, and series.
• Troubleshoot common charting problems.
• Apply combination charts to business reporting and dashboards.

3. What Is a Data Series?

A data series is a collection of related numerical values that Excel plots together on a chart. Each series usually represents a particular metric, category, or measurement.

Consider the following example:

In this dataset:

• Revenue: One data series containing monthly revenue values.
• Expenses: One data series containing monthly expense values.
• Profit Margin: One data series containing monthly percentage values.
• Month: The category labels used along the horizontal axis.

Revenue and expenses are measured in currency, while profit margin is measured in percentages. A combination chart can display revenue and expenses as columns and profit margin as a line.

4. What Is a Combination Chart?

A combination chart combines different chart types to display multiple data series within the same chart area. The most common combination is a column chart with a line chart.

Common Combination Chart Types

5. Why Use Combination Charts?

• Compare different metrics: Display sales, costs, targets, percentages, or growth rates together.
• Show trends clearly: Use a line to highlight changes across time.
• Improve reporting: Present related measures in one visual instead of multiple charts.
• Handle different scales: Use a secondary axis when one series is much smaller or uses a different unit.
• Support decision-making: Help managers identify patterns, performance gaps, and unusual changes.
• Build dashboards: Display several related indicators in a compact reporting area.

6. Preparing Data for a Combination Chart

Good chart preparation helps Excel interpret categories and data series correctly. Keep the data in a simple tabular structure with one header row and one row for each category.

Data Preparation Guidelines

1. Place category labels, such as months or product names, in the first column.
2. Place each numerical metric in a separate column.
3. Use descriptive column headings.
4. Ensure numeric values are stored as numbers rather than text.
5. Use consistent units and meaningful number formats.
6. Remove unnecessary blank rows and columns from the selected data range.
7. Check for missing values, duplicate categories, and incorrect entries.

Example structure:

Month    Revenue    Expenses    Profit Margin
January  100000     70000        30%
February 120000     82000        32%
March    140000     95000        32.1%
April    135000     90000        33.3%

For clarity, store percentage values as true percentages in Excel. For example, enter 30% or the decimal value 0.30 and format it as Percentage. If the source data contains 30 to mean 30%, confirm how the data is stored before applying percentage formatting.

7. Creating a Combination Chart in Excel

The following steps describe how to create a combination chart in modern desktop versions of Microsoft Excel. Menu labels can vary slightly by version.

Step 1: Select the Data

1. Open the Excel workbook containing your dataset.
2. Check that the headings and numerical values are arranged correctly.
3. Select the complete range, including the column headings and category labels.
4. For the example dataset, select the Month, Revenue, Expenses, and Profit Margin columns.

Step 2: Open the Insert Tab

1. Click the Insert tab on the Excel ribbon.
2. Locate the Charts group.
3. Choose Insert Combo Chart if it is available.
4. If the direct Combo Chart option is not shown, open the Recommended Charts dialog and look for the All Charts tab.

Step 3: Choose a Combination Chart

1. Select the Combo chart category.
2. Review the available chart-type choices for each data series.
3. Choose Clustered Column for Revenue.
4. Choose Clustered Column for Expenses.
5. Choose Line for Profit Margin.

Step 4: Enable a Secondary Axis if Needed

Because revenue is measured in currency and profit margin is measured in percentages, a secondary axis can help display both series clearly.

1. In the Combo Chart dialog, locate the Secondary Axis checkbox.
2. Enable it for the Profit Margin series when appropriate.
3. Check that the left axis represents revenue and expenses and the right axis represents profit margin.
4. Confirm that the line is visible and the percentage scale is understandable.

Step 5: Insert the Chart

1. Review the selected chart types and axis settings.
2. Click OK to insert the chart.
3. Move or resize the chart as required.
4. Verify that every intended data series appears correctly.

Step 6: Add a Meaningful Chart Title

Select the chart title and replace the default text with a descriptive title such as Monthly Revenue, Expenses, and Profit Margin.

Step 7: Add Axis Titles

Use the Chart Elements button or the Chart Design tools to add axis titles where supported. Label the primary axis with a currency description and the secondary axis with a percentage description. This prevents readers from confusing the different scales.

8. Using the Change Chart Type Dialog

If a chart already exists, you can change it into a combination chart without recreating the entire visual.

1. Select the existing chart.
2. Open the Chart Design tab.
3. Click Change Chart Type.
4. Select the Combo category.
5. Choose a chart type for each data series.
6. Enable the secondary axis for the appropriate series if necessary.
7. Click OK and review the result.

This method is useful when an existing column chart needs an additional line series or when the chart's visual design needs to change.

9. Understanding the Secondary Axis

A secondary axis is an additional value axis, usually displayed on the right side of the chart. It allows a data series to use a different scale from the series plotted against the primary axis.

For example, monthly revenue might range from ₹100,000 to ₹180,000, while profit margin ranges from 30% to 36.1%. Plotting both values on one axis can make the percentage line appear nearly flat or otherwise difficult to interpret.

Primary Axis vs Secondary Axis

When Should You Use a Secondary Axis?

• When series have substantially different numerical ranges.
• When series use different units, such as currency and percentage.
• When a line series is difficult to see because of the primary scale.

Important: A secondary axis can make a relationship appear stronger or weaker depending on its scale. Use it carefully, label both axes clearly, and avoid implying a relationship that the data does not support.

10. Formatting Multiple Data Series

Formatting makes a combination chart easier to interpret. Keep the visual style consistent and use formatting to distinguish metrics rather than decorate the chart excessively.

Formatting Techniques

• Series colors: Use distinct colors for revenue, expenses, and profit margin.
• Line thickness: Make the line visible without overpowering the columns.
• Markers: Add markers when readers need to identify individual data points.
• Data labels: Show values only when they improve understanding.
• Number formats: Display currency values as currency and percentage values as percentages.
• Legend: Use concise series names that clearly identify each metric.
• Gridlines: Keep only the gridlines that help readers estimate values.
• Chart background: Use a clean background and avoid unnecessary effects.

11. Formatting Axis Values Correctly

Correct axis formatting is essential when multiple data series use different units.

Format a Currency Axis

1. Right-click the primary value axis.
2. Select Format Axis.
3. Open the Number section.
4. Choose an appropriate currency or number format.
5. Set decimal places and display units according to the report's requirements.

Format a Percentage Axis

1. Right-click the secondary value axis.
2. Select Format Axis.
3. Choose Percentage in the Number section if the source values are stored as true percentages.
4. Adjust the number of decimal places.
5. Set suitable minimum and maximum bounds if needed, ensuring that the chosen range does not mislead the reader.

If the data contains values such as 30, 32, and 35 to represent percentages, applying percentage formatting directly will display 3000%, 3200%, and 3500%. Convert these values to 0.30, 0.32, and 0.35, or use a suitable custom format only when the data convention is understood.

12. Adding Data Labels to Selected Series

Data labels display exact values directly on a chart. Adding labels to every series can make a chart crowded, so use them selectively.

1. Click the chart.
2. Select the specific series, such as the profit margin line.
3. Right-click the selected series.
4. Choose Add Data Labels.
5. Open Format Data Labels to adjust position and number formatting.
6. Repeat for another series only if the extra labels are necessary.

For a chart with many months, consider labeling only important values or using labels sparingly. This keeps the visual readable.

13. Example: Sales and Target Achievement

A sales manager wants to compare actual monthly sales with the sales target and the target achievement percentage.

To create the chart:

1. Select the complete dataset.
2. Insert a Combo Chart.
3. Display Actual Sales and Target as clustered columns.
4. Display Achievement as a line.
5. Use a secondary axis for Achievement if needed.
6. Add a title such as Monthly Sales Performance Against Target.

Interpretation: The chart shows whether actual sales meet the target and how achievement changes from month to month. April is a month requiring attention because actual sales are below the target and achievement is lower than in the other displayed months.

14. Calculating Supporting Metrics with Excel Formulas

You can calculate metrics such as achievement percentage and profit margin before building the chart. Suppose actual sales are in column B and target values are in column C.

Target Achievement Percentage

=IFERROR(B2/C2,0)

Format the result as Percentage. The formula divides actual sales by target sales and returns zero if the calculation produces an error, such as division by zero. If zero would conceal a data issue, use a blank result or another explicit error-handling approach instead.

Profit Margin

If revenue is in column B and profit is in column D, use:

=IFERROR(D2/B2,0)

Format the result as Percentage. Confirm that profit and revenue are defined consistently in the underlying dataset.

Revenue Growth Percentage

If January revenue is in B2 and February revenue is in B3, use the following formula in the February growth cell:

=IFERROR((B3-B2)/B2,0)

This calculates the percentage change from the previous period. For the first period, there may be no previous value, so handle that row separately where appropriate.

15. Best Practices for Combination Charts

• Choose chart types based on what each metric represents.
• Use columns for comparisons across categories and lines for trends where appropriate.
• Keep the number of data series manageable.
• Use a secondary axis only when it improves understanding.
• Clearly label both value axes when two scales are displayed.
• Use a meaningful title and descriptive series names.
• Apply consistent number formats for currency, percentages, and quantities.
• Avoid excessive data labels, colors, shadows, and 3-D effects.
• Verify that category labels appear in the correct order.
• Check that the chart communicates the intended message without distorting the data.
• When presenting a chart to an audience, explain any secondary axis and the units being compared.

16. Common Problems and Troubleshooting

17. Using Excel Tables for Dynamic Charts

When data changes frequently, an Excel Table can make the chart easier to maintain.

1. Select the source data.
2. Press Ctrl + T or choose Insert > Table.
3. Confirm that the table has headers.
4. Create the combination chart from the table data.
5. Add new records to the table and verify that the chart range expands as expected.

Table-based chart ranges can update as records are added, although chart behavior depends on how the chart was created and which references it uses. Test the chart after adding rows to ensure all series update correctly.

18. Combination Charts in Business Dashboards

Combination charts are useful in dashboards because they allow related business measures to be viewed together. Common applications include:

• Sales dashboards: Actual sales columns with target achievement or growth as a line.
• Financial reports: Revenue and expenses as columns with profit margin as a line.
• Marketing reports: Advertising spend as columns with conversion rate as a line.
• Website analytics: Visits as columns with conversion percentage as a line.
• Operations reports: Production volume as columns with defect rate as a line.
• Inventory monitoring: Stock levels as columns with demand trends as a line.

Before using a combination chart in a dashboard, make sure the metrics are related in a meaningful way and the visual does not imply causation where the data only shows a comparison.

19. Practical Exercise

Task: Create a combination chart to compare quarterly revenue, expenses, and profit margin for a small business.

Sample Data

Exercise Instructions

1. Enter the sample data into Excel with the headings shown.
2. Select the entire dataset.
3. Insert a Combo Chart.
4. Set Revenue and Expenses to Clustered Column.
5. Set Profit Margin to Line.
6. Assign Profit Margin to a secondary axis if needed.
7. Add a descriptive chart title and axis titles.
8. Format the revenue and expense axes as currency and the margin axis as percentage.
9. Check the chart for readability and correct values.
10. Write two observations about quarterly performance.

20. Mini Project: Monthly Business Performance Dashboard

Create a one-page Excel dashboard for a fictional company using monthly sales data.

Project Requirements

• Prepare at least six months of data.
• Include actual revenue, target revenue, expenses, and profit margin.
• Create a combination chart with actual revenue and target as columns and profit margin as a line.
• Use a secondary axis where it improves readability.
• Add a clear title, legend, axis labels, and appropriate number formats.
• Use consistent colors and avoid unnecessary chart effects.
• Write a short summary describing the strongest month, the weakest month, and any noticeable trend.

Expected Learning Outcome

After completing the project, you should be able to prepare chart-ready data, select appropriate chart types for different metrics, configure a secondary axis, format multiple data series, and communicate business performance through a clear Excel visualization.

21. Interview Questions and Answers

Q1. What is a combination chart in Excel?

Answer: A combination chart displays two or more data series using different chart types within one chart, such as columns for sales and a line for profit margin.

Q2. Why would you use a combination chart?

Answer: It helps compare related metrics, display trends, and present multiple measures in one visual.

Q3. What is a secondary axis?

Answer: A secondary axis is an additional value axis that allows a data series to be displayed on a different scale, often on the right side of the chart.

Q4. When should you use a secondary axis?

Answer: Use it when the scales or units of two data series differ significantly and a single axis makes one series difficult to interpret. Label both axes clearly.

Q5. How do you create a combination chart?

Answer: Select the data, open Insert Combo Chart or Change Chart Type, choose a chart type for each series, configure a secondary axis if needed, and format the chart.

Q6. Can you change an existing chart into a combination chart?

Answer: Yes. Select the chart, go to Chart Design, choose Change Chart Type, select Combo, and assign the desired chart type to each series.

Q7. Why might a line appear flat in a combination chart?

Answer: The line's values may be small relative to the column values. Assigning the line to a suitable secondary axis may improve visibility.

Q8. How can you make a combination chart easier to understand?

Answer: Use a descriptive title, clear axis labels, distinct series formatting, correct number formats, a readable legend, and only necessary data labels.

Q9. What is one risk of using a secondary axis?

Answer: Different axis scales can exaggerate or minimize the apparent relationship between series. The axes should be clearly labeled and scaled responsibly.

Q10. How can you make a chart easier to update?

Answer: Store source data in an Excel Table, use descriptive headers, and verify that the chart includes newly added rows.

22. Quick Revision Checklist

• Understand the purpose of combination charts.
• Prepare clean data with one column per series.
• Select the complete dataset, including headings.
• Choose suitable chart types for each metric.
• Use a secondary axis only when appropriate.
• Format currency and percentage values correctly.
• Add meaningful titles, axis labels, and a legend.
• Keep colors and data labels simple and consistent.
• Verify that all data series and categories are correct.
• Check that the chart communicates the data accurately.

23. Conclusion

Creating combination charts with multiple data series is an important Advanced Excel skill for business reporting, financial analysis, marketing analytics, and dashboard design. By combining columns, lines, and other suitable chart types, you can compare related measures while highlighting trends that may otherwise be difficult to see.

The key is to select the right chart type for each series, use secondary axes carefully, apply correct number formats, and keep the visual clear. Practicing with sales, revenue, expense, target, and profit-margin datasets will help you create more effective Excel reports.

24. Additional Learning Resources

• Advanced Excel Course: Explore the Advanced Excel Course
• Course Demo: Register for a Course Demo
• JustAcademy Website: Visit JustAcademy