Making Pyramid Graph for Headcount Distribution Representation

Headcount Pyramid graphs are used to graphically represent the grade/designation wise distribution of the total headcount for entire organization or for specific department or for specific project. It is like a pie chart which shows the distribution of various elements of the total. However, for parameters like headcount, Pyramid graph is more effective as compared to a pie chart (lower level headcount is expected to be more than the upper level headcount, thus forming a pyramid). Refer to the screenshot below for a typical headcount pyramid graph…

Steps to make a Headcount Pyramid Graph

• Make a table as shown below for grade wise headcount.

• Add a column to the data table and apply formula in this column to insert the negative values of the headcount. . (in the given example, the formula in cell C3 will be “=-B3”). After adding this column, the data table should look like the table given below

• Select the table and go to Insert --> Bars (under charts section) --> Cluster Bar Chart. It should insert graph as shown in the screen below

• Select and delete gridlines of the graph
• Now select Vertical category axis i.e. the grade appearing on the graph. And Click on format selection from Chart Tools --> Layout. Change the Axis labels from “Next to Axis” to “Low”. After doing this, grades label will get aligned to the left of the graph window.

• Select any of the data series and click on format selection from Chart Tools --> Layout. Change the values of “Series overlap” to 100% and “Gap Width” to 0%

• Remove Horizontal value axis and Legend (select axis press delete. Similarly select Legend and press delete)
• Change the color of Bars, so that both the bars looks identical.
• Again select Vertical category axis i.e. the grade appearing on the graph. And Click on format selection from Chart Tools --> Layout.  Go to “Line Color” tab and select “No Line” to remove the line appearing at the center of the bars

• Select “Headcount” data series and add data labels by selecting Chart tools --> Data Labels --> Outside end
• Finally do some formatting changes as required. Like Font size, border color, shadow effects etc.

That’s it.  The Headcount Pyramid is ready and it should look similar to the screen below.