Positive Negative Bar Chart

Here is a nice bar chart that clearly shows how multiple units does compared to each other based on same criteria. Since this chart can display positive and negative development very good, I will call it positive negative bar chart.

Our chart will show change of profit from 2012 to 2013 for 10 companies.

Here are the steps for building this chart:

This is our base data:

Positive Negative Bar Chart 1

We need to calculate difference in profit.

Positive Negative Bar Chart 2

Now add another column to the right that shows % difference (from 2012 figures). This is the data we are going to visualize.

Positive Negative Bar Chart 3

In order to create a good looking and responsive chart, we need to separate positive and negative values before building our chart. This way any change of values will automatically reflected to our chart.

Add two more columns to right that shows only positive and only negative values. Purpose of this will become more clear in next step.

Positive Negative Bar Chart 4

Now insert a clustered bar chart while a blank cell is selected and open Select Data interface. Select data series as shown below.

Positive Negative Bar Chart 5

Here is how your chart supposed to look like at this stage:

Positive Negative Bar Chart 6

Here are the adjustments to do on the chart:

  • Categories Options: Select Categories in Reverse Order.
  • Categories Options: Select label position as Low.
  • Delete horizontal axis and gridlines.
  • Add labels for % Difference series.
  • Set Series Overlap to 0%
  • Set Gap Width to 25%

Your chart is supposed to look like this after the adjustments above:

Positive Negative Bar Chart 7

Now another round of adjustments:

  • Change fill color for Positive Diff. series as green.
  • Change fill color for Negative Diff. series as red.
  • Add major horizontal gridlines and set color of gridlines to very light gray.
  • Add chart title, type a title text you like and decrease font size a little.

Here is the current chart (we are almost done):

Positive Negative Bar Chart 8

For profit change amount labels, we are going to use a trick.

  • Set fill color of cells D3 to D12 to white.
  • Set font color of this range to 8pt.
  • Now copy range paste as linked picture.
  • Arrange some empty space on the right side of your chart (stretch the chart to right and then plot area to left).
  • Drag this linked picture and place it on the empty space you just arranged.
  • Stretch the picture until values placed between related gridlines.

Here is the final chart:

Positive Negative Bar Chart 9

To download this chart follow the link below:

Download Positive Negative Bar Chart

Download FAQ

Please feel free to leave a comment for your questions and/or suggestions.

You may also like...

5 Responses

  1. Raj says:

    It is wornder Chart. But can not download. Kindly look into this.
    Warm Regards

    • melih says:

      I have just tried to download the file with 3 different web browsers (mozilla, chrome and internet explorer11). All worked flawlessly and I was able to download without any problems.
      Did you checked download FAQ ?
      If you did and are still unable to download, it is probably related to your internet settings or browser version.

  2. Raj says:

    Yes I think Melih it is probably my internet setting. Can you email me xcel file at Rajnikant.surve@gmail.com

  3. Clement says:

    Isn’t it Set Series Overlap to 100% instead of 0%?

  4. Nick says:

    Very useful but series overlap should be 100%

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.