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.
![Employee wise MTD Sales](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-1.jpg)
- 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.
![Create Pivot Table Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-2.jpg)
- Drag the MTD Sales field in ROWS and VALUES.
![Pivot Table](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-3.jpg)
- Right Click on any value of Row Labels and click on Group.
![Group Option in Pivot Table](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-4.jpg)
- Grouping window will be opened.
- Put 20 in “By” text box (Please take this number as per your data, here we are taking 20)
![Grouping window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-5.jpg)
- 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.
![Value Field Settings option](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-6.jpg)
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.
![Value Field Settings Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-7.jpg)
- 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.
![Remove the Grand Total](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-8.jpg)
- Now select the pivot table and go to Insert tab and insert a 2D Area Chart.
![Insert 2D Area Chart](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-9.jpg)
- Our Pivot Chart will look like below image.
![Pivot Chart](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-10.jpg)
- 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.
![Hide All option](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-11.jpg)
- 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
![Chart Elements](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-12.jpg)
- After doing above given settings, our chart will look like below image.
![Chart after settings](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-13.jpg)
- Now select the Area (Click on blue color)
- Go to Format tab>>Shap Styles>>Choose a shape style
![Shape Styles](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-14.jpg)
- Now select the chart and Design Tab>>Add Chart Elements>>Lines>>Drop Lines
![Add Drop Lines](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-15.jpg)
- Right click on the drop lines available on the chart and click on Format Drop Lines.
![Format Drop Lines Option](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-16.jpg)
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)
![Format Drop Lines Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-17.jpg)
- Take the Begin Arrow Type as Oval Arrow.
![Format Drop Lines Window](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-18.jpg)
- Add the data labels from the Chart Elements.
![Add the data labels](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-19.jpg)
- Right Click on the data labels and go to the Change Data Label Shapes and choose Rounded Rectangle shape.
![Change Data Label Shapes](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-20.jpg)
- Select the Data Labels and change the shape style from the Format tab.
![Shape style](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-21.jpg)
- Take the dark blue color for data label’s shapes.
![Change the color of data label's shape](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-22.jpg)
Our Frequency Analysis Chart is ready and It will look like below image.
![Frequency Analysis Chart](https://www.pk-anexcelexpert.com/wp-content/uploads/Blogs/ChartAndVisualization/FrequencyAnalysis/Frequency-Analysis-Chart-23.jpg)