How to Create a Histogram in Excel

In this lesson, you will learn what a histogram chart is and how to plot one.

The Histogram and the Pareto Diagram are two types of charts connected with quality management that can be created in Excel. A histogram is simply a column chart which the present number of occurrence of the characteristic most often in the range of numbers. Besides quality control, a histogram is also used in statistics. A sample histogram looks like this:

Creating a Histogram in Excel

example of histogram in Excel

You can learn the rules for creating a histogram and how to create a histogram in Excel. For starters, you need a table of data. Let it be, that you analyze the results of the exam. An example table is:

sample table resulst of the exam

The main principles for creating a histogram are

  • data should be divided into equal intervals
  • number of compartments should be 5 -15 so that the histogram was clear
  • frequency in any of the intervals should not be equal to 0
  • between the columns is not to be gaps

Creating a histogram in Excel is very simple. First, based on the data table you create a column chart. First, delete the legend it will not be needed.

histogram without legend

Then you remove the spaces between the columns. Just right click on the column and choose ‘Format Data Series’. A dialog box appears, where you choose ‘No gap’.

histogram Format Data Series no gap

The most important steps you have already completed. Histogram have to be improved. Depending on your vision, you can choose the right colors to increase transparency. One possibility is in the chart styles. In addition, you must add the data series. The finished sample histogram looks like this:

See also  How to Make a Chart for Large Data Set

example of histogram in Excel

The results of the examination are, of course, only an example. You can also use a histogram to illustrate population demographics and broad capabilities in quality control.

Creating a Histogram using Data Analysis Toolpak add-in

Instead of creating a step-by-step histogram, you can use the Analysis Toolpak add-in.

With the Analysis ToolPak installed, click on the Data tab on the ribbon and select the Data Analysis button in the Analysis group.

In the Data Analysis dialog box, select the Histogram option and click on the OK button.

Histogram Data Analysis Toolpak

Enter a range of values in the input range field.

As Bin Range, enter the range of your bins.

Select Chart Output.

Clicking OK will automatically create a histogram.

Download free spreadsheet

The Data Analysis Toolpak is not available in all versions of Excel. If you are using a version of Excel that does not have the Data Analysis Toolpak, you can use the FREQUENCY function to calculate the frequency of values in a data set and then use a bar chart to visualize the data.