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.

...
Histogram with bimodal distribution

Step

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.

...
Sample data
...
Calculating Min. Value

Step

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.

...
Bin value column

Step

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."

...
Histogram dialog box

Step

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.

...
Filled Histogram dialog box

Step

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."

...
Saw-toothed distribution

Step

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.