How to Create Histograms in MS Excel

A histogram is a visual representation of numerical data, which you can create in MS Excel. It is a type of graph, where information is grouped into bins that cover a range of values and are represented as columns. 

In this tutorial, we will walk you through the step-by-step instructions to create a histogram in Microsoft Excel 2007, 2016, and above. Let us explore the three methods to create the histogram.

3 Methods to create histograms in Microsoft Excel:

  • Use the Insert Chart menu – (Excel 2016 and above)
  • Use the Data Analysis menu – (Excel 2007 and above)
  • Use the FREQUENCY function

Let us begin.


Method #1: Use the Insert Chart Menu

In Excel 2016, you can insert a dynamic histogram. It means any changes made to the dataset will update the corresponding chart.

Step #1: Open an Excel sheet

Open a new MS Excel spreadsheet.

In the new sheet, create a table displaying the salaries of employees, like our example below. 

You can also open an existing Employee Salary file or similar spreadsheet.

Step #2: Select the data

From the Salary column (column B) select the cell range B2:B31 by clicking with your mouse on B2. While holding the mouse button down, drag your cursor to the bottom of the column.

This will be the source data to create a histogram of the employee salary distribution.

Step #3: Select the chart type

From the main menu bar, click on the Insert tab.

From the Insert ribbon that displays, click on the Insert Statistic Chart icon drop-down. 

Tip: Hover with your mouse over each icon to see its name.

Select Histogram from the drop-down options.

Excel creates a histogram as displayed in our example below.

Notice the following:

  • Excel has autogenerated 3 bins and set them to intervals of 7000. 
  • The first column shows 17 employees drawing a salary more than or equal to $9000 and less than or equal to $16000, represented between two square brackets as [9000,16000].
  • In the second and third columns notice the use of parenthesis before the first number and a square bracket after the second number (16000,23000] and (23000,30000]. 
  • It means that the values collected in the second bin are for salaries more than $16000 and up to $23000.
  • The values collected in the third bin are for salaries of more than $23000 and up to $30000.

Step #4: Add Elements

To understand the chart better, we can add special elements.  

As an example:

Click the Green plus icon on the right side of the chart.

Select the elements that you want to add, as displayed in the example below.

This can be: Axes | Axis Titles | Chart Title | Data Labels | Gridlines or Legend.

For more information on customizing your chart, we have added a special section at the bottom of this article.

But first, ensure that you have created the perfect chart that matches your needs.

For that, you can customize the bins for better distribution.

Step #5: Customize the bins

Right-click the horizontal axis and select Format Axis from the drop-down list that opens up.

The Format Axis pane will display on the right side of the screen.

Excel autogenerates the bin properties, but you can change each bin property to suit your needs.

In our example, we will change the Bin width to 3000:

To do that, select the radio button next to Bin width and change the 7000 to 3000.

As a result the Number of bins changes to 7. 

You can also change the Number of bins. The Bin width will then change at the same time. 

In our example, we changed the Overflow bin to 35000.

Values over 35000, will be stored in the overflow bin.

In our example, we changed the Underflow bin to 7000. Values less or equal to the number you specify are stored here. 

The chart automatically updates and reflects the changes made in the Format Axis pane.

You can switch to earlier values by clicking on the Reset button, to the right of Overflow bin and Underflow bin.

Follow the above-mentioned steps to create histograms. Once done,  you can change the labels, styles, and properties as required.


Method #2: Use the Data Analysis Menu

This method is best suited if you are using Excel 2007, 2010, and 2013, by making use of the Data Analysis tool. This method creates a static chart.

Step #1: Open an Excel sheet

Open a new MS Excel spreadsheet.

In the new sheet, create a table displaying student math scores and corresponding bins, like our example below. 

Create the bins in a separate column, set to intervals of 10. 

Set the first bin as 40, and the last bin as 90. 

Step #2: Load the Analysis Toolpak

If the Data Analysis tool is not visible on the Data tab, you need to load the Analysis Toolpak add-in. If you already have this tool, skip to Step #3.

From the main menu bar, click on File.

Click on Options.

In the Excel Options window, select Add-Ins on the left.

In the pane on the right, the Analysis Toolpak is listed under Inactive Application Add-ins.

Click on Analysis Toolpak.

Underneath this pane, from the drop-down next to Manage, select Excel Add-ins.

Click on the Go button.

In the Add-Ins dialog box, select the Analysis ToolPak check box.

Click on the OK button.

Step #3: Select Data Analysis

From the main menu, click on the Data tab.

In the Data ribbon, select Data Analysis

The Data Analysis window will display.

Select Histogram and click on the OK button.

Step #4: Select Histogram details

In the histogram window, select the Labels check box. This will help select the cell range.

To select the Input Range, click on the arrow in the block to the right of the Input Range field. 

The window will minimize, and you will see your spreadsheet.

Click on the first cell below your first heading, and while holding down the button, drag the cursor down the column until you reach the last entry. In our example, it is A2:A12.

Click on the same button on the Input Range for the histogram window to open again.

Do the same for Bin Range, by selecting cells B2:B7, as in our example.

Deselect the Labels check box to avoid the “number formatted as text” error.

Select the Output Range radio button. 

Click the arrow to the extreme right of the field next to the Output Range radio button.

The window will minimize, and you will see your spreadsheet.

Click on the first cell in Column D, where your data distribution table will then be placed. 

Click on the same button on Output Range for the histogram window to open again.

Select Chart Output to display the chart.

You can also create the chart in a new Workbook. To do so, click the New Workbook radio buttons.

Click on the OK button.

Excel creates a frequency distribution table in cells D2 to E9.

Notice the following:

  • Bins are on the X (Horizontal Category) Axis.
  • The Frequency of grouped values are on the Y (Vertical Value) Axis.
  • The More label indicates values greater than the last bin, that is 90.

Because this is a static chart, the chart links to the distribution table and not to the original data. 

If you change anything in the original data, it will not affect the chart.

To edit values in the chart, you must edit the cell values in the distribution table. 

For example, set the bin intervals to 5 and the last bin to 80. 

Observe the changes:

  • Excel creates a frequency distribution table in cells D2 to E12.
  • Bins on the X (Horizontal Category) Axis.
  • Frequency of grouped values on the Y (Vertical Value) Axis.
  • The More label shows values greater than the last bin, which is 80.

Method #3: Use the FREQUENCY Function

In this method, Excel creates a histogram by using the FREQUENCY function. This method is also dynamic. 

Step #1: Open an Excel Sheet

Open a new MS Excel spreadsheet.

In the new sheet, create a table displaying student math scores and corresponding bins, like our example below. 

Create the bins in a separate column, set to intervals of 10. 

Set the first bin as 40, and the last bin as 90. 

Step #2: Enter the FREQUENCY function

In Excel, the FREQUENCY function is an array formula, and the syntax is:

=FREQUENCY(data_array, bins_array)

Where,

  • data_array is the dataset to be evaluated, and
  • bins_array is an array or range used to group your data frequencies.

The FREQUENCY function returns a list of frequencies into each bin.

Select a range of cells next to Column B, where you want the result to display. 

Click with your mouse on Cell C2, and while holding down the button, drag your cursor down the column. 

Release the button when you have selected one extra cell. Since FREQUENCY is an array function, you must select this extra cell.

In our example, this selects the area C2:C8.

Place your cursor in the Formula Bar and start typing =FRE

As you type FRE in the Formula Bar Excel will display the function name and the tooltip, as shown below.

Double-click the FREQUENCY function that Excel has brought up. 

This will display the syntax as shown below.

Select the data range A2:A12 (data_array) with your cursor

Then separate it by typing comma(,) in the Formula Bar.

Select the bin range B2:B7 (bin_array) by clicking and dragging with your mouse. 

Add a bracket ) to close the formula.

Step #3: Submit the formula

After selecting the data_array, bin_array, and the output cells, press CTRL+SHIFT+ENTER to submit the formula.

Excel inserts curly brackets around the formula because it is an array formula.  

The number of elements in the returned array is one extra than the number of elements in the bins_array

The extra element in the returned array returns the count of any values above the highest interval. In this case above 90.

Enter a label >90 in B8.

Let us interpret the Frequency column:

  • No student scored less than 40.
  • 1 student scored between 40-50.
  • From C4 to C8, 2 students each scored between 50-60, 60-70, 70-80, 80-90, and above 90.

Step #4: Insert a chart

Select the Bins and Frequency list including the extra element, by clicking and dragging over them with your mouse.

In our example, that would be B2:C8.

On the main menu bar, click the Insert tab.

On the Insert ribbon, click the Insert Column or Bar Chart icon.

A dropdown menu will open.

Select 2-D Column.

Excel creates a Column chart with the FREQUENCY function and the selected bins. 

For the chart to look like a histogram, we should remove the space between the bins.

Select the columns by clicking on any of the columns

You will know the columns (and not the rows or chart) has been selected, by the blue circles appearing on the corners of the columns.

Click right, and from the menu that appears, select Format Data Series… 

In the Format Data Series pane that opens, set the Gap Width to 0 (zero).

To distinguish each of the bins, we should change the border color.

To do that, click the Fill and Line icon, which looks like a paint bucket, on the Format Data Series pane as illustrated below.

Click on the word Border for the dropdown to open

Select the Solid line radio button.

Set the Color to black by clicking on the pencil, and selecting the black block.

Set the line Width to 1pt.

Close the Format Data Series pane by clicking on the X in the corner.

Each bin is identified with a solid black border. 

Customizing a Histogram

After creating a histogram, customize it to make it easy to read and represent.

To add Chart Elements, click the green plus icon on the right of the chart or double-click on the chart.

The histogram above displays the chart elements selected. Add or remove chart elements by clicking or deselecting the checkbox against each element name. 

Click the  Paint Brush icon to edit the Chart Styles. Click the Style tab to scroll up or down and select a chart style from the dropdown list. 

To change chart styles, you can also select the chart and click the Chart Designs tab on the main menu, for the Chart Designs ribbon to open. Choose from the available styles.

Click the Colors tab to change the chart theme color as illustrated below.

To further customize these colors, click on ‘How do I change these colors?’ at the bottom of the color pane.

This will display the corresponding tooltip. 

Read the contents, and change the theme colors as desired. 

Click the filter icon to edit the Chart Filters. You can choose the data points and names to display on the chart.

Click the Values tab to select or deselect the values to display on the chart and click Apply.

Click Select Data, to Switch Row/Columns values, Series, and Labels, and click OK

All options are intuitive – play around with the options to reflect the changes in the chart.

Click the Names tab.

Click Select Data, to edit, add and remove the Legends, Axis Labels, and Switch Row/Columns

The options are intuitive – play around to check what best suits your needs. 


Conclusion

The next time you want to generate statistical data, you will know how. You should be comfortable creating a histogram using one of these three methods. Plus, you will be able to customize it to suit your requirements.

Do you have suggestions or queries? At OfficeBeginner we will be happy to assist and create more helpful tutorials.

Leave a Comment