Skip to content
Skip to main content
header search
ahihi OpenLearn
OpenLearn
Menu
sticky search

About this free course

Become an OU student

Download this course

Share this free course

Course content Course content
Data analysis: visualisations in Excel
Data analysis: visualisations in Excel

Start this free course now. Just create an account and sign in. Enrol and complete the course for a free statement of participation or digital badge if available.

More free courses

2.2.2 Ungrouped frequency distribution tables

When you are summarising small amounts of data, then it is better to organise and represent it in an ungrouped frequency distribution table. This is a type of distribution that shows how many times each individual value occurs in a data set; they are usually used to calculate the accurate frequency of individual data values.

For example, say you are interested to know how many plants people have in their homes. A survey gives the following figures as number of plants that 18 different people have in their homes:

Number of plants = 1, 5, 2, 2, 3, 3, 5, 5, 1, 1, 1, 3, 4, 4, 2, 3, 3, 3

To answer your question, first you need to see the frequency of each value in the data. Value 1 occur 4 times, so you can describe it as 4 people having 1 plant. Then you do the same for the rest of the values, so: 3 people have 2 plants, 6 people have 3 plants, 2 people have 4 plants, and 3 people have 5 plants.

In the following activity, you will learn how to make ungrouped frequency tables in Excel.

Activity 2 How to make an ungrouped frequency table in Excel

Timing: Allow approximately 30 minutes to complete this activity

In this activity, you will learn how to make an ungrouped frequency table in Excel. Once you have produced the ungrouped frequency table in Excel, you may need to compare it with the final output by clicking ‘Reveal discussion’. This will help you to see whether you have produced the accurate ungrouped frequency table or not.

Watch Video 1, which gives on how to create a frequency table, or follow the instructions below.

Download this video clip.Video player: Video 1
Copy this transcript to the clipboard
Print this transcript
Show transcript | Hide transcript

Transcript: Video 1

NARRATOR
In this video, I will be showing you how to make an ungrouped frequency distribution table in Excel. Start by opening the Excel file JC_electrics. You can see here that there are four sheets within this Excel file.
For this activity, we are going to use the sheet titled ungrouped frequency table. Go ahead and select it by clicking the tab. Here you will find the quarterly data of the number of generators sold. Make sure that the data is arranged in columns. Label column B with the variable name Criteria and column C as Frequency.
Now, first, we will find the minimum and maximum values in the data. Select a cell outside of the range and type =MAX. And select the range, which is A5 to A28, then press Enter. This will give you the maximum value, which is 15.
To find the minimum value in column G, type =MIN. And select the range, which is A5 to A28. Press Enter. This will give you the minimum value, which in this case is seven.
To calculate the range in column G, type equals. And then select the maximum value, in this case, 15 minus. And then select the minimum value, which is seven. Then press Enter. This calculates the range of eight for this data set.
We will now arrange the values in column A labeled Generators from ascending to descending order. To do this, select the values A5 to A28 in column A. Then click Data in the toolbar. Select Sort. Then select Continue with selection and press Sort.
In column B, which we label Criteria, we will type in every value from our range starting from the minimum value of seven to the maximum value of 15. We will use this column to calculate the frequency.
Let's now assume we want to calculate in how many quarters the company sold a maximum of seven units. To do this, we need to calculate the frequency. Column A contains sales with values between 7 to 15, which we have arranged in descending order. Go to the first row of column C under the label Frequency and type =COUNTIF.
Select the range A5 to A28 in column A. Then select the first criteria, which is seven from column B. This will produce a frequency of four. This means that seven units were sold in four quarters. Repeat this step for each criteria until you reach the number of quarters in which 15 units are sold. Note how in this formula only the criteria value changes each time.
And here we have an ungrouped frequency table.
End transcript: Video 1
Download
Video 1
Interactive feature not available in single page view (see it in standard view).
  • Open the Excel file JC Electrics [Tip: hold Ctrl and click a link to open it in a new tab. (Hide tip)] . This file contains the quarterly data of number of generators sold. Make sure that the data is arranged in columns.
  • Copy the data containing the number of generators sold to Column A of a new worksheet.
  • Label Column B ‘Value’ and label Column C ‘Frequency’.
  • Find the minimum and maximum value in the data. In this example: =MAX(A5:A28), which is 15, and =MIN(A5:A28), which is 7
  • Calculate the range: (MAX – MIN), so 15 minus 7 equals 8.
  • Arrange the values in Column A in ascending order. Select the values (A5:A28) in Column A, click Data in the toolbar and then click Sort, select Continue with current selection and press Enter. Figure 8 shows how your information should be displayed.
JC Electrics Excel sheet
Figure 8 Arranging the data from ascending to descending order
Show description|Hide description

This figure shows the JC Electrics Excel sheet Generators column values on the left, which are: 7, 7, 7, 7, 8, 8, 9, 9, 9, 9, 9, 10, 10, 11, 11, 11, 12, 12, 12, 13, 13, 14, 14, 15. Next to this column are two empty columns labelled Value and Frequency. All three columns are under the headings ‘JC Electrics’ followed by (Quarterly data of units sold).

Figure 8 Arranging the data from ascending to descending order
  • To count the number of quarters in which 7 units were sold, you need to calculate the frequency in Column C. Type =COUNTIF (Range, value). For example, =COUNTIF (A5:A28,7)
JC Electrics Excel sheet
Figure 9 Calculating the frequency of ungroup data
Show description|Hide description

This figure shows the JC Electrics Excel sheet Generators column values on the left, which are: 7, 7, 7, 7, 8, 8, 9, 9, 9, 9, 9, 10. Next to this column are two further columns, with the first labelled as Value and the second labelled as Frequency. The Value column contains the values 7, 8, 9, 10, 11, 12, 13, 14, 15. The Frequency column in the row Generator:7, Value 7 shows the formula: =COUNTIF(A5:A28,7).

Figure 9 Calculating the frequency of ungroup data
  • You should now save your file as you will return to this ungrouped frequency table in a later activity.

Comment

Excel spreadsheet for JC Electrics
Figure 10 Ungrouped frequency table
Show description|Hide description

This figure shows an Excel spreadhseet for JC Electrics (Quarterly data of units sold). The first column, labelled Values, lists the values 7, 8, 9, 10, 11, 12, 13, 14, 15 The second column, labelled Frequency, contains the values 4, 2, 5, 2, 3, 3, 2, 2, 1.

Figure 10 Ungrouped frequency table

Figure 10 above shows the completed frequency table. The same data is shown in Table 6 below.

Table 6 Ungrouped frequency table
Value Frequency
7 4
8 2
9 5
10 2
11 3
12 3
13 2
14 2
15 1

As is mentioned above, ungrouped frequency tables are useful when you have a small set of data and you want to easily observe the frequency of each value in the data set. However, if you have a large data set then a grouped frequency distribution table is the best option; you will learn about these in the next section.