In this article you will learn how to create a Frequency Analysis chart in Excel. We will create this chart show the headcount % for different sales range. You will also learn how to create the number group and how to show the % Running Total in the pivot table. This chart is useful to understand that maximum employee are making how many sales (or any other metric).
For example:- In this chart 58% of employee are making only 200 or less than 200 sales.
We have below given employee wise MTD Sales data.
- First of all we will create the Pivot Table on this data.
- Select the data and go to Insert tab and insert the Pivot Table.
- In Create Pivot Table window select the Existing Worksheet and give the location of same sheet.
- Drag the MTD Sales field in ROWS and VALUES.
- Right Click on any value of Row Labels and click on Group.
- Grouping window will be opened.
- Put 20 in “By” text box (Please take this number as per your data, here we are taking 20)
- Now right click on any number of “Sum of MTD Sales” column in the pivot table and click on Value Field Settings.
- Or you can double click on header of “Sum of MTD Sales” column.
In the Value Field Settings window:
- Give the Custom Name as HC% (Head Count%)
- Select the Show Value As tab.
- Select “% Running Total In” in the Show Value As drop down.
- Change the Number format as percentage with 0 decimal place.
- Now remove the Grand Total from the pivot table.
- Select the pivot table and go to Design Tab>>Grand Totals>>Off for Rows and Columns.
- Now select the pivot table and go to Insert tab and insert a 2D Area Chart.
- Our Pivot Chart will look like below image.
- We can remove the Pivot chart element like HC% and MTD Sales Filter.
- Select the chart and go to Analyze Tab>>Field Buttons>>click on Hide All.
- Remove the Gridlines, Legend and Primary Vertical Axis from the chart.
- Click on ‘+‘ button (Chart Elements button) and uncheck the Gridlines, Legend and Primary Vertical Axis
- After doing above given settings, our chart will look like below image.
- Now select the Area (Click on blue color)
- Go to Format tab>>Shap Styles>>Choose a shape style
- Now select the chart and Design Tab>>Add Chart Elements>>Lines>>Drop Lines
- Right click on the drop lines available on the chart and click on Format Drop Lines.
In Format Drop Lines Window
- Go to the File and Line option
- Take the Solid color as Dark Blue
- Width as 1pt.
- Dash Type as Dash (Third one)
- Take the Begin Arrow Type as Oval Arrow.
- Add the data labels from the Chart Elements.
- Right Click on the data labels and go to the Change Data Label Shapes and choose Rounded Rectangle shape.
- Select the Data Labels and change the shape style from the Format tab.
- Take the dark blue color for data label’s shapes.
Our Frequency Analysis Chart is ready and It will look like below image.
