Histograms in EXCEL
Get data set in EXCEL – Variable of interest should be in a given
column (say, column D with a header row containing a label)
Give the set of bin upper points you want for the histogram in another
column (say, column G with a header row containing label)
Place cursor in data area, select Tools -> Data Analysis -> Histogram.
Fill in the Input Range with the cells of the raw data (say D1:D541 for a
dataset with 540 observations and a header label)
Fill in the Bin Range with the cells giving the upper points of the
ranges (say G1:G13 for a histogram with 12 bins and a header label)
Click on Labels (Don’t if there is no header row with labels)
Click on New Worksheet Ply and Chart Output (then OK)
This will give a crude plot that is easy to clean up
Click on Chart and stretch it out
Right click on a bar (where it says FREQUENCY) and
select FORMAT DATA SERIES -> OPTIONS and reduce
gap width to 0
Can also vary colors by point (technically RANGE) by
clicking on that, then Click OK
Further experimentation leads to improvements (e.g.
background color by Right clicking on blank part of
graph and selecting Format Plot Area
Before (Original Output):
Histogram
Frequency
200
100 Frequency
0
.
.
.
.
.
.
.
rain100
Histogram
140 100
120 200
300
100
Frequency
400
80 500
60 600
40 700
800
20
900
0 1000
1100
e
0
0
0
0
0
00
10
30
50
70
90
or
11
M
1200
rain100 More
After (Using only above instructions):