How to create a simple histogram chart in Excel

Excel 365
A histogram chart is often confused with a bar chart because it displays data using bars of different heights. The 'true' histogram chart shows the distribution of numerical data. Each bar of the histogram represents the count of data values within the specified range. See more about different types of histogram charts in Excel.
Simple histogram chart in Excel 2016

The chart above shows the distribution of employees ages. The graph contains two unique bins <= 25 and > 55 covering edge cases.

To create a histogram in Excel 2016, do the following:

   1.   Add the data for the chart and transform it (if needed):

For this example, the birthday date transformed to the age of people:

The data for simple histogram chart in Excel 2016

To calculate full years, use the formula:

= DATEDIF (<birthday>, TODAY (), "y")

Note: The function TODAY () returns the current date, and DATEDIFF () with the last "y" parameter returns the full number of years between two dates.

See more How to calculate age in Excel.

   2.   Create a histogram chart:

   2.1.   Select the prepared data (in this example, C2:D20).

   2.2.   On the Insert tab, in the Charts group, click the Statistic button:

The Insert Statistic Chart button in Excel 2016

From the Statistic dropdown list, select Histogram:

The Histogram Chart in Excel 2016

Excel creates the histogram chart from the data:

The simple Histogram chart in Excel 2016

As you can see, the default histogram has too many bins (bars), but you can change the number of bins and bin widths.

   3.   Format the histogram chart:

   3.1.   Right-click on the horizontal axis and choose Format Axis... in the popup menu:

Format Axes in popup Excel 2016

   3.2.   In the Format Axis pane, on the Axis Options tab, under Bins:

Format Axis for Histogram chart in Excel 2016
  • In the Bin Width field, define the number range size for bins.

    For this example, every age range spans 10 years.

  • Select the Overflow bin checkbox and type the number, all values above this number will be added to the last bin, ignoring other options such as Bin Width.
  • Select the Underflow bin checkbox and type the number, all values below and equal to this number will be added to the first bin, ignoring other options such as Bin Width.

Make other adjustments as you desire.

Note: Histograms show the shape and the spread of continuous data. For categorical data, discrete by its definition, you should use bar charts that represent category frequencies.

If you have any questions or suggestions, please feel free to ask OfficeToolTips team.

We use cookies to personalise content and ads, to provide social media features and to analyse our traffic. We also share information about your use of our site with our social media, advertising and analytics partners who may combine it with other information you’ve provided to them or they’ve collected from your use of their services.

Learn more