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:
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.
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.
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.
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.
Select chart data range: select the frequency values only
before starting the Chart Wizard.
Click the Chart Wizard button.
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
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.
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.
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.
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
Double click on one of the bars to bring up the Format Data
Series dialog. Select the Options tab and reduce the Gap
Right click on the Chart area or Plot area to bring up the
Format dialog that allows colour selection.
The adjacent plot illustrates the sort of outcome you might expect.