This September 1999 help sheet gives information on how to

• create histograms in Excel
• variations on a histogram

DATA

We read in the following data on house sale prices in Central Davis in 1999, measured in thousands of dollars.
375, 340, 310, 279.9, 278.5, 273, 272, 270, 270, 258.5, 255, 253, 249, 245, 244, 241, 239.5, 238, 236.5, 235, 235, 233, 230, 229, 224.5, 220, 213, 212, 204.
[One way to get this data into column format is to cut the data into a text editor and manually type in enter after each observation and delete the comma].

HISTOGRAM

There are 25 observations. Interpretation of the data is made somewhat easier by the data already being ordered (if it is not ordered we can order it using Data | Sort). But it is useful to collapse the data further.

A histogram does this by counting the number of observations that fall within a certain range (a "bin") and then plotting this frequency against the bin value.

In Excel choose Tools | Data Analysis | histogram and hit the histogram key.

• For input range either type in by hand the input range (e.g. A1:A30 where A1 is a label such as Sales Price) or select the data range on the spreadsheet by clicking on the first entry (the label) and then dragging the mouse down and de-clicking at the last data entry.
• For bin range either put in nothing, in which case Excel will choose the bin range, or better still give your own bin range, explained further below.
• For labels click if you have provided a label for the data (always good practice).
• In output range click and give a cell which has at least, say, 10 free cells to the right and 15 free cells below it). If the data are in column A a good choice might be, say, cell C3. Alternatively you can put the chart on a new worksheet but then it is more difficult to keep track of where the chart originated from.
• Select Chart output (but not Pareto or cumulative percentage).

For these data the number of intervals seems reasonable but the cell boundaries are not nice rounded numbers. For this particular data rounding off to the nearest $50,000 seems natural, so we might choose the interval boundaries as 200, 250, 300, 350 and 400. This choice can be done by creating a bin. In, say, columns B1:B6 type, respectively Sales Price, 200, 250, 300, 350, 400. (The entry in B1 will be used to automatically label the x-axis of the histogram).

In Excel choose Tools | Data Analysis | histogram and hit the histogram key.

• Provide the same outputs as earlier, which Excel should automatically display, change the output range to a new value if you want to keep the first histogram, and provide the Bin range by either type in by hand the bin range (e.g. B1:B6 where B1 is a label such as Sales Price) or select the data range on the spreadsheet by clicking on the first entry (the label) and then dragging the mouse down and de-clicking at the last bin entry.

• Provide a clear histogram title such as House Sale Prices in Central Davis 1999 which is input by clicking on the chart title area and changing the text.
• Delete the Frequency Legend Entry on the right-hand side (here frequency) by clicking on it and hitting the delete key.

You can move the chart within the worksheet by clicking on it, leaving the mouse depressed, and drag the mouse.
If you need to enlarge the chart size, then resize the entire chart by putting mouse icon on bottom right hand corner of chart and dragging down to appropriate size.
 

VARIATIONS ON A HISTOGRAM

Variations on a histogram are

• Select Cumulative percentage to have an additional line added to the chart which shows the cumulative frequency distribution, with scale given on the right-hand side.
• Select Pareto (sorted histogram) to order the histogram categories by the frequency of occurrence. This is useful for categorical data but less useful for numerical data which are already naturally ordered.
• Select both Pareto (sorted histogram) and Cumulative percentage to get a histogram sorted by frequencuy of occurrence and the associated cumulative frequency distribution.

Sample output is given below. I stored the data and labels in A3:A32 as I used the first two rows for data description and I stored the bin in B24:B29.

A final point is interpretation. Excel labels the histogram by using the upper value in the interval. Thus the frequency of 17 labeled under 250 means that there were 17 observations in the sample that took values greater than 200 and less than or equal to 250. It would be clearer to label this interval using its midpoint of 225, and this is done by some other programs.

For further information on how to use Excel
   go to http://www.econ.ucdavis.edu/faculty/cameron