Histograms in EXCEL



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):

After (Using only above instructions):

-----------------------

[pic]

[pic]

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download