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:
We need to calculate difference in profit.
Now add another column to the right that shows % difference (from 2012 figures). This is the data we are going to visualize.
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.
Now insert a clustered bar chart while a blank cell is selected and open Select Data interface. Select data series as shown below.
Here is how your chart supposed to look like at this stage:
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:
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):
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:
To download this chart follow the link below:
Download Positive Negative Bar Chart
Please feel free to leave a comment for your questions and/or suggestions.
It is wornder Chart. But can not download. Kindly look into this.
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.
Yes I think Melih it is probably my internet setting. Can you email me xcel file at Rajnikant.firstname.lastname@example.org
Isn’t it Set Series Overlap to 100% instead of 0%?
Very useful but series overlap should be 100%