Plot two date series as a column chart

Question

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).

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.

Answer 1

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.

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.

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.

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.