How to Make a Frequency Chart in Excel (Step-by-Step)

Have you ever felt lost in a huge database of numbers trying to make sense of where everything fits in? In today’s data-driven world, that’s a common struggle.

But what if I told you there is a tool that can help you visually see where all the numbers align? 🧐

Enter the frequency chart – one that makes complicated data easy to understand. There are three different ways of creating a frequency chart in Excel and we will be exploring both below.

You can download our free sample workbook here for hands-on practice with frequency charts. Let’s get right into the tutorial 🚀

How to Create Frequency Distribution Table Using Frequency Function

To create a frequency distribution table in Excel, you need to have data with different recurring values. Let’s assume we have the following sample data 📗

Sample frequency distribution data in column A

We can say that a variety of values appear in the data repeatedly. What the frequency chart will do is plot a graph that gives a visual representation of the values that appear and the number of times they appear.

But to do that, you need to have two essential data arrays or input ranges in your data set. These are the Bins and Frequency of Data itself.

Let’s see how to extract this range of values from your data set below.

To create Bins,

Step 1) Select your original data set.

Select data in column A

Step 2) Go to the Data tab on the ribbon.

Step 3) Under the Sort & Filter section, click Advanced.

Click Advanced under the Sort & Filter feature

Step 4) The Advanced Filter dialogue box will appear on the screen.

Step 5) Under Action, it shows two options, select Copy to another location.

Step 6) In the Copy bar, you need to select the column where you want your unique values to appear.

Select column B

Step 7) Click on the arrowhead at the end of the Copy bar and select cell B2.

Step 8) Click on the arrowhead again to return to the dialogue box.

Step 9) Tick the Unique Records only box at the bottom left of the dialogue box

Step 10) Press Ok.

Checkmark the Unique Records box

Step 11) The unique values from your data set will appear in column B.

Step 12) In the column header, change the name to Bins Range.

Change the name of the column header

Hold on, we’re not done yet! There’s one last thing to do.

Step 13) Select your Bins Range column.

Step 14) Go to the Home tab and under the Editing section, click Sort & Filter.

Step 15) From the dropdown that appears, select Sort A to Z🔡

Sort the values in the column using Sort & Filter

And it’s done. Your bin array is all sorted and ready to use.

Bin range sorted

Now, we need to calculate the frequency. To do that, we will use the frequency array formula:

Click to copy

where,

data_range refers to the actual data set with recurring values

bin_range refers to the unique values extracted from your data

Step 16) Select cell C1 and type in Frequency as a header.

Step 17) Insert the frequency formula in cell C2 as:

Click to copy

But don’t press Enter just yet!!

Step 18) Press CTRL+Shift+Enter on your keyboard ⌨

You will notice a pair of braces or curly brackets appear at the start and end of the frequency function as shown in the image below. This indicates that the current formula is an array formula.

Kasper Langmann, co-founder of Spreadsheeto

Step 19) Double-click the Fill Handle or drag it down to cell D5 to calculate the frequency for the remaining cells.

Frequency appears in column C

And your frequency distribution table is all ready!

How to Create a Frequency Chart Using Column Chart

Under this heading, we will see how to create a frequency chart in Excel in the easiest way possible.

To create a frequency chart in our Excel spreadsheet.

Step 1) Select your output range or Frequency column.

Select the frequency column

Step 2) Go to the Insert tab on the ribbon.

Step 3) Under the Charts section, click on Insert Column or Bar chart and select a 2D column chart

Select and create a 2D column chart

Step 4) In the chart output, you will see the frequency values on the y-axis and different bin values on the x-axis.

Frequency appears on the chart

Step 5) To change the bin values, right-click the chart and click Select Data from the dropdown menu that appears.

Select Data from dropdown menu

Step 6) A Select Data Source dialog box will appear on the screen 💻

Step 7) Under the Horizontal Axis Labels, click Edit.

Select Edit from dialog box

Step 8) A small Axis Label will pop up, select the bin range values in this range using the arrowhead.

Step 9) Press Ok.

Select bin range

Step 10) Click on the plus icon (+) on the top right corner of the chart and check Axis Title.

Checkmark Axis title

Step 11) You will see Axis Title boxes appear on the x and y-axis of the chart.

Step 12) Fill in Frequency on the left side and in Range on the right.

Step 13) You can also fill in the Chart Title given at the top centre of the chart or delete it.

Step 14) Once done, this is what your final frequency distribution chart will look like:

Final frequency distribution table

It took some time, but we got it all right 😉

How to Create Frequency Distribution Table Using Data Analysis

Using Data Analysis is the easiest way of creating a Frequency Distribution table and it takes less than two minutes to create one. Don’t believe us? See for yourself below.

Say, we have the following sample data.

Sample data for Data Analysis

Now, to create the table using Data Analysis, you need to have the Add-in enabled. If you have it enabled, you will be able to find the Data Analysis option on the Data Tab under the Analysis section.

Select Data Analysis under Data Tab.

If you can’t find it on the Data tab, it might be because you have turned the Data Analysis ToolPak Add-in off. To turn it on,

Step 1) Go to File > More > Options.

Select Options

Step 2) Click Add-ins from the list of Categories. In the Manage bar, select Excel Add-ins and click Go.

Select Excel Add-ins

Step 3) From the Add-ins dialog box that appears, check the Analysis ToolPak option

Step 4) Press Ok.

Select Analysis ToolPak

The Data Analysis Add-in should now appear in your Data tab. You can learn more about it here.

Step 5) Click on the Data Analysis button.

Step 6) A Data Analysis dialog box will appear.

Step 7) From the list, select Histogram and press OK.

Select Histogram

Step 8) The Histogram window pops up on the screen.

Step 9) In the Input range, select your raw data set.

Step 10) In Bin Range, select the column containing unique values from your data set.

Step 11) Check the Labels box.

Step 12) Under output options, you can choose to either create the table on the same worksheet in range or choose a New Worksheet 📝

Step 13) We chose Output Range.

Select Output Range

Step 14) Select the area on your sheet where you want the table to appear.

An extra feature that Histogram offers over other methods is the option to calculate Cumulative Frequency.

To add that to your table,

Step 15) Check the Cumulative Frequency box at the bottom of the dialogue box.

Tick Cumulative Frequency

But don’t go on pressing Enter yet.

How to Create a Frequency Chart Using a Histogram Chart

Let’s now see how to create a Histogram chart in MS Excel 📗

Start right from where we stopped – in the Histogram dialog box, check the Chart output box at the bottom and press Enter.

Select Chart Output

Here’s where all the magic happens.

The Histogram will now appear with the Unique Values, Frequency, Cumulative Frequency columns and Frequency chart. This is what your worksheet must look like:

Histogram appears on screen

Pretty cool, right?

You can make little tweaks to the final output and charts if you like but the major part of our frequency distribution table and chart is done here 📊

How to Create Frequency Distribution Table Using Pivot Table

Another easy way of creating a Frequency Distribution Table is using the Pivot Table. Let’s see how to do that below.

To create a Pivot Table,

Step 1) Select your raw data set.

Step 2) Go to the Insert tab and under the Tables section, click on Pivot Table.

Select Pivot table from Insert tab

Step 3) A Pivot Table dialog box will appear on the screen.

Step 4) Select your raw data set and select New Worksheet from the options

Step 5) Press Ok.

Select New Worksheet

Step 6) A blank Pivot Table will open up in a new worksheet.

Step 7) There will be a Pivot Table window pane on the right side.

Pivot Table window pane appears

Step 8) Select the Height column and drag it to the Row field at the bottom of the pane.

Step 9) Drag the same column again and drop it in the Values field.

Drag column to copy data to table

Step 10) Your Pivot Table will now look something like this:

Pivot table appears

This means we’re on the right path.

Step 11) Right-click the Sum of Height column and select Value Field Settings from the menu.

Select Value Field Setting from options

Step 12) A value Field Settings dialog box will appear.

Step 13) From under the Summarize value field by, select Count.

Step 14) Press Ok.

Select Count

Let’s now group our Row Labels. Note that this step is optional and if you don’t want to group your data, skip to the Pivot Chart part.

Step 15) Select any cell in the Row Labels column and right-click.

Step 16) Select Group from the dropdown menu that appears.

Select Group

Step 17) A small Grouping dialog box will appear.

Step 18) Set the distribution of the values as you like – we set it by 3.

Set distribution values

Grouping distributes your data set according to the values provided. That means if I set my By value as 5, my row label will contain values like 160-164 and so on. This helps us know how many values occur in a certain range.

Kasper Langmann, co-founder of Spreadsheeto

Step 19) Press Ok.

And it’s done. Your Frequency Distribution Table has been created.

Your final Pivot Table will now look something like this:

Final pivot table

Pretty cool, right? 😀

How to Create a Frequency Chart Using a Pivot Chart

Now that we have our pivot table with all the data points we need, let’s create our frequency chart using a pivot chart in Microsoft Excel.

To do that,

Step 1) Select any cell from the Pivot Table.

Step 2) On the ribbon, go to the PivotTable Analyze tab.

Step 3) Under the Tools section, click on Pivot Chart 📊

Select Pivot Chart

Step 4) From the Insert Chart dialog box, select the Clustered Column chart.

Step 5) Press Ok.

Insert chart

Step 6) The Pivot Chart will appear on the screen with the grouped Height and Frequency values plotted on the x and y-axis, respectively.

Chart for grouped values

Let’s quickly make some formatting changes to the chart and it will be ready in no time.

Step 7) Click on the plus icon (+) at the top right corner of the chart.

Step 8) Uncheck Legend – this will remove the unnecessary Total written on the chart.

Uncheck Legend

Let’s now remove the annoying Field Buttons on the chart.

To do that,

Step 9) Right-click the Count of Height button at the top left of the chart.

Step 10) Select Hide All Field Buttons on the Chart.

Hide all Field Buttons

And the Field Buttons have been removed.

Step 11) Click on the plus icon (+) at the top right corner of the chart ➕

Step 12) Uncheck the Chart Title and check the Axis Titles.

Uncheck chart title

Step 13) In Axis Titles, type in Frequency on the left and Height at the bottom.

And it’s done. Your Frequency Distribution Chart for Grouped Values is ready.

The same for Ungrouped Values looks something like this:

Ungrouped values chart

That was easy, no?

Conclusion

In this tutorial, we saw three different methods of creating a frequency distribution table and chart in Excel📗

The major difference between all these methods is that unlike in the frequency function methods, you don’t need to find Bin Ranges and Frequency explicitly.

Nevertheless, all methods offer a clear and concise way of representing data visually. For beginners, however, I would suggest the Data Analysis method as it is easier and quicker to use 😀

Read our other related articles below:

We hope you enjoyed reading this article as much as we enjoyed creating it.