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

Once the basic histogram is created, it can be customized to improve clarity and visual appeal. Adjust colors and styles to enhance readability according to your preference, accessible through the ‘Chart Styles’ options.

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  Waffle chart

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