How to make a histogram in Excel

Statistical data analysis is key in the study of various phenomena associated with events or activities in daily and industrial life. One of these tools are histograms , which are generally column graphs associated with a frequency of data.

A kind of particular histogram turns out   to create a Pareto chart . In this post we will talk about how to make a histogram in Excel to analyze the frequency of data.

How to Make a Histogram in Excel to Analyze Data Frequency

Index( )

  1. On what devices can you make a histogram in Excel?
    1. on Windows
    2. on macOS
    3. from Android
    4. on iOS
  2. What data should a histogram contain?
    1. input data
    2. location numbers
  3. How do you make a histogram in Excel using the add-ins?
    1. With the same class rank
    2. With different class ranges

On what devices can you make a histogram in Excel?

Microsoft Excel is an office automation tool that is compatible with many operating systems (OS), which is why you will be able to carry out the activities allowed by this software on various devices; Among the devices that you can use to develop a histogram in Excel are the following:

on Windows

Before starting the procedure to make your histogram, make sure you have the data analysis tool active in your version of Excel; To do this, visit the official Microsoft page and follow the instructions to load the analysis tool in Excel . Then follow the steps below to get your histogram:

  • Open a new spreadsheet and put in your data for analysis, better known as input data.
  • Try to organize your data in a matrix of “n” rows by “m” columns.
  • In a separate column put your “class rank”.
  • Navigate the top menu to the “Data” tab, and then select “Data Analysis”.
  • Look for the “histogram” optionand tap on it.
  • Click “Accept” and a pop-up window will appear that you must fill out.
  • In “Input range” select the data of the matrix indicated in the second point.
  • In “Class range” put the data corresponding to the column with the same name.
  • The “Output Range” is the spreadsheet spacewhere the resulting table generated by Data Analyzer will be placed.
  • Finally click or check the “Create Chart” option to generate the column chart.
  • Then click on “OK” and you will have as a result the histogram you are looking for.

on macOS

  • Choose the data with which you want to form your histogram, you can place them in a column.
  • Navigate the menu and choose “Insert”, then look for the option with the graph or statistics symbol and click on it.
  • Select the “Histogram” option and voila, you will have your graph.

from Android

  • Select the data.
  • From the mobile, press the “Edit” option and then navigate the menu to “Insert”, look for the “graphic” option and choose “histogram”.
  • By clicking on “Histogram”, you will have your graph ready.

on iOS

Follow the same steps that we showed you in the previous section corresponding to Android devices, and by completing these you will be able to obtain your graph or Histogram.

What data should a histogram contain?

We have already shown you how to obtain your histogram on various devices with different operating systems, however, you need to take into account what data your graph should have . For this, we will tell you below what these data should be:

input data

This is the data that will be analyzed for the elaboration of the histogram. It is important that you keep in mind that these amounts cannot be alphanumeric, that is, they have to be numbers or amounts that can be processed as such. You have the possibility to use tables in Excel and use them to organize this data.

location numbers

This concept corresponds to the intervals or ranges of input data that will be used to classify and group the amount of data in each of these; that is, it is the typical “Class Range” that is used in a table of ordered data with their respective frequencies.

How do you make a histogram in Excel using the add-ins?

As you may have noticed, there are several ways to make a histogram in Excel, so you can choose the one that is easiest for you or to your liking. In addition, you will need to take into account the nature of the data range ; Well, you will find situations that are simple and direct action, and others that require additional steps, for example:

With the same class rank

This situation is the simplest, since the range of classes is divided into intervals of equal value , so the resulting frequency is the data with which the histogram will be made. In this case, the procedure that we have explained so far will serve you.

With different class ranges

This type of situation changes the procedure to obtain the histogram, since the difference in the width of the class interval does not allow obtaining a correct graph or histogram directly. In this sense, you will have to make an adjustment in the source data, to obtain additional columns of study that will be the ones that you represent in the histogram.

A class interval width column and a frequency density column are needed to make this type of histogram. The width of the class interval is obtained by subtracting the upper limit from the lower limit of each interval, and recording it in the row corresponding to each interval.

Then you must find the frequency density, and this is obtained by dividing the frequency of each interval by the amplitude of each interval; You must record the resulting value in the cell corresponding to the row of each interval with respect to the frequency density column.

Having the complete frequency density data, you can proceed to make your histogram following the steps mentioned in the corresponding section; you will only have to replace the original input data with the data corresponding to the “Frequency density” column in the “Input range” field, while in the “Class range” field you will have to put the current class interval.

Finally, you should know that Excel is a very powerful software that will not only allow you to manage histograms, but will also allow you to create graphs of another nature from tables with data . So it is a very versatile tool . We hope this post has been to your liking and usefulness.


by Abdullah Sam
I’m a teacher, researcher and writer. I write about study subjects to improve the learning of college and university students. I write top Quality study notes Mostly, Tech, Games, Education, And Solutions/Tips and Tricks. I am a person who helps students to acquire knowledge, competence or virtue.

Leave a Comment