0

The following Table is ready for comparison:
Table

When creating a standard bar chart value A and C are not easily comparable. In Excel you cannot display a value within a set on the secondary axis. So I created a column selecting every set individually and set B on a secondary axis the result is this:
graph

For the overlap I already found a solution: How to Create a Column Chart in Excel with Primary and Secondary Axis Without Bars that Stack over Each Other. But the values from Old and New are not next to each other and in the same color.

How can I create a bar chart that let me compare the values Old and New next to each other and also place one set of values on the secondary axis?

Improve this question

1 Answer 1

Reset to default
1

Well, since you're already putting B on the secondary axis, you're not really interested in comparing A to B to C. So let's change our approach.

How about normalizing the data? This means dividing all the values by the old values. These are the values in the table below. In all cases, old/old is 1 or 100%, and new/old is less than 100% because all values are decreasing.

A simple column chart, the first chart, shows new next to old. The percentages on the vertical axis may confuse people, so in the second chart I've formatted the axis labels to None, added data labels to the bars, and used the option (introduced in Excel 2013*) to use values from cells for the labels, with the original percent values as the labels.

*If you have an earlier version of Excel, you can use Rob Bovey's free Chart Labeler add-in to get the same effect. It's a free addin from Application Professionals.

Compare Normalized Values

Another way to approach this is to look at the percentage change of the values, which is (new-old)/old. These values are calculated in the table below. Again, a simple column chart serves well.

Compare Changes in Value

Improve this answer
2
  • Thanks for showing a different approach. I went for the first option. In Excel 2010 it was possible to manually edit the labels I placed using "Layout" - "Datalabels". The labels are then just textboxes which you can edit.
    – JeroenDV
    Commented Sep 29, 2016 at 11:25
  • Sure, you can edit the text of individual labels. If you have to do it a lot, or if the values change, it's nice to have a way to do it more quickly, that links to cells with values that may change. Both the Excel 2013 labels and the results of the Chart Labeler program satisfy this.
    – Jon Peltier
    Commented Sep 30, 2016 at 17:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .