Create a Combined Chart (Bars + Lines) in Excel — Step-by-Step Guide

The combined chart — mixing bars and lines — transforms flat tables into clear visual messages: volumes represented by bars, trends by a line. This format is particularly useful when you want to compare measurements of different natures (for example, revenue and conversion rate) without sacrificing readability. Don’t worry if you’re a beginner: I detail here every step, from data preparation to fine-tuning the secondary axis and formatting, with practical advice to avoid common mistakes.

In brief

Why choose a combined chart?

You might think that overlaying two series on the same chart will always suffice, but in practice the units and magnitudes often differ: a sales volume in thousands and a rate in percentage cannot share the same axis without losing all meaning. A combined chart solves this problem by associating bars with quantity representation and the line with relative evolution. This format facilitates the detection of gaps: you immediately see if a volume increase is accompanied by a rate increase, or if the trend diverges instead.

Prepare the data

Ideal table structure

To avoid manipulations when inserting the chart, organize your data in clear columns: first column for the time dimension (month, quarter), next column for the series intended for bars (e.g. sales), and next column for the series intended for the line (e.g. rate). Avoid merged cells and keep explicit headers — Excel uses them to automatically label the chart.

Check the integrity of the data

Before creating the chart, review your values: empty cells, text formats for numbers, misplaced decimals, or incorrect separators can distort the output. If your data comes from an export, take the time to clean the file — for example, importing a CSV file is not uncommon, and correcting errors at this stage avoids complicated recalculations later.

Create the combined chart: step-by-step method

1. Select the data

Select the entire table (including headers). If you have non-contiguous ranges, hold Ctrl to add the desired ranges. Correct selection ensures that legends and the x-axis are generated automatically.

2. Insert a bar chart

In the Insert tab, choose a column or bar chart (clustered column is often the right starting point). You get a chart where all your series appear as columns — this is simply a starting point, we will then change one series into a line.

3. Convert a series into a line

Right-click on the series that should become a line, then choose “Change Series Chart Type.” In the window, select “Line” type for this series and check the “Secondary Axis” box if the unit differs from that of the bars. Confirm: Excel now overlays the bars and the line, but the secondary axis still needs to be adjusted for optimal reading.

Adjust the secondary axis and scale

The secondary axis is the key to an understandable combined chart. A poor scale can crush the line or make the bars insignificant. Click on the secondary axis, then select “Format Axis.” There, manually set the bounds (minimum/maximum) and the major unit so that the line occupies a visual range comparable to the bars. Sometimes a simple transformation (e.g., displaying a rate in points rather than as a fraction) clarifies the relationship between the series.

Practical tips for the scale

• Start by letting Excel choose the bounds, then adjust if the line seems flat.
• For a percentage series, set the secondary axis between 0% and 100% if it makes sense; otherwise, adapt according to your data.
• Avoid axes that start far from zero if absolute comparisons matter.

Improve readability: formatting and best practices

A good chart combines precision and visual simplicity. Choose a palette where the bars and the line stand out immediately — for example, dark gray bars and a colored line with a thick stroke. Add markers on the line to highlight key points, but don’t overdo it: too much decoration distracts from the data.

Elements to check

• Legend: place it consistently (bottom or right) and verify that it accurately describes each series.
• Data labels: display only if they add value (e.g., for peaks, totals).
• Grid: a discreet horizontal grid helps read values without cluttering the image.
• Colors: choose accessible colors (high contrast, avoid red/green exclusively for colorblind users).

Advanced cases: multiple curves or series on different axes

Sometimes you need to display two curves and two sets of bars simultaneously. Excel allows you to define for each series the axis (primary or secondary) and the type (bar or line). Before multiplying the axes, ask yourself: will this chart remain readable? Too many axes hinder understanding. As a general rule, limit yourself to two axes and two types of representation.

Summary table of choices according to data type

Data type	Recommended representation	Remark
Volumes (sales, units)	Bars	Good for comparing relative sizes
Rates, percentages	Curve on secondary axis	Highlights the trend
Indices / scores	Curve or bars depending on the objective	Choose according to desired reading

Troubleshooting: common problems and solutions

The curve is reduced to a flat line

Often the secondary axis is poorly calibrated. Check the bounds and adjust them manually. If the scale difference is extreme, consider a transformation (logarithm) or normalize the series for a relative comparison.

Legends or series names are incorrect

This generally comes from missing headers or an incomplete selection before inserting the chart. Modify the data selection (button “Select Data”) to correct the names and the order of the series.

Automate and prepare data with formulas

Before visualizing, consolidate your data with formulas: for example, use SUMIF to aggregate sales by category, or COUNTIF to count occurrences according to criteria. These formulas facilitate the creation of series ready for display, without manual edits. If you work on series extracted from multiple sources, prior cleaning helps avoid outliers in the chart — hence the usefulness of tools and functions to prepare your data properly.

Export, printing and integration

A well-prepared chart exports easily to PowerPoint or prints for a report. Before exporting, check the resolution and font format: some fonts readable on screen become illegible on paper. For web integration, export in PNG or SVG as needed; SVG preserves sharpness and allows later adjustments via CSS.

Examples of common use

• Sales dashboard: sales in bars, conversion rates in curve.
• Financial report: revenue by segment vs gross margin.
• Marketing analysis: impression volume vs click-through rate (CTR).

FAQ

How to add the secondary axis if the option does not appear?

If the “Secondary Axis” option is grayed out, first select the series by right-clicking in the chart, then choose “Format Data Series.” If the series is a complex combined chart, check that the series is not grouped or defined as an embedded chart.

Can the curve be animated for a presentation?

Excel offers limited animations; for sophisticated staging, export the chart to PowerPoint and apply animations on elements (series entry, point appearance). Ensure the animation serves interpretation and not just aesthetics.

Should the secondary axis always be used for percentages?

No. If the percentage values are of a similar order of magnitude to the absolute values, the primary axis may suffice. The secondary axis is useful when the gap between units makes reading impossible without scale separation.

Additional Resources

• Use aggregation functions like SUMIF to prepare your series before visualization.
• For conditional counts useful for binary series, the COUNTIF formulas can automate the creation of a dedicated column.

{
“@context”: “https://schema.org”,
“@type”: “WebPage”,
“about”: {
“@type”: “Thing”,
“name”: “combined bar and line chart in Excel”
},
“keywords”: [“combined chart”, “line”, “bars”, “secondary axis”, “Excel”]
}