On the Data Analysis dialog box, choose Histogram. On the Data tab click the Data Analysis button. See Figure 11.įigure 11 Create the Histogram with Custom Bins

The number you enter should be the upper bound. Next, you want to enter the data to use for the bins. Place a check in the Analysis ToolPak and click OK. See Figure 9.Īt the bottom of the dialog box, choose Excel Add-ins from the Manage drop-down list and click the Go button. Go to the File tab, choose Options on the left, and then select the Add-ins category. You need to add the Excel’s Analysis ToolPak Add-in. If one of the format options isn’t appropriate, you can do a frequency data analysis and then create the histogram. Microsoft’s support site has a description for each of the options: (Figure 8) You can choose to change the bins according to Category, Bin Width, Number of bins, Overflow bin, or Underflow bin. The Format Axis pane opens on the right side of the Excel window. 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. Most of the time, I have wanted to change these bin numbers.

The second option allows you to have a line on the chart showing a cumulative percentage.Įxcel creates the chart with its best estimate for the bin intervals (even distribution between minimum and maximum values). See Figure 3.Ĭhoose the first Histogram option. On the Insert tab ribbon, click the Insert Statistical Chart button. Let’s say that you have certification exam scores for a team of employees. For example, you can have a list of employees and their sales commissions, a list of grades or scores, or even percentages. If you would like, Excel can even choose your intervals. You just need the list of data points and the ranges (or bin numbers) that show the interval for Excel to measure. The resulting histogram or Pareto appears as a column chart.

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

Cumulative frequency histogram excel