How to Create a Histogram in Excel
This wikiHow teaches you how to create a histogram bar chart in Microsoft Excel. A histogram is a column chart that displays frequency data, allowing you to measure things like the number of people who scored within a certain percentage on a test.
Inputting Your Data
Open 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.
Create a new document.ClickBlank workbookin the upper-left corner of the window (Windows), or clickFileand then clickNew Workbook(Mac).
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.
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:
- Sturge's Rule- The formula for this rule isK = 1 + 3.322 * log(N)whereKis the number of bin numbers andNis 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 iscube 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.
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.
Add your data in column A.Type each data point into its own cell in columnA.
- For example, if you have 40 pieces of data, you would add each piece to cellsA1throughA40, respectively.
Add your bin numbers in column C if you're on a Mac.Starting in cellC1and 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.
Creating the Histogram on Windows
Select your data.Click the top cell in columnA, then hold downShiftwhile clicking the last columnAcell that contains data.
Click theInserttab.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 theInsertmenu.
ClickRecommended Charts.You'll find this option in the "Charts" section of theInserttoolbar. A pop-up window will appear.
Click theAll Chartstab.It's at the top of the pop-up window.
ClickHistogram.This tab is on the left side of the window.
Select the Histogram model.Click the left-most bar chart icon to select the Histogram model (rather than the Pareto model), then clickOK. Doing so will create a simple histogram with your selected data.
Open the horizontal axis menu.Right-click the horizontal axis (e.g., the axis with numbers in brackets), clickFormat 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.
Check the "Bin width" box.It's in the middle of the menu.
Enter your bin number interval.Type into the "Bin width" text box the value of an individual bin number, then pressEnter. 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 in10here.
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 anAxis Titletext box on the left or the bottom of the graph, and type in your preferred title.
- Chart Title- Click theChart Titletext box at the top of the histogram, then type in the title that you want to use.
Save your histogram.PressCtrl+S, select a save location, enter a name, and clickSave.
Creating the Histogram on Mac
Select your data and the bins.Click the top value in cellAto select it, then hold downShiftwhile clicking theCcell that's across from the bottom-mostAcell that has a value in it. This will highlight all of your data and the corresponding bin numbers.
ClickInsert.It's a tab in the green Excel ribbon at the top of the window.
Click the bar chart icon.You'll find this in the "Charts" section of theInserttoolbar. Doing so will prompt a drop-down menu.
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.
Review your histogram.Before saving, make sure that your histogram looks accurate; if not, consider adjusting the bin numbers and redoing the histogram.
Save your work.PressCommand+S, enter a name, select a save location if necessary, and clickSave.
QuestionThe histograms I'm trying to represent have different class widths represented as different width blocks, and they are not replicating. How do I get different class widths?wikiHow ContributorCommunity AnswerYou need to go to Setting > Option >Class and then choose your class.Thanks!
How to move the individual task up and down?
How do I create a stacked histogram?
What does it mean when a message says, histogram input range contains non numeric data?
How do I adjust the number of bins? I have tried formating horizontal axis information.
How do I determine the bins?
- Bins can be as wide or as narrow as you think they should be, so long as they both fit the data and don't exceed the reasonable number of bins for your data set.
- Make sure your histogram makes sense before drawing any conclusion from it.
Sources and Citations
In other languages:
Español: , Português: , Deutsch: , Italiano: , Русский: , Français: , 中文: , Nederlands: , Bahasa Indonesia: , ไทย: , 日本語: , العربية:
Video: Creating a Histogram in Microsoft Excel 2016
Why Prince William wasn’t happy about this gift for his son Prince George
The Best Retinol Beauty Buys To Anti-Age Your Skin
Find The Best Makeup Shades For Your HairColor
Staying In Town Over Memorial Day Give Yourself a SpaTreatment
How to Wear a Fanny Pack—and 15 Amazing Ones to ShopNow
The Workout That Tells Your Office Job to Suck It
Liquid Liner 101: All the Tips and Tricks You Need for PerfectApplication
20 Effective Ways To Lower Blood Pressure
In WTF news Should you wear a badge to show strangers you’re single
How to Be a Good Friend to Both Parties in a Divorce
The Best Yoga Poses for You