How to Create a Histogram in Excel

Inputting Your Data

1. Open Microsoft Excel. Its app icon resembles a white "X" on a green background. You should see the Excel workbook page open.

   1. On a Mac, this step may open a new, blank Excel sheet. If so, skip the next step.

2. Create a new document. Click Blank workbook in the upper-left corner of the window (Windows), or click File and then click New Workbook (Mac).

3. Determine both your smallest and your largest data points. This is important in helping figure out what your bin numbers should be and how many you should have.

   1. For example, if your data range stretches from 17 to 225, your smallest data point would be 17 and the largest would be 225.

4. Determine how many bin numbers you should have. Bin numbers are what sort your data into groups in the histogram. The easiest way to come up with bin numbers is by dividing your largest data point (e.g., 225) by the number of points of data in your chart (e.g., 10) and then rounding up or down to the nearest whole number, though you rarely want to have more than 20 or less than 10 numbers. You can use a formula to help if you're stuck:^{[1] X Research source}

   1. Sturge's Rule - The formula for this rule is K = 1 + 3.322 * log(N) where K is the number of bin numbers and N is the number of data points; once you solve for K, you'll round up or down to the nearest whole number. Sturge's Rule is best used for linear or "clean" sets of data.
   2. Rice's Rule - The formula for this rule is cube root (number of data points) * 2 (for a data set with 200 points, you would find the cube root of 200 and then multiply that number by 2). This formula is best used for erratic or inconsistent data.

5. Determine your bin numbers. Now that you know how many bin numbers you have, it's up to you to figure out the most even distribution. Bin numbers should increase in a linear fashion while including both the lowest and the highest data points.

   1. For example, if you were creating bin numbers for a histogram documenting test scores, you would most likely want to use increments of 10 to represent the different grading brackets. (e.g., 59, 69, 79, 89, 99).
   2. Increasing in sets of 10s, 20s, or even 100s is fairly standard for bin numbers.
   3. If you have extreme outliers, you can either leave them out of your bin number range or tailor your bin number range to be low/high enough to include them.

6. Add your data in column A. Type each data point into its own cell in column A.

   1. For example, if you have 40 pieces of data, you would add each piece to cells A1 through A40, respectively.

7. Add your bin numbers in column C if you're on a Mac. Starting in cell C1 and working down, type in each of your bin numbers. Once you've completed this step, you can proceed with actually creating the histogram.

   1. You'll skip this step on a Windows computer.