How to Construct a Histogram Using Excel 2007
Histograms are used in Excel to exhibit a visual representation of data distribution. They can be a powerful tool in the interpretation of long-term trends and tendencies of a specific set of data. In constructing a histogram in Excel, you must refer to a list of numerical data as well as a list of bin numbers. The numerical data can be any quantitative item that can be measured numerically such as test scores, customer-satisfaction scores or stock prices. The bin numbers are a list of intervals you provide to specify the numerical distribution in the histogram.
Things You'll Need
- MS Excel 2007
- Example quantitative data
Capture the stock data. Go to Yahoo Finance and type "SBUX" in the symbol box. Click on the link that says "historical prices". Go to the bottom of the page and click "download to spreadsheet". The info will download automatically and then open in an Excel spreadsheet.
Get the Max and Min values of the "Adj Close" column. Go to the editing option of the Home menu. In the drop-down box of mathematical functions select "Max." Type "G2:G501" and press "Enter." Repeat the same with "Min." The Max value should be 28.29 and the Min value should be 7.17.
Create the bin values. Round 28.29 to 30 and 7.17 down to 5. Start at 5 and add increments of 1 until you get to 30. Type those numbers in the column next to "Adj Close" and call it "Bin values."
Open the histogram dialog box. Under the "Data" tab select "Data Analysis". In the dialog box select "Histogram" and press "OK." The Histogram dialog box will then open.
Input the histogram setting. In the "Input Range" field type "$G$2:$G$501" and in the "Bin Range" field type "$H$2:$H$25". These are the cell ranges of the data and bin values respectively. In "Output Options" select "New Worksheet Ply" and name the worksheet "Histogram." Then select chart output and press "OK."
Survey the Histogram. Observe the distributions of the lines. If they bundled in the middle the histogram is said to be "evenly distributed" or "bi modal." This is usually the case with test or survey data. As is sometimes expected with erratic stock data the histogram in this article turned out to be "saw-toothed." This kind of distribution might indicate that the stock under inspection has been quite volatile over the past 500 days.
Tips & Warnings
- This demonstrates how to build a histogram in Excel that represents the 500-day range of closing stock prices for Starbucks Coffee (SBUX).
- Your copy of Excel may not have a Data Analysis option. If this is the case go to the Excel drop down menu(round button) and select "Excel Options" at the bottom. In the left column select "Add-ins" and then select Excel Add-in in the Manage drop menu. Then press "Go". This will install the appropriate add-ins to create histograms.