How to Format Data for a Combo Chart in Excel

To format data for an Excel combo chart, put category labels in the first column, give each data series its own column with a clear header, and keep every value aligned with the right category. Select the complete range, then use Insert > Insert Combo Chart > Create Custom Combo Chart to assign a chart type to each series and a secondary axis when the scales differ. The guide below uses Microsoft Excel for Microsoft 365 on Windows; the same data layout works on Mac and, where combo-chart controls are available, Excel for the web.

What You’ll Need

A worksheet with one header row, one category column, and at least two numeric data series.
A clear reason to combine chart types, such as showing monthly sales as columns and a percentage as a line.
Excel for Microsoft 365 on Windows for the steps below. Mac ribbon labels can vary slightly; if Excel for the web does not show the combo-chart options you need, open the file in the desktop app.

You do not need a formula or a special template. You do need data that Excel can interpret consistently: dates should be dates, percentages should be numeric values formatted as percentages, and measurements should not be stored as text.

Step-by-Step

Choose categories and series before arranging the table. Decide what each chart point represents, such as a month, product, or region. Those categories go down the first column. Each measure you want to plot goes in a separate column. For example, to compare monthly sales and targets while showing conversion rate, use the headers Month, Sales, Target, and Conversion rate. Sales and Target can be columns, while Conversion rate can be a line. A combination is most useful when the series tell related parts of one story, not simply because several measures are available.
Build a simple rectangular data range. Put the headers in row 1 and the first category in row 2. Add one category per row, and place each measure in the matching row across its own column. For instance, A1:D1 might contain Month, Sales, Target, and Conversion rate; A2:D4 could contain January, February, and March with their corresponding values. Keep the table contiguous, with no completely blank rows or columns inside it. A single header row and one category column help Excel identify the series and axis labels correctly.
Check that each column contains the right kind of value. Enter sales and target amounts as numbers, not text with manually typed symbols. Apply currency formatting using Home > Number, or use the Number Format menu in the Number group. Enter a percentage as a numeric percentage value and format it with Home > Number > Percent Style. For example, a conversion rate of 4.2% should be stored as 0.042 and displayed as 4.2%, not entered as the text 4.2%. If categories are dates, enter real Excel dates and choose a display format such as month and year.
Make the headers and categories unambiguous. Use short, distinct series names such as Sales, Target, and Conversion rate; Excel may use these names in the legend. Avoid blank headers, duplicate names, and long explanatory notes in the header row. Give categories a consistent level of detail: do not mix individual dates with monthly totals in the same category column. If the first column contains dates, keep them in chronological order when the order matters. If it contains text labels, check for accidental leading or trailing spaces that could make labels look duplicated.
Select the complete table, including headers. Click the first header cell and drag to the last value, or click inside the data and press Ctrl+A on Windows to select the current data region. Check that the selection includes every category and series but does not include an unrelated note or total below the table. On Mac, use Command+A while the active cell is in the data region. If the selection is awkward or the worksheet contains adjacent data, drag to select the precise range instead. Including headers helps Excel name each series and label the category axis.
Insert a custom combo chart and assign each series. With the range selected, choose Insert > Insert Combo Chart > Create Custom Combo Chart. In the dialog, locate each series by its header and choose a chart type from its dropdown. A common arrangement is Clustered Column for Sales and Target and Line for Conversion rate. Keep related series that share a scale together as columns; use a line for a measure whose trend should be easy to follow. If the Combo command is not visible, use Insert > Recommended Charts, open the All Charts tab, and choose Combo.
Set a secondary axis only when the scale requires it. In the custom combo dialog, tick Secondary Axis beside a series whose values are on a substantially different scale, such as Conversion rate when Sales is measured in dollars. This gives that series its own vertical scale. Do not enable a secondary axis just to make a small series look more prominent: two scales can mislead if readers do not notice them. Label both axes clearly after inserting the chart. If all series use comparable units and ranges, leave them on the primary axis.
Review the chart and correct the data mapping if necessary. Select OK, then check that the horizontal axis shows the intended categories, the legend uses the column headers, and each series is drawn in the chosen style. If Excel has treated rows as series instead of columns, select the chart and choose Chart Design > Switch Row/Column. Use Chart Design > Select Data to inspect or adjust the chart data range and series. Add a descriptive chart title and axis titles with Chart Design > Add Chart Element. Resize the chart so labels and the legend are readable.
Test the layout with the actual values. Look for a line that appears flat, columns that dominate the chart, or labels that do not match the data. Confirm that the secondary-axis scale is appropriate and that the line has not been assigned to the wrong column. If new rows will be added regularly, consider converting the source range into an Excel table with Insert > Table before creating or updating the chart. A table can make a chart easier to maintain as the dataset grows, but still verify that the chart includes new records.

For the example layout, the chart should show one category for each month, two column series for Sales and Target, and a line for Conversion rate. The actual labels and values in the table drive the chart; colors and chart styling are separate choices you can adjust after the data is mapped correctly.

Common Mistakes

Combining unrelated measures. A combo chart should help readers compare related information. If the series have no meaningful connection, separate charts are usually clearer.
Putting several measures in one column. Excel expects one series per column or row. If sales and targets are interleaved in one column, split them into separate columns and align each value with its category.
Using text where Excel expects numbers. A value such as 25% typed with a leading apostrophe, or an amount containing manually entered text, may not plot as expected. Store the value numerically and apply a number format.
Adding totals or notes to the selected range. A total row can become an extra category and distort the chart. Keep summaries outside the plotted range unless they are deliberately part of the visualization.
Using a secondary axis without labeling it. A line on a second scale can appear to rise or fall dramatically even when the underlying change is modest. Make the axis title and units visible, and explain the scale when necessary.
Leaving blank or duplicate headers. Excel may assign generic series names or make the legend confusing. Give every plotted column a unique, descriptive heading.

Tips

Start with the simplest possible layout: categories in one column and one measure in each adjacent column. Avoid merged cells, multi-row headers, and decorative spacer columns in the chart source.
Use a line for a continuous trend, such as a rate over time, and columns for quantities that are compared category by category. This is a useful convention, not a strict rule; choose types that make the comparison easy to understand.
Format source values before charting. Currency, dates, and percentages are easier to verify in the worksheet, and Excel can usually carry those formats into axis labels.
Keep the number of series modest. Too many columns, lines, or legend entries make it hard to see which measure matters. Consider filtering or making separate charts if the audience must compare many categories.
Use clear axis titles that include units, such as Revenue ($) and Conversion rate (%). Do not rely on color alone to explain which series uses the secondary axis.
If the table will be reused, keep source data separate from notes and presentation summaries. A clean source range makes edits and chart maintenance less error-prone.

Troubleshooting

The chart shows the wrong categories or series: Select the chart and choose Chart Design > Select Data. Check the chart data range, category-axis labels, and each series name and values. If months appear in the legend while measures appear along the horizontal axis, choose Chart Design > Switch Row/Column. Also verify that the first row and first column were included in the selected range.

A series is missing or appears as zero: Check whether its cells contain numbers rather than text, whether the chart range includes that column, and whether the cells contain blanks or errors. Select a problem cell and inspect the formula bar. Re-enter values as numbers if needed, then apply the desired display format. If a series contains occasional blank values, decide whether a gap is the honest representation or whether the source needs completing; do not fill missing values with zero unless zero is accurate.

The line is almost flat or the columns are hard to compare: Compare the units and value ranges. If the line is a percentage and the columns are amounts, return to Chart Design > Change Chart Type, choose Combo, and enable Secondary Axis for the percentage series. Then make the scale and axis title clear. If both series use the same units, a secondary axis may be unnecessary; instead, check for unusually large values or an accidental text-to-number issue.

The combo option is unavailable: Confirm that the chart is selected and that the selected source includes at least two series. In the desktop app, open Insert > Recommended Charts > All Charts and look for Combo. Excel for Mac uses similar chart-type controls, though their position and wording may vary by release. If the current Excel for the web interface does not expose the required combo settings, choose Open in Desktop App and make the chart there.

New rows do not appear in the chart: Check the range shown under Chart Design > Select Data. Expand the range to include the new records, or use an Excel table as the source so additions are easier to incorporate. Also make sure the new rows follow the same column layout and contain values in the expected formats.

Frequently Asked Questions

What is the best data layout for an Excel combo chart?
Use one header row, one category column, and one column for each data series. Put each category on its own row and keep every measure aligned with that category. This gives Excel clear series names and axis labels.

Can I create a combo chart with only two data series?
Yes. Two series are enough to combine chart types, for example a column series for units and a line series for a related rate. The series do not have to use a secondary axis if their units and scales are comparable.

When should I use a secondary axis?
Use one when a series has a different unit or a value range that makes it unreadable beside the other series, such as a percentage alongside a large currency amount. Label the secondary axis clearly so readers understand that it uses a separate scale.

Should dates be stored as text or as Excel dates?
Use real Excel dates when the categories represent dates or time periods. Format the cells to display the dates as needed. Text labels can work for fixed categories, but real dates give Excel more options for arranging and displaying a time axis.

Can I change the chart type after creating the chart?
Yes. Select the chart and choose Chart Design > Change Chart Type, then choose Combo and assign a type to each series. This changes the visualization without requiring you to rebuild the source table.

Does the combo-chart method work on Mac and Excel for the web?
The same source-data layout applies. On Mac, look for the Combo chart option in the Insert or chart-type controls; wording and placement can differ by release. Excel for the web may not expose every desktop setting, so use the desktop app if you need a specific axis or series option.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.