Histogram in Excel: master frequency polygons
![Screen with graphics](https://www.ninjaexcel.com/wp-content/uploads/2021/09/8.webp)
Key information
A HISTOGRAM It is simply a visual representation of the times a group of events occurs. It is similar to a bar graph, where each bar represents the frequency at which what you want to measure occurred. For example, in the scores of a group of students, you can see how many scored above 90%, how many scored between 80% and 90%, and so on.
The basics
Data set: Histograms are made on a set of data. The idea is that there are enough of them so that the histogram can faithfully represent the frequencies: if there is little data, it is more likely that each event occurs very few times or not at all, so the graph does not provide much information. Categories do not have to be numbers, but can also be words.
Interpretation: Each bar indicates the number of times a certain event or set of events occurred. If one bar is higher than another, then that event occurred more times than the other.
Advantages: It is a quick and convenient way to summarize information and interpret data. If you have the latest version of Excel, it is also very easy to do. If this is not your case, don't worry! Here we tell you step by step how to achieve a histogram in Excel regardless of the version you have of the program.
Step 1: Select the data.
Consider the following course list with student grades:
![histogram data in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_218_news_big.png)
Select the entire database where you have the information:
![select histogram data in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_220_news_big.png)
Ninja Tip: You can select cells with keyboard shortcuts, holding down the SHIFT key and moving with the keyboard arrows.
Step 2: Insert your histogram into Excel
Go to the “Insert” tab on the toolbar, and in the “Charts” section select the histogram or statistics graph icon.
![insert histogram in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_221_news_big.png)
When you click, two options will appear, and you must select the first of the “Histogram” options.
![select histogram in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_222_news_big.png)
You will see that a histogram will automatically appear in your Excel spreadsheet.
![histogram in excel, frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_223_news_big.png)
Step 3: Customize and design your histogram.
First, double click on “Chart Title” to edit it. Notice that the outline of the box containing the title becomes a dashed line. Choose the one you want. In this case, we will leave “Student Grades”.
![histogram format in Excel, frequency graph in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_224_news_big.png)
We will also add titles to the axes to know what each one corresponds to. Make sure your chart is selected and click on the “Design” tab in the toolbar. You will see that it is just below “Chart Tools”, which only appears when you have this element selected in the spreadsheet.
![histogram design in excel, frequency polygon design in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_225_news_big.png)
On the left of the tab, select “Add Chart Element” and then “Axis Titles.” You will see that you can select to add a horizontal title or a vertical one. We will first select “Primary Horizontal”.
![histogram design axis title histogram in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_226_news_big.png)
Repeat the process again, but now selecting “Primary Vertical”.
![graph design histogram design in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_227_news_big.png)
We see that the graph now has two axis titles, located horizontally and vertically.
![axis title histogram in excel frequency polygon excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_228_news_big.png)
Double click on each one to edit the content.
![Axis title histogram in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_229_news_big.png)
You can also change the colors and style of the histogram in the same “Design” tab, in the “Design Style” section.
![histogram design style in Excel frequency polygon design excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_238_news_big.png)
So far we have seen the most basic form of the histogram, and it leaves almost everything in the default form that Excel automatically returns. You may want to make many more changes after this, which we will see below.
Modify the ranges
Ranges are nothing more than an elegant way to call the “bars” that form in the histogram. You may want to have more or fewer ranges, or you may want to define the width of each one. For example, in the chart above we had four ranges, each with a width of 30%. You may want to have the information a little more disaggregated, so you could define more ranges or a smaller range width.
Step 1: Right click on the chart and select “Format chart area”.
![histogram range format in Excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_240_news_big.png)
Step 2: A panel will appear on the right of the screen to configure your chart. Click “Chart Options” and then “Horizontal Axis.”
![histogram axis options in Excel frequency graph in Excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_241_news_big.png)
Step 3: Click on the icon with vertical bars, and then on “Axis Options” to display the list of options. The default range is “Automatic”, that is, Excel does all the calculations for you.
![configuration bin range histogram in excel frequency polygon](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_242_news_big.png)
You can select “Range Width” and specify how thick you want each of your bars. For example, you can indicate that you want a width of 20%.
![bin format histogram range in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_243_news_big.png)
The result is as follows. If you notice, there is a difference of 20 percentage points between the upper and lower limit of each range, leaving five bars in total.
![bin width range width histogram in excel frequency polygon](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_244_news_big.png)
You can also select “Number of ranges”, which defines how many bars you want in your histogram. For example, six ranks instead of four.
![histogram in excel number of ranges number of bins frequency polygon](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_245_news_big.png)
Leaving the following histogram:
![frequency hitogram number of bins number of ranges frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_246_news_big.png)
Perhaps you noticed that under the “Number of ranges” option there are two boxes that you can check: “Overflow range” and “Underflow range”.
The first case (“Overflow range”) is used so that the last range contains from a lower limit to all numbers greater than this. For example, we want the last range to have all cases that scored more than 65%.
![overflow range histogram in excel frequency graph frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_297_news_big.png)
The result is as follows:
![overflow range histogram in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_298_news_big.png)
The second case (“Underflow range”) is the same case for the first range, that is, for it to contain all the values up to an upper limit. For example, we want the first range to contain all cases that scored less than 20%.
![underflow range histogram in excel frequency graph in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_299_news_big.png)
This is how the histogram looks:
![underflow range histogram in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_300_news_big.png)
Note that both options can be active, and that they can also be combined with both the “Range Width” and “Number of Ranges” options.
Category Histogram
The ranges configuration contains another alternative that we have not reviewed yet, which is by category. This applies when you want to graph the frequency of data that does not contain numbers, but rather text. For example, instead of grades, we could have a list of students' favorite colors.
![histogram categories in excel frequency polygon excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_301_news_big.png)
Step 1: In order for Excel to correctly interpret the text information to make a histogram, it is necessary to add a previous step to the case with numerical data. You simply have to add a new column that contains “1”, and which we will call “Auxiliary”.
![historange of categories in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_302_news_big.png)
This column will allow you to add up the number of times each of the colors appears.
Step 2: Select only the column with the categories you want to graph (“Color”) along with the “Auxiliary” column.
![histogram of categories in excel frequency polygon](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_303_news_big.png)
Step 3: Repeat Step 2 at the beginning of this post to insert the histogram.
![histogram in excel, frequency graph frequency polygon](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_304_news_big.png)
The result is as follows:
![histogram in excel, frequency graph in excel frequency polygon](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_305_news_big.png)
Clearly this is nothing like what we're looking for, but no problem, there's still one step left!
Step 4: In the Chart Area Format panel, you must select “By Category” so that Excel can understand that your histogram has text instead of numbers.
![histogram in excel histogram with categories excel frequency polygon excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_306_news_big.png)
And ready! After modifying the main title and the axis titles, the histogram finally looks like this:
![histogram with categories in excel frequency polygon in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_307_news_big.png)
Using the Analysis Tools Package (for older versions of Excel)
If you don't have the latest version of Excel (2016), you can use the data analysis package to achieve the same goal.
Installing the data analysis package
Step 1: In the toolbar, click “File” and then “Options” (in the lower left corner).
![excel analysis tools histogram excel 2013 frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_308_news_big.png)
Step 2: In the new options window, you must select “Add-ons”. In the “Manage” section, select “Excel Add-ins” and then click “Go.”
![analysis tools in excel histogram excel 2013 frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_309_news_big.png)
Step 3: A new window will appear, in which you must check “Analysis Tools” and then click “OK”.
![tools for analysis histogram excel 2013 frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_310_news_big.png)
If you go to the “Data” tab in the toolbar, you will see that a new section called “Analysis” has been added.
![histogram excel 2013 frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_312_news_big.png)
Inserting a histogram
Let's go back to the case of the initial base with student grades. The “Data Analysis” tool needs us to give it a little more information before inserting the histogram.
Step 1: Add a column with the ranges. Each element in the column indicates the upper limit of each range. That is, you will have as many ranges as the upper limits you have specified. If the last limit is less than the highest value in your data, then Excel will automatically add one more bar to hold the “remaining” data.
![histogram in excel 2013 data analysis frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_317_news_big.png)
Step 2: Go to the “Data” tab and click on the new “Data Analysis” tool.
![frequency histogram frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_318_news_big.png)
Step 3: In the new window, select “Histogram” and then click “OK”.
![histogram in excel 2013 frequency polygon excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_319_news_big.png)
Step 4: Incorporate the elements of your histogram.
- Select the “Ratings” column for “Entry Range.”
- Then select the “Ranges” column for “Class Range”.
- Check the “Labels” box as well since the columns include the headings.
- For the output options, select “Output Range” if you want the histogram to be on the same sheet in which you have the data, and then indicate the cells where you want Excel to output the histogram.
- Before finishing, check the “Create chart” box to have Excel automatically create the histogram. Finally click “Accept”.
![histogram excel 2013 frequency graph excel 2013 histogram data analysis in excel](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_314_news_big.png)
You've made it! As you can see, Excel outputs the chart data in a table in addition to the figure. The histogram is ready. All that remains is to fix the design.
![histogram frequencies histogram in excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_316_news_big.png)
Step 5: Configure the data. In the “Ranges” column of the data that the program returned to create the histogram, select the percentage format. You can also see this in the histogram. You can also later change the titles to have a figure that perfectly fits your needs.
![histogram excel 2013 frequency graph excel 2013](https://www.ninjaexcel.com/wp-content/uploads/2022/09/thumb_315_news_big.png)
Ninja Tip: If you don't want to have the "and greater..." bar, simply delete the row containing "and greater..." from the table and that will result in the same change in the histogram.
Ninja Tip: If you want to learn and delve deeper into other types of charts in Excel, don't miss this post.
Frequent questions
To create a Histogram in Excel we must select the data and insert a graph, select Histogram and click accept
A Histogram in Excel is a graph that allows us to view columns that show frequency data