In Excel for Microsoft 365 on Windows, select your data and choose Insert > Recommended Charts > All Charts > Combo, then assign a column or line chart to each data series. A column-and-line chart combines vertical columns with a line, making it useful for comparing values such as monthly orders with a conversion rate or actual sales with a target.
What a Column-and-Line Chart Shows
A column-and-line chart is a combination chart: it displays at least one data series as columns and another as a line on the same chart. The categories, such as months, appear along the horizontal axis. Columns make the size of individual values easy to compare, while a line makes a changing pattern or target easier to follow across categories.
For example, imagine a table with Month, Orders, and Conversion rate. Columns can show the number of orders in each month, while a line shows the conversion rate. This lets a reader see both the volume of orders and how efficiently visits became orders. The chart does not imply that the two measures have the same units; if they use substantially different scales, the line may need a secondary vertical axis.
This chart is sometimes called a combo chart or a column-line chart. It is not the same as a chart with two lines, or a stacked column chart. In a stacked chart, values are piled within each category; in a combo chart, different series use different visual forms.
Arrange the Data Before Charting
Excel needs a clear category column and separate columns for each series. Put a descriptive heading at the top of each column, keep each observation on one row, and avoid blank rows or merged cells inside the data range. A simple layout might look like this:
- Column A: Month, with January, February, March, and so on.
- Column B: Orders, with a numeric count for each month.
- Column C: Conversion rate, with percentage values for the same months.
Make sure the measures line up by category. If March is missing from one series but not the other, check whether the blank represents a genuinely unavailable value or a zero. A zero says the value was measured and was none; a blank usually means there is no value to plot. Treating one as the other can change the story the chart communicates.
Use real Excel dates where dates are appropriate, rather than text that only looks like a date. Excel may otherwise treat date labels as unrelated categories or order them unexpectedly. If the categories are months, use consistent labels and chronological order. Select the headings and data together, but do not include unrelated totals unless you deliberately want those totals plotted as another category.
Create the Combo Chart in Excel
- Select the full data range, including the header row and category labels.
- Choose Insert > Recommended Charts, then open the All Charts tab in the dialog.
- Select Combo. Excel offers chart-type choices for each series; set the measure you want as columns to Clustered Column and the other measure to Line.
- If the series have different units or scales, select Secondary Axis for the line series. If they use comparable scales, leave that option off unless there is a clear reason to use it.
- Choose OK. Add a useful chart title and check that the category labels, legend, and axes make the result understandable.
The exact chart gallery appearance can change as Microsoft updates Excel, but the Combo category is the relevant choice. If Excel chooses an unsuitable combination automatically, use the combo settings to assign the chart type to each named series yourself. To change the result later, select the chart and use Chart Design > Change Chart Type, then select Combo and revise the series assignments.
On Excel for Mac, the chart tools and dialog layout can differ slightly by release. Look under Insert for Recommended Charts or the chart menu, then choose the Combo chart options if available. Excel for the web supports many chart tasks, but some chart-type and formatting controls may differ from the desktop app; if a needed combo option is not available in your interface, open the workbook in desktop Excel.
When to Use a Secondary Axis
A secondary axis gives one series its own vertical scale, usually displayed on the right side of the chart. It is useful when one measure is a count and another is a percentage, or when their magnitudes differ so much that a single scale would flatten one series. In the orders-and-conversion example, orders might range from 200 to 500 while conversion rates range from 2% to 6%. Plotting both against a single numeric scale would make the rate line difficult to interpret.
In the Combo dialog, tick Secondary Axis for the line series when you set its chart type. You can also select the series in an existing chart, open Format Data Series, and choose Secondary Axis. Axis formatting is important: format the right-hand axis as a percentage if the line represents a rate, and give both axes clear titles when the units may not be obvious.
A second axis can also mislead if its bounds are chosen to exaggerate or suppress a relationship. Two lines or series may appear to move together simply because the axes have been scaled to make them look similar. Start with sensible bounds, explain what each axis measures, and avoid presenting visual alignment as proof that one measure causes another. If readers could mistake the scales, add a note or choose separate charts instead.
Choose the Right Measures and Chart Types
Use columns for measures whose values are meaningfully compared across distinct categories, such as sales by month, calls by department, or units by product. A line is well suited to showing a continuous or ordered progression, especially a trend over time. Pairing those forms works when each series adds a different but relevant perspective on the same categories.
Good combinations include actual monthly revenue as columns with a sales target as a line, or website visits as columns with a conversion rate as a line. A target line is especially straightforward when its value is repeated for each category. For a cumulative total, a line may show the running progression while columns show the underlying periods, though the chart should label both measures clearly.
A combo chart is not automatically the best way to put every measure together. Avoid pairing unrelated measures just because they share a date column. If there are many series, too many categories, or several axes, readers can lose track of what the chart means. Two small charts with aligned time categories may be clearer than one crowded chart. For a simple comparison of two measures with the same units, a regular clustered column chart may be easier to read.
Format the Chart for Clear Reading
After creating the chart, select it to reveal Chart Design and Format. Use Chart Design > Add Chart Element to add or adjust items such as a chart title, axis titles, legend, and data labels. A title should say what is being compared and, where useful, the period covered. Axis titles can identify units such as Orders and Conversion rate, so a reader does not have to infer them from context.
Keep the colors distinct and consistent. A restrained color for columns and a contrasting line color help readers distinguish the series; markers on the line can make individual monthly points easier to locate. Do not rely on color alone when the chart will be printed in grayscale or used by readers with color-vision differences. Use clear labels and sufficient contrast, and remove decorative effects that compete with the data.
Data labels can help when there are only a few categories, but labels on every column and line point can clutter a large chart. Consider labeling just the line or using a legend and readable axes. If a percentage series has a secondary axis, format that axis as Percentage using the axis formatting pane and check the displayed decimal places. Keep the scale appropriate: a rate axis that starts far above zero can make small changes look dramatic, so consider whether the visual emphasis is justified.
Common Problems and How to Fix Them
- The line is nearly flat. The two measures may be using a shared scale despite having different units. Edit the combo settings and put the line on a secondary axis if that is analytically appropriate.
- The wrong values appear as categories. Check that the selected range includes the intended category column and headers. Use Chart Design > Select Data to review the chart data range, series names, and horizontal axis labels.
- Months appear in the wrong order. Sort the source data chronologically and make sure dates are actual date values rather than text labels that sort alphabetically.
- A line or column is missing. Check that the series contains numeric values, that its cells are included in the chart range, and that the series has not been hidden or assigned an unexpected chart type.
- Gaps are connected in an unexpected way. For a chart with missing cells, select the chart and open Chart Design > Select Data > Hidden and Empty Cells. Review how empty cells are displayed; a gap, zero, or connected line gives a different visual impression.
When a chart seems wrong, inspect the source values before changing its appearance. A chart can faithfully display data that were entered in the wrong units or as text. Confirm that rates are stored as percentages rather than whole-number counts, and that one series has not accidentally included a total row.
Keep the Chart Updated and Share It Accessibly
If you expect to add rows over time, convert the source range to an Excel table with Insert > Table before building the chart. Charts based on a table generally expand as new table rows are added, which reduces the chance that later months are omitted. Verify the chart after adding data, particularly if you have changed the source range or used a custom axis scale.
For sharing, give the chart a descriptive title and make the legend labels meaningful. In Excel, select the chart and use the chart formatting controls to edit its title and labels; add alternative text through the chart’s context menu or the accessibility options available in your version. A chart image pasted into a document may not retain the same ability to inspect underlying data, so consider sharing the workbook or a data table as well when readers need exact values.
Frequently Asked Questions
How do I make a column and line chart in Excel?
Select the category labels and data series, then choose Insert > Recommended Charts > All Charts > Combo. Set one series to Clustered Column and another to Line, and choose a secondary axis if the units or scales require it.
Can I put a line on top of a column chart in Excel?
Yes. Use a combo chart and assign the relevant series to Line while leaving the other series as a column type. This is more reliable than drawing a line over a separate chart because both series remain connected to the same chart data.
Should the line use a secondary axis?
Use one when the line series has different units or a scale that would make it unreadable beside the columns, such as a percentage paired with a count. Keep a single axis when the measures are comparable, and label any secondary axis clearly.
How do I change a column series to a line?
Select the chart, choose Chart Design > Change Chart Type, and select Combo. Change the chart type for that series to Line, then confirm the dialog. The wording or layout may vary slightly across Excel releases.
Why is my line chart not showing correctly?
Check the selected data range, numeric values, category labels, and axis assignment. Text-formatted numbers, a missing data column, an unsuitable shared scale, or incorrectly ordered dates can all make a combo chart look wrong.
Related
- Change Block Capitals to Lowercase in Excel
- Center Across Selection in Excel: Add It to Quick Access
- Cell in Microsoft Excel: Meaning, Types, and How to Use It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Column Chart in Excel: How to Create and Format OneA column chart in Excel displays values as vertical bars, making it easy to compare categories or track…
- How to Add a Trendline in an Excel GraphTo put a trendline in an Excel graph, select the chart, choose Chart Design > Add Chart Element…
- Excel Waterfall Chart End Value: Set the Final TotalTo make the last bar in an Excel waterfall chart show the ending balance, right-click that bar and…
- Bubble Chart in Excel: Make Each Bubble a Different SizeYes. To make bubbles different sizes in an Excel bubble chart, give each data point its own size…
- Pivot Tables in Microsoft Excel: How to Create and UseA PivotTable in Microsoft Excel summarizes and reorganizes a larger data set so you can compare totals, counts,…
- Excel Bar Graph Not Showing All Data? Fix ItIf an Excel bar graph is missing bars or categories, first check its source range in Chart Design…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.