Home>Blogs>Charts and Visualization>Frequency Analysis Chart in Excel

# Frequency Analysis Chart in Excel

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.

## Frequency Analysis Chart in Excel

• 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.
• Frequency Analysis Chart in Excel
• 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.
• 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.