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.
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.
Create the Histogram Chart
Select your data. On the Insert tab ribbon, click the Insert Statistical Chart button. See 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.
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.
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.
The Format Axis pane opens on the right side of the Excel window. Axis Options is selected by default. See 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)
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.
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.
Next, you want to enter the data to use for the bins. The number you enter should be the upper bound. See 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.
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.
The histogram will appear on a new worksheet and will look like 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.
Even though the More data is still on the worksheet, the chart doesn’t show it. See Figure 16.
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.