How To Make An Ungrouped Frequency Distribution In Excel
Mireille Duhon
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.
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.
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.
1999-2024. All rights reserved. The Open University is incorporated by Royal Charter (RC 000391), an exempt charity in England & Wales and a charity registered in Scotland (SC 038302). The Open University is authorised and regulated by the Financial Conduct Authority in relation to its secondary activity of credit broking.
Another type of frequency distribution we could create is an ungrouped frequency distribution, which displays the frequency of each individual data value rather groups of data values.
The easiest way to visualize the values in an ungrouped frequency distribution is to create a frequency polygon, which displays the frequencies of each individual value in a simple chart.
Statology makes learning statistics easy by explaining topics in simple and straightforward ways. Our team of writers have over 40 years of experience in the fields of Machine Learning, AI and Statistics. Learn more about our team here.
Statology Study is the ultimate online statistics study guide that helps you study and practice all of the core concepts taught in any elementary statistics course and makes your life so much easier as a student.
While everyone knows how easy it is to create a chart in Excel, making a histogram usually raises a bunch of questions. In fact, in the recent versions of Excel, creating a histogram is a matter of minutes and can be done in a variety of ways - by using the special Histogram tool of the Analysis ToolPak, formulas or the old good PivotTable. Further on in this tutorial, you will find the detailed explanation of each method.
Have you ever made a bar or column chart to represent some numerical data? I bet everyone has. A histogram is a specific use of a column chart where each column represents the frequency of elements in a certain range. In other words, a histogram graphically displays the number of elements within the consecutive non-overlapping intervals, or bins.
The following screenshot gives an idea of how an Excel histogram can look like:
How to create a histogram in Excel using Analysis ToolPakThe Analysis ToolPak is a Microsoft Excel data analysis add-in, available in all modern versions of Excel beginning with Excel 2007. However, this add-in is not loaded automatically on Excel start, so you would need to load it first.
If Excel shows a message that the Analysis ToolPak is not currently installed on your computer, click Yes to install it.
Now, the Analysis ToolPak is loaded in your Excel, and its command is available in the Analysis group on the Data tab.
Specify the Excel histogram bin rangeBefore creating a histogram chart, there is one more preparation to make - add the bins in a separate column.
Considering the above, type the bin numbers that you want to use in a separate column. The bins must be entered in ascending order, and your Excel histogram bin range should be limited to the input data range.
In this example, we have order numbers in column A and estimated delivery in column B. In our Excel histogram, we want to display the number of items delivered in 1-5 days, 6-10 days, 11-15 days, 16-20 days and over 20 days. So, in column D, we enter the bin range from 5 to 20 with an increment of 5 as shown in the below screenshot:
Make a histogram using Excel's Analysis ToolPakWith the Analysis ToolPak enabled and bins specified, perform the following steps to create a histogram in your Excel sheet:
To do this, you can place the cursor in the box, and then simply select the corresponding range on your worksheet using the mouse. Alternatively, you can click the Collapse Dialog button , select the range on the sheet, and then click the Collapse Dialog button again to return to the Histogram dialog box.
Tip. If you included column headers when selecting the input data and bin range, select the Labels check box.
- Select the Output options. To place the histogram on the same sheet, click Output Range, and then enter the upper-left cell of the output table.
For this example, I've configured the following options:
- And now, click OK, and review the output table and histogram graph:
To make an automatically updatable histogram, you can either use Excel functions or build a PivotTable as demonstrated below.
How to make a histogram in Excel using formulasAnother way to create a histogram in Excel is using the FREQUENCY or COUNTIFS function. The biggest advantage of this approach is that you won't have to re-do your histogram with each change in the input data. Like a normal Excel chart, your histogram will update automatically as soon as you edit, add new or delete existing input values.
Like the Histogram option of the Analysis ToolPak, the Excel FREQUENCY function returns values that are greater than a previous bin and less than or equal to a given bin. The last Frequency formula (in cell E9) returns the number of values greater than the highest bin (i.e. the number of delivery days over 35).
Note. Because Excel FREQUENCY is an array function, you cannot edit, move, add or delete the individual cells containing the formula. If you decide to change the number of bins, you will have to delete the existing formula first, then add or delete the bins, select a new range of cells, and re-enter the formula.Making a histogram using COUNTIFS functionAnother function that can help you calculate frequency distributions to plot histogram in Excel is COUNTIFS. And in this case, you will need to use 3 different formulas:
Tip. If you plan to add more input data rows in the future, you can supply a bigger range in your FREQUENCY or COUNTIFS formulas, and you won't have to change your formulas as you add more rows. In this example, the source data are in cells B2:B40. But you can supply the range B2:B100 or even B2:B1000, just in case :) For example:
=FREQUENCY(B2:B1000,D2:D8)Make a histogram based on the summary dataNow that you have a list of frequency distributions computed with either FREQUENCY or COUNTIFS function, create a usual bar chart - select the frequencies, switch to the Insert tab and click the 2-D Column chart in the Charts group:
Generally speaking, you already have a histogram for your input data, though it definitely requires a few improvements. Most importantly, to make your Excel histogram easy to understand, you need to replace the default labels of the horizontal axis represented by serial numbers with your bin numbers or ranges.
The easiest way is to type the ranges in a column left to the column with the Frequency formula, select both columns - Ranges and Frequencies - and then create a bar chart. The ranges will be automatically used for the X axis labels, as shown in the below screenshot:
Tip. If Excel converts your intervals to dates (e.g. 1-5 can be automatically converted to 05-Jan), then type the intervals with a preceding apostrophe (') like '1-5. If you want the labels of your Excel histogram to display bin numbers, type them with preceding apostrophes too, e.g. '5, '10, etc. The apostrophe just converts numbers to text and is invisible in cells and on the histogram chart.If there is no way you can type the desired histogram labels on your sheet, then you can enter them directly on the chart, independently of the worksheet data. The final part of this tutorial explains how to do this, and shows a couple of other improvements that can be made to your Excel histogram.
How to make a histogram with a PivotChartAs you may have noticed in the two previous examples, the most time-consuming part of creating a histogram in Excel is calculating the number of items within each bin. Once the source data has been grouped, an Excel histogram chart is fairly easy to draw.
To create a pivot table, go to the Insert tab > Tables group, and click PivotTable. And then, move the Delivery field to the ROWS area, and the other field (Order no. in this example) to the VALUES area, as shown in the below screenshot.
If you have not dealt with Excel pivot tables yet, you may find this tutorial helpful: Excel PivotTable tutorial for beginners.
2. Summarize values by CountBy default, numeric fields in a PivotTable are summed, and so is our Order numbers column, which makes absolutely no sense :) Anyway, because for a histogram we need a count rather than sum, right-click any order number cell, and select Summarize Values By > Count.