Saturday, January 28, 2012

How to Make a Histogram From a List of Data in Excel


1. Open a new Microsoft Excel 2010 spreadsheet. Select cell 'A1' and type in 'Input Range.' Select cell 'B1' and type in 'Bin Range.'
2. Click on cell 'A2' and type in the first value in your data set. Enter the rest of the data set into the cells down the first column.
3. Select cell 'B2' and enter your bins down the second column. Each actual bin number will stand for the top value in that bin's range. So, if you enter bins of '20,' '40' and '60,' the ranges will be anything below 20, 21 to 40, and 41 to 60, respectively. Excel will automatically add an additional bin called 'More,' which will collect every value higher than your highest bin number. The More bin will appear even if your highest bin is equal to or greater than your highest value. In addition, you have the option of not entering any bins and allowing Excel to automatically determine your bins.
4. Click the 'File' tab at the top of the screen and click 'Options' from the menu that appears on the left side of the Excel window. Click on 'Add-Ins' on the left edge of the Options window, then click the 'Go' button. Click the check box next to 'Analysis ToolPak' and click 'OK.' This will load the add-on that is required to create a histogram. You will only need to load this add-on once, as Excel will now load it automatically.
5. Select the 'Data' tab and click the 'Data Analysis' button. Select 'Histogram' and click 'OK.' The Histogram window will appear.
6. Click the radio button next to 'Output Range' and then click on cell 'C1.' Click the check box next to 'Chart Output.' This process will begin the histogram table at cell 'C1' and create the actual histogram to the right of the table on the spreadsheet.
7. Place your cursor into the 'Input Range' field and press 'Backspace' to erase whatever was in the box. Click and hold the mouse button over cell 'A1' on your worksheet, then drag the mouse down until you reach the last value in your data set and release the mouse button. If you entered your own bins on the worksheet, repeat this process with your bins in the 'Bin Range' field.
8. Click the check box next to 'Labels' as your data has labels in the first row, then click 'OK.' Excel will display your histogram.

Blogger news