Creating a Histogram in Excel

Have you ever had data in Excel from a survey or sales commissions? You can analyze them by displaying the data in a histogram. A histogram is used to show frequency within groups. Sometimes a histogram is called a Pareto, which is a sorted histogram. The resulting histogram or Pareto appears as a column chart. See Figure 1.

example histogram

Figure 1

You just need the list of data points and the ranges (or bin numbers) that show the interval for Excel to measure. If you would like, Excel can even choose your intervals.

Find the Data

Start with your raw data. For example, you can have a list of employees and their sales commissions, a list of grades or scores, or even percentages. Let’s say that you have certification exam scores for a team of employees. See Figure 2.

histogram data points

Figure 2

Create the Histogram Chart

Select your data. On the Insert tab ribbon, click the Insert Statistical Chart button. See Figure 3.

Insert Statistic Chart button

Figure 3

Choose the first Histogram option. See Figure 4. The second option allows you to have a line on the chart showing a cumulative percentage.

histogram button

Figure 4

Excel creates the chart with its best estimate for the bin intervals (even distribution between minimum and maximum values). See Figure 5. Most of the time, I have wanted to change these bin numbers.

auto-generated histogram

Figure 5

Format the Histogram Bins

To change the bin intervals that Excel auto-generated, right-click the horizontal axis (the axis along the bottom of the chart in our example) and choose Format Axis from the shortcut menu. See Figure 6.

Format Axis shortcut menu

Figure 6

The Format Axis pane opens on the right side of the Excel window. Axis Options is selected by default. See Figure 7.

Axis options pane

Figure 7

You can choose to change the bins according to Category, Bin Width, Number of bins, Overflow bin, or Underflow bin. Microsoft’s support site has a description for each of the options: (Figure 8)

Microsoft Excel axis options description

Figure 8

Use Data Analysis ToolPak

If one of the format options isn’t appropriate, you can do a frequency data analysis and then create the histogram. You need to add the Excel’s Analysis ToolPak Add-in. Go to the File tab, choose Options on the left, and then select the Add-ins category. See Figure 9.

Excel Add-ins dialog box

Figure 9

At the bottom of the dialog box, choose Excel Add-ins from the Manage drop-down list and click the Go button. Place a check in the Analysis ToolPak and click OK. See Figure 10.

Excel Analysis ToolPak add-in

Figure 10

Next, you want to enter the data to use for the bins. The number you enter should be the upper bound. See Figure 11.

Upper bounds bin numbers

Figure 11

Create the Histogram with Custom Bins

On the Data tab click the Data Analysis button. On the Data Analysis dialog box, choose Histogram. Then, click OK. See Figure 12.

Excel Data Analysis Histogram choice

Figure 12

On the Histogram dialog box, you click in the Input Range box and then select the cells for your raw data. The Bounds numbers in Column B go in the Bin Range box. Place a check in the Chart Output box and click the OK button. See Figure 13.

Excel Histogram dialog box with data

Figure 13

The histogram will appear on a new worksheet and will look like Figure 14.

Excel Histogram Chart

Figure 14

You may want to remove the More data since it is zero. Deleting the data in cells A7:B7 will not remove them from the chart. Click on the chart to select it. On the Chart Tools Design tab, click the Select Data button.

On the dialog box, scroll down on the right side and remove the check from the last box, which has the More data. See Figure 15. Then click OK.

Excel Select Data dialog box

Figure 15

Even though the More data is still on the worksheet, the chart doesn’t show it. See Figure 16.

Excel Revised Histogram chart

Figure 16

Additional Tips

Click on the chart to select it. Use the Histogram “Chart Tools” Design and Format tabs to adjust the look of your chart. Add a chart style or change colors on the Design tab. If you would like to delete the legend on the right side of the chart, you can click on it and hit the delete key.

Angela Nino - Versitas Editor

Leave a Reply