Create a Histogram in Excel

A histogram is a column chart that displays frequency data. In order to use the Histogram tool in Excel, you'll need to organize your data into two columns on the spreadsheet: one column for "input data," and the other for "bin numbers." Input data is the data that you want to analyze. Bin numbers represent the intervals that you want the Histogram tool to use for measuring and analyzing the input data.

Steps

Installing the Analysis ToolPak Add-In for Excel 2010 and 2013

Make sure that the add-in is installed. Before you can use the Histogram tool in Microsoft Excel, you'll need to make sure that the Analysis ToolPak Add-in is ready to use.[1]

  1. Navigate to the "Excel Add-ins" dialog box. You can do this from the main Home screen once you've opened the program.
    • Click Options on the File menu.
    • Then, click Add-Ins in the navigation pane.
    • In the Manage list, choose Excel Add-ins. Then, click Go.
  2. Select the Analysis ToolPak Add-in. Once you're in the Add-Ins dialog box, select the Analysis ToolPak check box under Add-Ins available, if it is not already selected, Then, click OK.
    • Note that the Analysis ToolPak Add-in will not appear in the Add-Ins dialog box if it is not already installed on your computer. If you do not see Analysis ToolPak in the Add-Ins dialog box, run Microsoft Excel Setup. Add the ToolPak to the list of installed items.

Installing the Analysis ToolPak Add-In for Excel 2007

  1. Navigate to the "Excel Add-ins" dialog box. This is where you can check whether the Analysis ToolPak is already installed on your computer.
    • From the Home screen, click the Microsoft Office button. Then, select Excel Options.
    • Click Add-Ins from the navigation pane.
    • Pick Excel Add-ins from the Manage list. Then, click Go.
  2. Select the ToolPak. In the Add-Ins dialog box, make sure that the Analysis ToolPak check box under Add-Ins available has been selected. Then, click OK. This should activate the Analysis ToolPak—and thus the Histogram function—on your computer.[2]

Creating the Histogram

  1. Enter your data. Organize your data into two adjacent columns on the spreadsheet. Fill the left-hand column with your "input data," and the right-hand with your "bin numbers."[3]
    • Input data is the set of numbers that you want to analyze with the Histogram tool.[4]
    • Bin numbers represent the intervals that you want the Histogram tool to use for measuring and analyzing the input data. For instance, if you want to separate grades into categories of A, B, C, D, and F, you could make bins for 60, 70, 80, 90, and 100.[5]
  2. Open the Data Analysis box. The process is consistent for all versions of Excel released since 2007. If you are using an earlier version of the software, then you will need to follow a slightly different process.
    • In Excel 2013, Excel 2010 and Excel 2007: navigate to the Data tab. Then, click Data Analysis in the Analysis group.
    • For Excel 2003 and earlier versions: select Data Analysis from the Tools menu. If there is not a Data Analysis option under the Tools menu, then you may need to install the add-in.
  3. Select "Histogram." Once you're in the Data Analysis dialog box, find Histogram listed amid the other analysis tools. Then, click OK. This will open the Histogram dialog box.
  4. Select the input range and the bin range. The input range is the range of cells that contain data. If your input data is a set of ten numbers, and you have copied it into the A column (from A1 to A10), then enter your data range as A1:A10. The bin range is the range of cells that contain your bin numbers. If you have four "bins" at the top of Column B, then enter your bin range as B1:B4.
  5. Check the chart output box. Under Output Options, click New Workbook. Then, select the Chart Output check box.
  6. Click OK to finish the job. Excel will generate a new workbook with a histogram table and an embedded chart. The chart should be a column chart that arranges the data from your histogram table.[6]

Sample Histograms

Doc:Histogram,Color Frequency Histogram,Shoe Size Histogram



Tips

  • By default the output is placed into a new worksheet, you can keep the output in the same worksheet by selecting the output range.
  • Note that a frequency table is also created.
  • Check out the website to download a simple template to create histograms.

Warnings

  • Make sure your histogram makes sense before drawing any conclusion from it.
  • Be careful to understand what the bins mean. The bin range should not be confused with the data.

Related Articles

Sources and Citations

  1. ↑ https://support.microsoft.com/en-us/kb/214269
  2. ↑ http://www.excel-easy.com/examples/histogram.html
  3. ↑ https://support.office.com/en-us/article/Create-a-histogram-B6814E9E-5860-4113-BA51-E3A1B9EE1BBE
  4. ↑ http://www.math.kent.edu/~honli/teaching/statistics/Chapter2/Excell_Histogram.html
  5. ↑ https://www.ncsu.edu/labwrite/res/gt/gt-bar-home.html
  6. ↑ https://support.microsoft.com/en-us/kb/214029
Retrieved from "https://kipkis.com/index.php?title=Create_a_Histogram_in_Excel&oldid=352862"