How To: Prepare an Excel Frequency Chart School of GeoSciences


How To: Prepare an Excel Frequency Chart

What is a Frequency Chart?

A frequency chart, or histogram, shows the number or per-centage of items falling within particular ranges of values. For example, students examination results are often displayed as a histogram showing the per-centages obtaining grades A to G.

Excel provides a Frequency function that can be used to produce data for a histogram. It also has a Histogram data analysis tool that is a little more difficult to use.

The Geology 3 assignment uses a set of 360 elevation values from a slice through the Earth. Assuming you have your data loaded into Excel columns proceed as follows:

  1. Find the maximum and minimum values of the data: click on any blank cell and click the Paste Function button. Select the MAX function from the Statistical Function Category. In the Number 1 box enter the range of the elevation data, typically B2:B361. OK. Repeat using the MIN function.

  2. Bin values: in a blank cell type a suitable minimum bin value. For example,if your MIN value is -5346 and you are proposing to use bin intervals of 1000 then type -6000. In the cells below increment the lowest bin value by the bin interval until you reach the bin value above your MAX value. The bin values might be -6000, -5000, -4000, etc., 2000, 3000, 4000 in a column of cells.

  3. Chart Wizard: Step 3 of Bar Chart
  4. Frequency function: select the blank cells to the right of the cells containing the bin values. Click the Paste Function button. Select the Frequency function from the Statistical Function Category. In the Data_array box type the cell range of the elevation data, typically B2:B362. In the Bins_array box type the cell range of the bins data, might be C2:C12. Hold down the Shift AND Ctrl keys and click on OK.

  5. Example Histogram Data
  6. Bin value labels: the Frequency function returns an array of frequency data. However, the bin values are misleading as labels. For example, there might be 81 elevation values alongside the -5000 bin value; so does this mean that there are 81 values between -6000 and -5000 or between -5000 and -4000, or something else? You can always work this out by knowing that the first bin above zero contains the number of values between 0 and the bin value.

    Alongside the frequency values type in some suitable unambiguous labels, such as '-6 to -5. Note: text items that begin with - or = symbols must be preceded by a ' symbol.

  7. Select chart data range: select the frequency values only before starting the Chart Wizard.

  8. Click the Chart Wizard button.

  9. Chart Wizard - Step 1: Select the Column Chart Type from the list of Standard Types. Select the Clustered Column Chart sub-type by clicking the top left illustration. Next.

  10. Chart Wizard - Step 2: Click the Data Range tab select the data range of the frequency values. Click the Series tab and select the Category (X) axis labels: by first clicking the icon to the right of the field, causing the dialog to shrink out of the way. Click on the top label cell, hold down the Shift key and click on the bottom label cell. Expand the dialog again. Next.

  11. Bar Chart Wizard
  12. Chart Wizard - Step 3: In the Titles dialog add a suitable Chart title and X and Y axis Titles. Select the Legend tab and uncheck the Show Legend checkbox. Next.

  13. Chart Wizard - Step 4: Finish. The Chart my be close to illegible at this stage. Try closing any split windows and making the chart bigger.

  14. Optimise the chart appearance:

    • Double click on the X axis running horizontally at the bottom of the Plot Area. The Format Axis dialog should appear. Try altering the Number Alignment too see if the scale labels can be tidied.

    • Double click on one of the bars to bring up the Format Data Series dialog. Select the Options tab and reduce the Gap width. OK.

    • Right click on the Chart area or Plot area to bring up the Format dialog that allows colour selection.

Hypsometry histogram

The adjacent plot illustrates the sort of outcome you might expect.