Step by step to create a column chart with percentage change in Excel
This tutorial will show the method on creating a column chart with percentage change as below screenshot shown step by step in Excel.
Supposing your original data contains Year and Amount headers as below screenshot shown:
Now you need to use formulas to expand the source data as below screenshot shown:
1. In the column C, use Year Label as header, then in cell C2, type below formula then drag fill handle down to fill cells till blank cell appears.
A2 is the cell containing the year number.
2. In column D, use Amount 1 as header, then use below formula to cell D2, drag fill handle to fill cells till blank cell appears.
B2 is the cell containing the first amount value.
3. In column E which is used to display invisible column in the chart, Invisible is the header, use below formula to E2 then drag auto fill handle over the cells till zero appears, and finally remove the zero value.
B3 is the cell that contains the second amount value.
4. In column E, header is Variance, this column calculates the difference between last year and next year. In E2, use below formula and drag fill handle down till the penultimate cell of the data column.
B3 is the amount of the second year, B2 is the amount of the first year.
5. In column G, type Positive or the plus symbol as header, then in cell G2, use below formula then drag auto fill handle over cells till the last one of the data range.
F2 is the cell containing the variance of two years.
6. In column H, use Negative or minus symbol as the header, then in H2, type below formula and drag fill handle over cells till the penultimate cell of the data range.
F2 is the cell that contains the variance of two years.
7. In column I, which will display the percentage value of the variance between two years. Type below formula in I2, then drag fill handle over cells till the penultimate cell of the data range and format the cells as percentage format.
F2 is the cell that contains variance of the first year and the second year, B2 is the amount of first year.
1. Select the column C, D, and E (Year label, Amount 1 and Invisible) data range, click Insert > Insert Column or Bar Chart > Clustered Column.
2. Click the plus symbol beside the chart to display the CHART ELEMENT menu, then uncheck Gridlines and Legend, this step is optional, just for better viewing data.
3. Click at the column which displays the invisible data, then click the plus symbol to display the CHART ELEMENT menu, click arrow beside the Error Bars, and click More Options in the sub menu.
4. In the Format Error Bars pane, check Both and Cap option, the check Custom option at the bottom, and click Specify Value.
5. In the Custom Error Bars dialog, select G2:G7 as the range in Positive Error Value section, and H2:H7 as the range in Negative Error Value section. Click OK.
Now the chart is shown as below
6. Now right click at the column which expresses the value of Amount 1, then select Format Data Series from the context menu.
7. In the Format Data Series pane, change values in both Series Overlap and Gap Width sections to zero. Then the chart will display as below:
8. Now you need to change the fill color of the Invisible column to no fill. Right click at the columns that indicate Invisible values, then choose No fill from the drop-down list of the Fill in the context menu.
If you are in Excel versions before 2013, just click Format Data Series from context menu, and under the Fill & Line tab of the Format Data Series pane, choose No fill in Fill section.
9. Click at the Invisible column, then click plus symbol to display the CHART ELEMENT menu, click the arrow beside Data Labels to display the sub menu, and click More Options.
10. In the Format Data Labels pane, check Value From Cells checkbox in the Label Options section, then select the percentage cells (column I) to the Data Label Range dialog. Click OK.
11. Then uncheck Value and Show Leader Lines check boxes, in the Label Position section, select the position option as you need.
Then you can format the chart title or chart fill color as you need.
Now, the ultimate chart is shown as below：