Broken Bar Graph:-
Broken Bar are used for graphical presentation of the data which contain very wide variations in the values i.e the data which contain very large observations along with small observations. And in this case , simple bar graph is not much useful because it will not reveal the true characteristics of small values. In other to provide reasonable and adequate shape of smaller bar it is better to break the larger bar at the top to provide the true characteristics.
Unfortunately, in Excel there is no straightforward to insert the graph of broken bars. In order to represent it on Excel, personally I tried to use simple trick . Hopefully, it would be helpful.
Step : Select the data , from insert tab insert the scatter 'Scatter with only markers' graph.
The following data relates to the imports of foreign merchandise of Indian merchandise (in million rupees) for the year 1975-76.
And it would like as below:
Broken Bar are used for graphical presentation of the data which contain very wide variations in the values i.e the data which contain very large observations along with small observations. And in this case , simple bar graph is not much useful because it will not reveal the true characteristics of small values. In other to provide reasonable and adequate shape of smaller bar it is better to break the larger bar at the top to provide the true characteristics.
Unfortunately, in Excel there is no straightforward to insert the graph of broken bars. In order to represent it on Excel, personally I tried to use simple trick . Hopefully, it would be helpful.
Step : Select the data , from insert tab insert the scatter 'Scatter with only markers' graph.
The following data relates to the imports of foreign merchandise of Indian merchandise (in million rupees) for the year 1975-76.
And it would like as below:
Then, from insert tab click on bar "Clustered Bar' icon.
By clicking on 'Clustered Bar' icon, below is illustration it would come as. Clearly , USA Imports had larger bar, so we need to break it, for better presentation.
For broken bars, we made formula based another Import column.
In column C, we used formula,
=If(B2<9000 , B2 , B2-9000)
From Chart Tools, Design tab, click on Select Data icon.
In Select Data Source box, in chart data range - change the range from $A$1:$B$13 to $A$1:$C$13. And click OK. It would appear as below:
Click on horizontal axis, click mouse third button, click on format axis. Put Minimum value as 0 and Maximum Value as 6000 as for example.
Select Imports Bar on chart. And from Chart Tools select Format. And click on Shape Fill. Choose same color as on Imports 1 bar (here it is red color).
After that click on Insert tab, and from Shapes icon, select a Rectangle. And place it over the difference of Imports and Imports1 bar respective of particular variable. Here it is only case of variable USA. Placed over it.
Format the rectangle color as solid white to match the background color.
Select the Import1 bar, and from Chart Tools, select Layout and click on Format selection.
And in Format Data Series. Select Series Option. And in Series Overlap select Overlapped (as100%). And click close.
Format Gridlines and add data labels. Here , we get broken bar graph.
Thank you!
Format the rectangle color as solid white to match the background color.
Select the Import1 bar, and from Chart Tools, select Layout and click on Format selection.
And in Format Data Series. Select Series Option. And in Series Overlap select Overlapped (as100%). And click close.
Format Gridlines and add data labels. Here , we get broken bar graph.
Thank you!
No comments:
Post a Comment