How to Create Histogram in Excel

How to Create Histogram in Excel – Excel is a very popular tool, and it is the best choice if you want to make a histogram in Excel. It is the best choice because it has many options to create such as histogram from table, histogram from frequency table, and many other options. If you want to know more about how to create a histogram in excel 2020, you should read this article carefully.

How to Create Histogram in Excel – A histogram is a chart that shows the frequency distribution of a set of values. The frequency distribution of these values are arranged into specified ranges known as bins. 

Examples of using a histogram include grouping performance scores into ranges, grouping values into ranges of years, or survey responses grouped into age brackets. A histogram is a type of data visualization.

How to Create Histogram in Excel

Part 1: Inputting Your Data

1. 1Open Microsoft Excel. Its app icon resembles a white “X” on a green background. You should see the Excel workbook page open.
   • On a Mac, this step may open a new, blank Excel sheet. If so, skip the next step.
2. 2Create 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.
   • 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]
   • 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.
   • 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.
   • 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).
   • Increasing in sets of 10s, 20s, or even 100s is fairly standard for bin numbers.
   • 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.
   • 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.
   • You’ll skip this step on a Windows computer.

Part 2: Creating the Histogram on Windows

1. Select your data. Click the top cell in column A, then hold down ⇧ Shift while clicking the last column A cell that contains data.
2. Click the Insert tab. It’s in the green ribbon that’s at the top of the Excel window. Doing so switches the toolbar near the top of the window to reflect the Insert menu.
3. 3Click Recommended Charts. You’ll find this option in the “Charts” section of the Insert toolbar. A pop-up window will appear.
4. Click the All Charts tab. It’s at the top of the pop-up window.
5. Click Histogram. This tab is on the left side of the window.
6. Select the Histogram model. Click the left-most bar chart icon to select the Histogram model (rather than the Pareto model), then click OK. Doing so will create a simple histogram with your selected data.
7. Open the horizontal axis menu. Right-click the horizontal axis (e.g., the axis with numbers in brackets), click Format Axis… in the resulting drop-down menu, and click the bar chart icon in the “Format Axis” menu that appears on the right side of the window.
8. Check the “Bin width” box. It’s in the middle of the menu.^[2]
9. Enter your bin number interval. Type into the “Bin width” text box the value of an individual bin number, then press ↵ Enter. Excel will automatically format the histogram to display the appropriate number of columns based on your bin number.
   • For example, if you decided to use bins that increase by 10, you would type in 10 here.
10. Label your graph. This is only necessary if you want to add titles to your graph’s axes or the graph as a whole:
    • Axis Titles – Click the green ＋ to the right of the graph, check the “Axis Titles” box, click an Axis Title text box on the left or the bottom of the graph, and type in your preferred title.
    • Chart Title – Click the Chart Title text box at the top of the histogram, then type in the title that you want to use.
11. Save your histogram. Press Ctrl+S, select a save location, enter a name, and click Save.

Part 3: Creating the Histogram on Mac

1. Select your data and the bins. Click the top value in cell A to select it, then hold down ⇧ Shift while clicking the C cell that’s across from the bottom-most A cell that has a value in it. This will highlight all of your data and the corresponding bin numbers.
2. Click Insert. It’s a tab in the green Excel ribbon at the top of the window.
3. Click the bar chart icon. You’ll find this in the “Charts” section of the Insert toolbar. Doing so will prompt a drop-down menu.
4. Click the “Histogram” icon. It’s the set of blue columns below the “Histogram” heading. This will create a histogram with your data and bin numbers.
   • Be sure not to click the “Pareto” icon, which resembles blue columns with an orange line.
5. Review your histogram. Before saving, make sure that your histogram looks accurate; if not, consider adjusting the bin numbers and redoing the histogram.
6. Save your work. Press ⌘ Command+S, enter a name, select a save location if necessary, and click Save.

Formatting a Histogram Chart

Once you’ve inserted a histogram into your Microsoft Excel worksheet, you can make changes to it by right-clicking your chart axis labels and pressing the “Format Axis” option.

Excel will attempt to determine the bins (groupings) to use for your chart, but you might need to change this yourself. For instance, for a list of student test results out of 100, you might prefer to group the results into grade boundaries that appear in groups of 10.

You can leave Excel’s bin grouping choice by leaving the “By Category” option intact under the “Format Axis” menu that appears on the right. If you want to change these settings, however, switch to another option.

For instance, “By Category” will use the first category in your data range to group data. For a list of student test results, this would separate each result by student, which wouldn’t be as useful for this kind of analysis.

Using the “Bin Width” option, you can combine your data into different groups.

Referring to our example of student test results, you could group these into groups of 10 by setting the “Bin Width” value to 10.

The bottom axis ranges start with the lowest number. The first bin grouping, for instance, is displayed as “[27, 37]” while the largest range ends with “[97, 107],” despite the maximum test result figure remaining 100.

The “Number Of Bins” option can work in a similar way by setting a firm number of bins to show on your chart. Setting 10 bins here, for instance, would also group results into groups of 10.

For our example, the lowest result is 27, so the first bin starts with 27. The highest number in that range is 34, so the axis label for that bin is displayed as “27, 34.” This ensures as equal distribution of bin groupings as possible.

For the student results example, this may not be the best option. If you want to ensure that a set number of bin groupings are always displayed, however, this is the option you’d need to use.

You can also split data into two with overflow and underflow bins. For instance, if you wanted to carefully analyze data under or above a certain number, you could tick to enable the “Overflow Bin” option and set a figure accordingly.

For example, if you wanted to analyze student pass rates below 50, you could enable and set the “Overflow Bin” figure at 50. Bin ranges below 50 would still be displayed, but data over 50 would be grouped in the appropriate overflow bin instead.

This works in combination with other bin grouping formats, such as by bin width.

The same works the other way for underflow bins.

For instance, if a failure rate is 50, you could decide to set the “Underflow Bin” option to 50. Other bin groupings would display as normal, but data below 50 would be grouped in the appropriate underflow bin section.

You can also make cosmetic changes to your histogram chart, including replacing the title and axis labels, by double-clicking those areas. Further changes to the text and bar colors and options can be made by right-clicking the chart itself and selecting the “Format Chart Area” option.

Standard options for formatting your chart, including changing the border and bar fill options, will appear in the “Format Chart Area” menu on the right.

Conclusion

Histograms are used to represent data which is normally distributed. They are generally used on continuous data and on how it’s spread out over different values. A histogram can help you determine if your data is normally distributed or not.