I currently have a chart as follows:

enter image description here

As you can see, I have three 'value' columns per X-axis item represented by the colours blue, orange and grey.

I also have three 'count' lines represented by the colours yellow, light-blue and green.

What I need is for the three lines to be merged into a single line and for the markers to appear over the corresponding column.

For example, looking at label D13 along the X-axis, you'll see:

  • the blue column has 42.4%,
  • the orange column has 72.7% and,
  • the grey column has 15.2%.

Currently there are three lines overlapping these with all markers in the middle of the orange column. What I need is one line which has three markers over the D13 columns, one marker over the blue column, one over the orange column, and one over the grey column.

Is this possible?

My current data table looks as follows:

enter image description here

1 Answer 1


This is fairly easy to accomplish-it just needs a little math and creative formatting. The problem you're running into is that you're using a line chart for the lines (only in Excel would that be "the problem").

Line charts, like column charts, use categorical values for the horizontal axis. So, the line chart has the same 6 categories horizontally as the column chart and this means that Excel stacks the points over the middle of the column chart values. You can test this by adding tick marks to the horizontal axis and you'll see that the middle column and the line markers align with the middle of each category (since the axis default value is Axis Position: Between Tick Marks.

chart with ticks between marks

It's even more obvious if you change the value to Axis Position: On Tick Marks.

chart with ticks on marks

To accomplish what you're looking for you need to convert your line chart series to XY/Scatter series. This allows you to define the horizontal location for each point. Then, set each of the XY series to a secondary axis and set the Min to 0 and the Max to 6 (or the number of categories you have in your production data). Set the Major Units to 1 and Minor Units to the number of columns in each category plus 1 additional (to account for the spaces between grouped columns). Finally, add an X value for each data point {.25, .50, .75}, {1.25, 1.50, 1.75}... This can be in the series definition, another table, or columns in your existing table-whatever is easiest for you. The final product should look like:

xy series

And, a little formatting to make the lines and column colors match, but have enough contrast to be readable:

final product

EDIT: To address your commented questions:
When setting the Minimum/Maximum Bounds for your axis, the actual numbers are less important than the number of intervals they create. In this case, you're looking to create the same number of intervals as the categorical data in your column chart. So you have 6 categories (D11, D12, D13, D14, D15 elect, and D15 J). Each of these has 3 subcategories (low, med, high) and the equivalent of 1 open space between groupings for a total of 4. This means you'll need 24 intervals along the horizontal axis.
So, you can use either a Min of 0 and Max of 24, with Major Unit of 1 and Minor Unit of 1:

twenty four ticks

Or, you can use a Min of 0 and Max of 6, with Major Unit of 1 and a Minor Unit of 0.25 (1/4 of the Major Unit).

6 major and 4 minor

Either will give you the same effect in the final chart.

EDIT: To answer your original question

To get a single line on a chart, you need a single series. Also, as mentioned above, for the effect you want (consolidating 3 data series into one line), you'll need to match the horizontal intervals of your points to the categorical value of the columns. So, you need to do 2 things. First, identify the horizontal values, and put them someplace Excel can find them and second, merge/concantenate/union them into a single data series.

First, identify the horizontal values. Based on the original answer, I'm going to add the Major (1-6) and Minor (0.25-0.75) units to columns in the table, so Excel knows what they're called and where they're at. In the following image, I added 3 columns to match the 3 Count categories in the original data and populated them (skipping the whole numbers which are the gaps between columns).

extra columns

Second, we need to get them into a single, consecutive series that Excel can use for the charted line. There are at least 3 ways this can be accomplished. Generally, they are:

  1. Using the Select Data Source > Edit Series dialog box for a data series, highlight each cell range, separated by a comma. This isn't too bad for a small, static dataset.
  2. Manually define a matrix and then use the Excel Match/Offset combo to create automagically create a series. This works well once it's setup, but it doesn't expand automatically with additional data.
  3. Creatively use 2 pivot tables to generate your data series. For Excel to create a chart line, it needs a series of values. For an XY/Scatter line, it needs a series of matched pair values (an x and y). So, you can create two Pivot Tables from your data, one each for x and y values. The data will be in a single column and then selected as the appropriate series source for your chart. It'll look like this:

two pivots

They're two pivot tables, formatted in Compact Form with No Subtotals and the following fields:

pivot fields

Then just use the Count X pivot table for your series X values and repeat for Y. It may resort them in a different order (ascending or descending), just manually drag them back into proper order.

Once added and done, your final chart should look like this:

final chart

  • Thanks for this, it's a great improvement. Is there any way to get it to chart so it's just the one line? I appreciate this may mean playing around with the data table, but I'm open to that.
    – Monomeeth
    Sep 26 at 23:38
  • 1
    So, in the meantime I've been trying to follow your steps to recreate what you've described above, but I must be missing something because I'm not having any joy. I've converted the line chart series to an XY/Scatter series and set these to the secondary axis. I then added a 2nd vertical axis and set the min bounds to 0 and max bounds to 5 (no. of categories in my chart). So far so good (I think). Then I hit my first hurdle with setting major/minor units. If I set the major units to 1 and the minor units to 4, then Excel changes the major units to 4 as well (so, both to 4). No way to.....
    – Monomeeth
    Sep 27 at 5:31
  • 1
    ..... get it to be 1 for major and 4 for minor. Then the second hurdle I have is adding an X value for each data point. It's not clear to me how I actually do this. I did try setting it in the Horizontal axis crosses section within the Axis options, but Excel didn't like it. Sorry to be a pain, but could you clarify your steps further or add some extra screenshots?
    – Monomeeth
    Sep 27 at 5:31
  • Sorry, I didn't properly understand your question earlier. I'm working on an update for the answer.
    – dav
    Sep 27 at 13:58

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .