How to Create a Histogram in Excel

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

Creating a Histogram in Excel

The Histogram and the Pareto Diagram are two types of charts connected with quality management that can be created in Excel. A histogram chart is a specialized column chart used to represent the frequency or occurrence of a particular characteristic within a range of values.

A sample histogram looks like this:

example of histogram in Excel

Begin with a dataset that you want to analyze. For example, let’s consider exam scores as shown below:

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

First, delete the legend as 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 has 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:

example of histogram in Excel

Creating a Histogram using Data Analysis Toolpak add-in

Alternatively, you can use Excel’s Data Analysis ToolPak add-in to create a histogram more efficiently:

  1. Install ToolPak: If you haven’t already, enable the Data Analysis ToolPak add-in in Excel.
  2. Access Data Analysis: Click the “Data” tab on the ribbon and select “Data Analysis” in the Analysis group.
  3. Choose Histogram: In the Data Analysis dialog box, choose “Histogram” and click “Ok”.
See also  How to Add Multiple Trendlines in Excel

Histogram Data Analysis Toolpak

Fill in the input range with your data, specify the bin range, and select “Chart Output”.

Excel will generate the histogram chart automatically.

Download free spreadsheet