MATH 1070 — EXCEL HISTOGRAM
First make sure that the “add-ins” for “Data Analysis” are available on your computer; click on “Tools”; if
“Data Analysis” is not on the menu, then click on “Add-Ins”; then check “Analysis ToolPak” and
“Analysis ToolPak VBA”; then click “OK”.
[If the add-ins are not available on your computer, you can
use a bar graph and turn it into a histogram, but you need to do the frequency table yourself.]
Make sure that your data values are in Column A.
Their location will go in the “Input Range”position.
Decide on the intervals that you want to use in your frequency table.
(The “default” choice of intervals
usually produces a poor result.)
Excel uses the following convention for the interval definitions (e.g. for
data in the range of 22 – 36):
30 < x
35 < x
(here, 25, 30, 35, and 40) in another column next to your data (e.g.,
Their location will go in the “Bin Range” position.
Go to the “Tools” menu.
Click on “Tools”; “Data Analysis”; “Histogram” .
Click on the “Input Range” position (space).
Then highlight (select) the data values in Column A.
Click on the “Bin Range” position.
Then highlight the right-hand endpoints in Column C.
Click on the “Output Range” button.
Then click in the “Output Range” position.
somewhere on the worksheet.
That will be the upper left-hand corner of your output (which will
consist of a frequency table and the histogram graph).
[If you don’t do this, your output will be
on a separate page.]
You can move these items around later if you wish.
Check the “Chart Output” option.
Click on “OK”, and your output should appear.
Now you need to “edit” your results, so that they look like an appropriate frequency table and histogram, as
Change the label on the frequency table from “Bin” to a more appropriate label.
Click on the chart (graph).
An outline with “handles” should appear.
Use the handles to make the
If you don’t want the “legend” on the right side of the graph, right-click on it and then click “clear” to
Right-click on one of the bars, then click on “Format Data Series”, then click on the “Options” tab,
then change the “Gap Width” to zero (0).
Still in the “Format Data Series” panel, you can now change the label on the horizontal axis from
“Bin” to something more appropriate.
You can also change the font size for both axes to make
the graph look better. Other editing options are also available.
If you don’t care about the blue and gray colors, you can also change them to white, and the
histogram will print out faster.
To change the blue bars, right-click on a bar, then click on
“Format Data Series”, then click on the “Patterns” tab, then click on the desired color.
gray area, right-click on the gray area, then click on “Format Plot Area”, then “Patterns”, then
click on the desired color.