How To Make an Excel Frequency Distribution Table in 7 Steps

Updated June 24, 2022

Many professionals across several industries use Excel to create and organize their data. One way professionals can maximize their use of the program is with frequency distribution tables. Knowing about frequency distribution tables can help you develop your business, computer and Excel skills. In this article, we discuss what an Excel frequency distribution table is, list seven steps you can follow to create one and detail how you can use them to enhance your professional skills.

What is an Excel frequency distribution table?

An Excel frequency distribution is a tool for looking at how data you have varies across specific ranges of values. For example, you can set a scale with categories of 1,000 starting at one, and ending at 10,000 if you're working with numerical values between those endpoints. The first category is "1-1,000," the second is "1,001-2,000" with the pattern continuing until "9,001-10,000." The table itself tells you how many values are in those ranges. For example, you may have 32 values in the first category, 23 in the next and five in the last.

How to make a frequency distribution table in Excel

Using data you collect in an Excel spreadsheet, you can create a Pivot Table and then change that table into a frequency distribution. Below are steps you can use to create a frequency distribution table in Excel:

1. Insert a Pivot Table

After you input the data you use into an Excel spreadsheet or receive a spreadsheet with the data already in it, you can create a Pivot Table. Below are steps you can follow to create a Pivot Table in Excel:

Select a cell within your data set.
Click the "PivotTable" option in the "Insert" tab and the "Tables" group.
Click "OK" to create a Pivot Table with your data.

Once you create a Pivot Table, Excel prompts you with directions for creating the table you want. You can use these directions to alter how your frequency distribution table looks.

2. Drag the fields to their respective areas

The options Excel's dialog box gives allow you to move fields into different areas of the table. Depending on how you label the data you collect, different options may appear which you can choose. For example, if you have a column labeled "Amounts" in your data set with currency values, then a field titled "Amounts" is an option you can choose. When you choose that option, you can then designate where the value goes. For example, you can take the "Amounts" field and place it in both the "Rows" area or the "Values" area.

For a frequency distribution, the "Amounts" you use go to the "Rows" area and the "Values" area. Other fields you may have include the items the currency values relate to, and other information about those items. For a simple frequency distribution, it's okay to only worry about your "Amounts" field. If you want to come back and alter the frequency distribution table after you create it to add more information, you can.

3. Navigate to the "Value and Field settings..." option

Once you designate where your categories go in the table, you can access the "Value and Field settings..." option. This option lets you change how your Excel Pivot Table interprets the values you have. To get to the option, select a cell within your Pivot Table's "Amount Sum" column, right-click the value, then choose the "Value and Field settings..." button. This brings up a dialog box where you have options about how Excel calculates and summarizes the field you select. Some options in this section are:

Sum: The total value of the numeric value in the left adjacent cell of your Pivot Table
Count: How many occurrences of a value or value range occur from the value or range left adjacent of the cell in your Pivot Table
Average: The average value of your amounts in the left adjacent cells of your Pivot Table
Max: The highest value of the amounts in the left adjacent cells of your Pivot Table
Min: The lowest value of the amounts in the left adjacent cells of your Pivot Table

4. Choose "Count" and click "OK"

To create a frequency distribution table, you want Excel to count the number of times a value within a specific range occurs. From the list of options, "Count" lets you do this. After you select "Count," select "OK" to finalize your choice. Once you do, your Pivot Table may change the values in your second column to how many times the related values in the column to the left occur. For example, if you have three occurrences of the value $107, then the right column of your Pivot Table generates a value of "3" and places it in the table.

5. Navigate to the "Group..." option

The "Group..." option lets you group values together by creating specific ranges. For example, you can create ranges of 5, 10, 100 or any other value you find important for your data. To locate the "Group..." option, select any cell with the "Rows" label. Then, right-click on the cell you selected to bring up a menu of options. After that, select the "Group..." button from that menu.

6. Enter values for the distribution and click "OK"

Depending on the data you're working with, you may have different values for your distribution. For example, a small organization may only have a few products ranging from around $5 to about $200. To create a distribution table for how many of those items the organization sells during a marketing period, you can choose the "Starting at" section and enter the value 1. Then, you can choose the "Ending at" section and enter a value of 200. Next, you can select how large the ranges are by putting a value in the "By" section such as 25. Then click "OK" to apply your changes.

This creates eight even groups of 25 between 1 and 200 on the Pivot Table, changing your table. This allows you to see how many values occur in each of those ranges instead of for specific numerical values. Finally, your frequency distribution table is complete.

7. Save your Excel spreadsheet

There are a few ways you can save your Excel spreadsheet. If you haven't saved the document before, then you can go to the "File" tab at the top left of the Excel program, select it and then click the "Save" or "Save as" buttons. The "Save" button creates a document and store it automatically to a location on your computer. The "Save as" button allows you to choose the name of the file and where you store it on your computer, allowing you to find your document more easily.

If you already saved your document before, you can use a keyboard command to save a new version of your Excel spreadsheet. You can press the "Ctrl S" keys to save your spreadsheet automatically as though you clicked the "Save" button. This can help you save time as you update the sheet with new data.

Uses for a frequency distribution table

You can use a frequency distribution table to compare the number of values within specific ranges. This helps you understand at what rate those values occur compared to each other. This can help businesses compare how well different products or different price ranges affect their sales. You can also use a frequency distribution table to create quick visual representations for meetings.

How to create a histogram for your frequency distribution table

You can select any cell in your frequency distribution table to make a visual representation of your data. Once you do, navigate your Excel program to the "Analyze" tab. Under that tab, there are several groups. The one you want is the "Tools" group. This lets you choose to create either a PivotChart or other types of Pivot Tables, which Excel may recommend to you. You want to create a PivotChart, so click on that button. Once you do, a dialog box appears and you can simply click "OK" to create a visual representation of your frequency distribution table.

By default, Excel creates a histogram, which is a type of vertical bar chart that shows how different categories or ranges compare to one another. For example, you may see several ranges of numbers, the names of products or other information below blue bars on the chart. These bars may have different heights, according to the number of times a value occurs within that category. This means you can give other professionals a quick way to view the data you created, without getting into the specific table itself.

Please note that none of the companies mentioned in this article are affiliated with Indeed.