Frequency Analysis Chart
Charts and Visualization

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.

Employee wise MTD Sales
Employee wise MTD Sales
  • 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
Create Pivot Table Window
  • Drag the MTD Sales field in ROWS and VALUES.
Pivot Table
Pivot Table
  • Right Click on any value of Row Labels and click on Group.
Group Option in Pivot Table
Group Option in Pivot Table

 

  • 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
Grouping window
  • 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
Value Field Settings option

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
Value Field Settings Window
  • 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
Remove the Grand Total
  • Now select the pivot table and go to Insert tab and insert a 2D Area Chart.
Insert 2D Area Chart
Insert 2D Area Chart
Pivot Chart
Pivot 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.
Hide All option
Hide All option
  • 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
Chart Elements
  • After doing above given settings, our chart will look like below image.
Chart after settings
Chart after settings
  • Now select the Area (Click on blue color)
  • Go to Format tab>>Shap Styles>>Choose a shape style
Shape Styles
Shape Styles
  • Now select the chart and Design Tab>>Add Chart Elements>>Lines>>Drop Lines
Add Drop Lines
Add Drop Lines
  • Right click on the drop lines available on the chart and click on Format Drop Lines.
Format Drop Lines Option
Format Drop Lines Option

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
Format Drop Lines Window
  • Take the Begin Arrow Type as Oval Arrow.
Format Drop Lines Window
Format Drop Lines Window
  • Add the data labels from the Chart Elements.

 

Add the data labels
Add the data labels
  • Right Click on the data labels and go to the Change Data Label Shapes and choose Rounded Rectangle shape.
Change Data Label Shapes
Change Data Label Shapes
  • Select the Data Labels and change the shape style from the Format tab.
Shape style
Shape style
  • Take the dark blue color for data label’s shapes.
Change the color of data label's shape
Change the color of data label’s shape

Our Frequency Analysis Chart is ready and It will look like below image.

Frequency Analysis Chart
Frequency Analysis Chart

Click here to download this Excel file

Watch the video tutorial of Frequency Analysis Chart

PK
My Name is PK. I am founder of PK-AnExcelExpert.com. I am a Microsoft Certified Professional. I have more than 10 years of experience in Data Visualization, Excel Automation and dashboard creation. Excel is my passion and I like to do always something innovative in Excel and share it with other people.
https://www.pk-anexcelexpert.com

Leave a Reply