Percentage Bar Diagram is same as sub-divided or component bar diagram (Previous Blog) except that it represent the data in percentage. It is useful to show relative importance of the various component parts to the whole. That is, the total for each part is taken as 100 and the value of each component is expressed in percentage of the respective total.
To prepare it on Excel, First create a column to expressed the data in percentage. As in example we created a column of Expenditure percentage, with formula -
=(B2/$B$8)
And, as data is presented on graph , it would be easy to check with cumulative percentage. So, we also created cumulative percentage column with formula -
=SUM($C$2:C2)
See Below diagram:-
Now, Select column A and C as shown in below figure.
Select Insert tab and click on '100% Stacked Column'. As 100% Stacked Column is for to compare the percentage that each value contributes to a total across categories by using vertical rectangles.
By, clicking on '100% Stacked Column', our graph looks like as in below diagram. After that from Chart Tools, Select Design tab and click on 'Switch Row/Column'as we need the Expenditure percentage on vertical axis.
As , by clicking on Switch Rows/Column, we get our graph as below which we need.
After that, need some formatting part of graph. We Add data Labels by clicking mouse third button and select "Add Data Labels".
Again, click mouse third button for Format Data Labels .
In Format Data Labels box , select Label Options and select Series Name.
Here, we get our Percentage Bar Diagram, you can do more formatting as per your need.
Thank you!
No comments:
Post a Comment