Data analysis in Microsoft Excel

As we have seen in class, Excel is capable of performing a variety of statistical analyses. These can be accessed under the “Tools” pull-down menu, within “Data Analysis”. Some of the capabilities we have explored so far include:

Descriptive Statistics
Identify the range of data for which you would like statistics in the Input Range.
Specify how the data is arranged in your spreadsheet - whether the data for one variable is grouped within the same column or the same row.
If you have included labels to describe your data in the first row/column, check the box to indicate this (it is a good idea to do this, since it makes the results easier to read, particularly when you are analyzing several variables simultaneously).
Specify where you would like the output to appear (on the same worksheet, on a new worksheet, in a new workbook).
Indicate which statistics you would like calculated. “Summary statistics” will provide most of the basic statistics you are interested in (mean, median, mode, variance, standard deviation, range, minimum, maximum, sum, count).

Histogram
Identify the range of data for which you would like a frequency table and/or a histogram in the Input Range.
If desired, identify the list of end points of the intervals you would like to use (in ascending order) in the Bin Range. Excel will choose equally spaced intervals if you do not specify any.
Specify where you would like the output to appear (on the same worksheet, on a new worksheet, in a new workbook).
Select Chart Output if you would like a histogram in addition to a frequency table.

Correlation
Identify the range of data for which you would like correlation statistics in the Input Range (at least 2 variables).
Specify how the data is arranged in your spreadsheet - whether the data for one variable is grouped within the same column or the same row.
If you have included labels to describe your data in the first row/column, check the box to indicate this (it is a good idea to do this, since it makes the results easier to read, particularly when you are analyzing several variables simultaneously).
Specify where you would like the output to appear (on the same worksheet, on a new worksheet, in a new workbook).
The output will appear as a matrix, with one row and one column for each variable.

Further details about these (or any other) Excel functions are available through the Help function in Excel.