How to fix formatting when adding another series to Excel chart?

Question

My original Excel bar chart looks like:

I want to add another series for 2014 data, as shown below:

I want the 2014 series to be scatter points overlaid on the original bar chart. However, when I add the 2014 series to the chart and change its chart type to Scatter, I obtain the following chart:

How can I change the bar chart back to its varying palette of colors as it is in the original chart, and change the 'Current' axis label to its original format which lists the different categories?

Ideally I'd want a time-efficient solution as I'll have to repeat this process with a number of similar charts.

Answer 1

I'm not sure if this is possible to do (quickly, elegantly, and/or without VBA) with multiple chart types. A few possible alternatives:

1. If you are only showing the difference from 2014, is a chart even necessary? Would a table be a better visualization?

2. Since it appears you are trying to plot the difference, why not plot this as a line graph?

3. Better yet, just plot the difference.

Answer 2

1. Start with a graph of one data series:

2. Add a second data series using Chart Tools|Design|Select Data:

3. Click the Add button and add data (series values) for a second series. Your chart should look like:

4. In the chart select the second data series. Then use the Chart Tools|Design|Change Chart Type tools to change the chart to a Line Chart.

From here on in it's formatting.

5. Select the second data series and do Chart Tools|Format|Format Selection to set the line color to no line. You can do any other formatting at this time.

6. Select the first data series and select each bar one at a time. Then use the Shape Fill button to customize each bar color:

Hope that helps!