0

I am trying to plot multiple series of (year, value) points in Excel, but I would like to display them as a column chart. I begin by plotting the data as two series in an x,y scatter plot which works perfectly. I then click "Change Chart Type" to a stacked column chart making sure the x-axis is a date axis. The blue series in this case successfully is plotted, but the orange series x values are being lost (notice plotting the 2023 yr point in 2020).

scatter

bar chart

How do I plot the two series as a column chart? (I was thinking maybe using #N/As to build out two series of the same length). The steps I outline above were able to work in another workbook of mine, but I cannot replicate it here.

2

First, some terminology: Bar charts run horizontally. Your screenshot shows a column chart.

If you want to use a column chart, why start with a scatter chart?

Select the data and insert a column chart. If your X axis labels are numbers, Excel will interpret this as another series. To avoid this, clear the cell in the data source (A3 in the screenshot), then select the data and insert a stacked column chart.

Once the chart is created, you can write the text back into cell A3.

enter image description here

Edit: without seeing your data layout, I'm doing some detective work here.

From your description it seems that your data is in two different ranges and the data is not sorted by the years. A Scatter chart has a numerical X axis and will slot the data in by value, so the orange values sit in the correct years.

enter image description here

A column chart has a categorical X axis, so the values will be plotted in the order that they appear in the data source. The labels will come from the first data series (if they data is in different ranges). So, when you convert the chart to a column chart, the orange values will be plotted in the first and second slot of the chart, which is not the year in column D of my screenshot.

enter image description here

The solution here is that you need to place the values for the orange data series in the correct position in the data source. In the screenshot, column D does not matter, but the placement of the values in column E is crucial.

Note how the X axis has only the year numbers of the data source. If you want to show all years in the chart, you need all years in the data source, too.

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.