How to create a frequency table in Excel
You can watch a video tutorial here.
Frequency tables are an integral part of statistical analysis. These are used to organize data in a way that is easy to understand. A frequency table tells you the frequency or how often an event occurs. For example, you could have a list of scores in a maths exam. From this, you could build a frequency table to understand in what range most of the students have scored. In Excel, you can use the Data Analysis tool to create a frequency table. This tool requires the bins as an input so the approach we will follow is:
1. Find the maximum and minimum values of the data range
2. Define the lower and upper limits of the bins based on the maximum and minimum values
3. Create the frequency table using the Data Analysis tool
Note: If the Data Analysis button is present under Data > Analyze, then skip steps 1 to 3
Step 1 – Open the Excel Options window
– Go to File > Options
Step 2 – Manage the Add-ins
a. Go to Add-ins
b. Select Excel Add-ins from the Manage drop-down
c. Click Go
Step 3 – Load the Analysis ToolPak add-in
d. Select Analysis ToolPak
e. Click OK
Step 4 – Find the maximum value of the data range
– Select the cell where the maximum number is to be displayed
– Type the formula using cell references:
=MAX(range of Maths score)
– Press Enter
Step 5 – Find the minimum value of the data range
– Select the cell where the minimum number is to be displayed
– Type the formula using cell references:
=MIN(range of Maths score)
– Press Enter
Step 6 – Create the bins
– Including the minimum and maximum values, define the lower and upper limit for each bin
– The upper limit for the last bin should be equal to or greater than the maximum value
– All bins should have equal ranges
Step 7 – Open the Data Analysis box
– Go to Data > Analyze
– Click on the Data Analysis option
Step 8 – Open the Histogram definition box
– Select Histogram
– Click OK
Step 9 – Define the parameters
– Enter the parameters using the cell references:
>Input range: the range of ‘Maths score’
>Bin range: the range of ‘Upper Limit’
>Output range: select where the result is to be displayed
– Tick the Chart output box to display the histogram as well
– Click OK
Step 10 – Check the result
– The frequency table is displayed along with a Histogram