We also want to add another chart to show the change from previous year (Column P).
Since we have already set up this chart with the formatting and the sizing, a fast way to recreate a second chart is to duplicate the first.
That way we don’t need to do the formatting all over again and the alignment would be spot on.
To do this, select the first chart, press CTRL + C, click anywhere on the sheet, and press CTRL + V.
Change the series to the series that we want – the one displayed in the second chart.
Instead of using Q6, Q7:Q13, drag them to P6 and P7:P13, respectively.
You will now notice that the values displayed on the chart have changed.
The idea is to reposition this chart beside the first one, and in this way, we don’t need the same data labels for the second chart.
Clicking on the data labels and pressing DELETE would also delete the vertical axis line at the 0% mark.
If you want to retain this line, another way to remove the data labels is to go to the Format Axis panel > Axis Options > Labels and for Label Position, select None from the dropdown.
Add the data labels by clicking on the series, do a right mouse click and select Add Data Labels.
Since the data labels already show the percentage, we no longer need the horizontal axis labels on the top.
Click on it and press DELETE.
Update the chart title to describe what is being shown in this chart.
Click on the title and in the formula bar, do a cell reference to the column heading of the data. In this case, =P6.
Reposition and resize the width of the plot area and chart area as desired.
We also want the bars to have a different color when the value is negative.
For Excel 2010 onwards, there is an automatic feature that does this.
- Click on the data series
- Go to the Format Data Series panel
- Under the first tab, expand the Fill
- Tick the box for Invert if negative.
- By default, the negative bars will be shown as white. To change this, first specify a color for the positive value.
- You will notice that there is now a second dropdown option for the inverted fill color. Here you can specify the color for the negative value.
Instead of having the total sales volume at the bottom of the second chart, change the cell reference to the total % change by selecting the textbox and typing =P14 in the formula bar.
Drag the second chart over the first chart and align the bars.
Once aligned, hold down the CTRL key and click on the border of the second chart.
You will notice that the selection of the chart is now indicated by circles at the four corners.
Move the chart to the right by pressing on the Right Arrow key.
Another way to do this is to hold down the SHIFT key and use the mouse to drag the chart.
As a last step, group both charts together so that you can move them together.
To do this, hold down the CTRL key and select both charts.
Right-mouse-click and select Group.