How to Make a Histogram in Excel

Display numerical data in a visually appealing way

Macbook Pro Beside Notebook With Ballpen

Negative Space / Pexels​

What is a histogram? It looks a lot like other bar charts, but it group numbers into ranges based on your determinations. Compared to other types of graphs, histograms make it easier to identify different data as well as categories and frequencies of occurrence.

You can create a histogram in Excel using its histogram tool. Just enter the data you want to analyze and the “bin numbers” you wish to use, which represent the frequency intervals you wish to use.

Histograms are supported by Excel 2016, 2013, 2010, 2007 and Excel for Mac, but the steps you take depend on which version of Excel you're using.

The Analysis ToolPak add-in is required to use the histogram tool. This add-in is not supported in Excel Online (Office 365). However, you can view a histogram created in a desktop version of Excel using Excel Online. 

How to Make a Histogram in Excel 2016

Excel 2016 has a histogram maker that's especially easy to use, as it’s one of the built-in charts available. 

  1. To begin, enter the data you want to use in your histogram into a worksheet. For example, enter the names of the students in a class in one column and their test scores in another. 

  2. Select the entire dataset.

  3. Go to the Insert tab and select Recommended Charts in the Charts group. 

  4. Go to the All Charts tab and select Histogram.

    Excel 2016 Histogram
    In Excel 2016, histograms are a built-in chart option.
  5. Select the Histogram option, then select OK.

  6. Right-click the vertical axis (the numbers beneath the chart) and select Format Axis to open the Format Axis pane and customize the histogram.

  7. Select Categories if you want to display text categories.

  8. Select Bin Width to customize the size of each bin. For instance, if the lowest grade in your dataset is 50 and you enter 10 in the Bin Width box, the bins will display as 50-60, 60-70, 70-80, and so on. 

  9. Select Number of Bins to establish a specific number of bins displayed.

  10. Choose Overflow Bin or Underflow Bin to group above or below a specific number. 

  11. Close the Format Axis pane when you have finished customizing the histogram.

How to Create a Histogram in Excel 2013, 2010, or 2007

Excel 2013 or earlier requires the Analysis ToolPak add-in to use the histogram tool. Make sure it's installed before you create a histogram in Excel.

  1. Go to the File tab, then select Options.

  2. Select Add-ins in the navigation pane. 

  3. Choose Excel Add-ins in the Manage drop-down, then select Go.

  4. Select Analysis ToolPak, then select OK.

    Excel Analysis ToolPak
  5. The Analysis ToolPak should be installed.

Once you've added the Analysis ToolPak, you can access it in the Analysis group under the Data tab. 

  1. Enter the data you want to use in your histogram into a worksheet. For example, enter the names of the students in a class in one column and their test scores in another. 

  2. Enter the bin numbers you want to use into a third column. For instance, if you want to display test scores by letter grade, you might enter 40, 50, 60, 70, 80, 90, and 100 into the cells of the third column.

  3. Go to the Data tab. In the Analysis group, select Data Analysis

  4. Select Histogram in the Data Analysis dialog box, then select OK. The Histogram dialog box will open.

  5. Select the Input Range (which would be the test scores in this example) and the Bin Range (which are the cells containing the bin numbers).  

  6. Choose the Output Range if you want the histogram to appear on the same worksheet. Otherwise, choose New Worksheet or New Workbook

  7. Select the Chart Output checkbox, then select OK. Excel will insert a static histogram on the sheet you selected.

Create a Histogram in Excel 2016 for Mac

You can easily create a histogram in Excel 2016 for Mac after installing the Analysis ToolPak.

The add-in is not available in Excel 2011 for Mac.

  1. Go to the Tools menu and click Excel Add-ins.

  2. Choose Analysis ToolPak in the Add-ins Available box and click OK

  3. Click Yes to install the add-in if prompted. 

  4. Exit Excel and restart the program. The Data Analysis option will appear on the Data tab. 

Once you've installed the add-in, you can create a histogram:

  1. Enter the data you want to use in your histogram into a worksheet. For example: enter the names of the students in a class in one column and their test scores in another. 

  2. Enter the bin numbers you want to use into a third column. For instance, if you want to display test scores by letter grade, you might enter 40, 50, 60, 70, 80, 90, and 100 into the cells of the third column.

  3. Go to the Data tab and click Data Analysis

  4. Select Histogram and click OK.

  5. Select the Input Range (which would be the test scores in this example) and the Bin Range (which are the cells containing the bin numbers).  

  6. Choose the Output Range if you want the histogram to appear on the same worksheet. Otherwise, choose New Worksheet or New Workbook

  7. Click the Chart Output checkbox, then click OK. Excel will insert a static histogram on the sheet you selected.

    Excel histogram
    Create a custom histogram; Excel makes it easy.
  8. You're done!