GUIDE TO MICROSOFT? EXCEL’S
DATA ANALYSIS TOOLS
1. What Are the Data Analysis Tools? Excel includes a collection of 19 data analysis tools. These provide analysis capabilities beyond Excel’s inherent capabilities. One of them provides
Fourier analysis for engineering applications. The remaining 18 provide statistical analyses. A discussion of the differences between these 18 statistical analysis tools and Excel’s 80 built-in
statistical functions begins on the first page of guide to Excel’s Statistical Functions.
2. Where Can I Find the Data Analysis Tools? The data analysis tools are available in the
component called the Analysis ToolPak that is an add-in program. Add-in programs are
developed by organizations other than Microsoft and can be added to Excel to increase its capabilities. Microsoft includes a number of add-ins, such as the Analysis ToolPak, with the Excel software it distributes. These must be added in to Excel before they can be used within it.
3. Are the Data Analysis Tools Available on the Computer I am Using? The level of
availability of the data analysis tools depends on the specific computer you are using. There are three possibilities.
a. The first possibility is that the Analysis ToolPak (1) has been installed and (2) has been
added to the Tools pull-down menu. This means the data analysis tools are ready to use. To determine if this is your situation, click on Tools from the menu bar. If there is an entry
Excel’s Data Analysis Tools 2
usually near the bottom of the subsequent pull-down menu labeled Data Analysis…, the tools
are ready to use. If it is not present, continue to the next possibility.
b. The second possibility is that the Analysis ToolPak (1) has been installed but (2) has
not been added to the Tools menu. You must then add it to the menu. To determine if this is your situation and to add it to the menu, click on Tools from the menu bar. Then click on
Add-ins… from the Tools pull-down menu. If the menu includes the selection Analysis
ToolPak in the subsequent dialog box, you should click on the check box to its left to add a check mark to the box. (Note you are to add Analysis ToolPak, not Analysis ToolPak-
VBA.) Next click on OK and the Analysis ToolPak will be added to the Tools menu and
ready to use. If Analysis ToolPak is not an option on the add-ins dialog box, continue to the third possibility. Usually this will not be true if you are using Excel 2000 but could be if you
are using Excel 97.
c. The third possibility is that the Analysis ToolPak (1) has not been installed and (2) has
not been added. You must then first install it and second add it to the Tools menu. If this is your situation, you will need to exit from Excel and all other application programs and return to your computer’s opening screen. Get your original diskettes or CD-ROM’s for Microsoft
Office (or just Excel) and run the Setup program for it. Put the first CD or first diskette for your Microsoft Office software in the appropriate drive.
(1) Click on the Start command button and click on Run from the subsequent menu.
(2) The Run dialog box will appear on the screen. Assuming your CD or diskette is in drive D enter D:\SETUP in the text box and click on OK. (You may next get a dialog box indicating
the Shortcut Bar is running. If so, click on OK.)
(3) The next dialog box will be labeled the Microsoft Office—Set Up. Click on the
Add/Remove command button.
(4) The next dialog box is labeled Microsoft Office—Maintenance. Click to put a check
mark in the Excel checkbox and to highlight Excel. Click on the Change Option button.
(5) The next dialog box is labeled Microsoft Office—Microsoft Excel. Click on the Add Ins
button. Click on Change Option.
(6) The next dialog box is labeled Microsoft Office—Add-Ins. Click to put a check mark in
the Analysis ToolPak (not Analysis ToolPak—VBA) check box and to highlight Analysis
ToolPak. Click on OK.
The installation program will now install the Analysis ToolPak. You can then start Excel. You will next need to add it to the Tools menu. Select Tools, then Add-Ins and put a check
Excel’s Data Analysis Tools 3
mark in the Analysis ToolPak check box and select OK. The Data Analysis Tools will now
be available from the Tools pull-down menu.
4. How Do I Use the Data Analysis Tools? Once you have the Analysis ToolPak installed
and it has been added to the Tools pull-down menu, you are ready to use these analysis tools.
First click on Tools from the menu bar and then click on Data Analysis on the subsequent pull-
down menu. The result will be the Data Analysis dialog box with a scrolling list of the 19 data
analysis tools. You then click on the name of the tool you wish to use and click on OK. A dialog
box will then appear which allows you to enter the input range, output options and any other options required for the tool you have selected. If you are uncertain of an entry for a tool’s dialog box, first click on Help (this is the Help in the tool’s dialog box, not the Help in the prior Data
Analysis dialog box with the scrolling list of the 19 tools). Once the tool’s dialog box is complete, click on OK and the output will appear.
5. For What Analyses Are the Data Analysis Tools Used? A presentation of the uses
of the data analysis tools is perhaps most helpful if it is given by category of analysis. Accordingly, the following list of the 18 statistical tools is by the type of statistical analysis each supports. The six categories used are Descriptive Statistics, Sampling, Hypothesis Testing, Analysis of Variance, Regression and Correlation, and Time Series Forecasting. The order of presentation of the six
categories is the order of a typical business statistics textbook. Within each category the tools are listed in the order they appear in this manual. The entry for each of the 18 statistical tools provides its name and its purpose. Further description of these is available through the Excel help
facility. To access it, select Tools from the menu bar, Data Analysis from the subsequent pull-
down menu, select the tool you wish to use from the resulting scrolling list, click on OK and click
on Help in the next dialog box.
DESCRIPTIVE STATISTICS
Histogram—determines and graphs individual and cumulative frequencies for a one
variable data set.
Descriptive Statistics—generates a report of the values for 16 descriptive statistics such as the mean, median, mode, standard deviation, range, skewness and kurtosis for a one variable data set.
Excel’s Data Analysis Tools 4
Rank and Percentile—produces a table of the ordinal and percentage rank of each value in a one variable data set.
SAMPLING
Random Number Generation—generates random values drawn from one of six possible
probability distributions (it also has a non-random selection called patterned).
Sampling—draws a random sample from a specified population for a single variable. This is the one statistical data analysis tool not used within this manual.
HYPOTHESIS TESTING
t-Test: Two Sample Assuming Unequal Variances—performs a t-test on two
independent samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the two population variances are not known to be equal.
t-Test: Two Sample Assuming Equal Variances—performs a t-test on two independent
samples to determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the two unknown population variances are known to be equal.
z-Test: Two Sample for Means—performs a z-test on two independent samples to
determine if the difference in the two population means is equal to a specified value (such as zero). This test is conducted under the condition that the values of the two population variances are known, or the sample sizes are large so the normal distribution can be used to approximate the t distribution.
t-Test: Paired Two Sample for Means—performs a t-test on two paired (matched)
samples to determine if the mean of the differences between the two population is equal to a specified values (such as zero).
F-Test: Two-Sample for Variances—performs an F-test on two independent samples to
determine if two population variances equal.
Excel’s Data Analysis Tools 5
ANALYSIS OF VARIANCE
ANOVA: Single-Factor—performs a one-way analysis of variance to determine if two or more population means are equal. This test uses data from a completely randomized
experimental design.
ANOVA: Two-Factor Without Replication—performs a two-way analysis of variance
without interaction to determine if two or more population means are equal. This test uses data from a randomized block experimental design.
ANOVA: Two-Factor With Replication—performs a two-way analysis of variance with
interaction to determine if two or more population means are equal. This test uses data from a two-way factorial experimental design.
REGRESSION AND CORRELATION
Covariance—creates a table of covariance values for all possible pairs of 2 or more independent variables.
Correlation—creates a table of correlation coefficients for all possible pairs of 2 or more independent variables.
Regression—performs a simple linear regression analysis or a multiple regression analysis with up to 16 independent variables.
TIME SERIES FORECASTING
Moving Average—projects a time series based on the moving averages smoothing method.
Exponential Smoothing—projects a time series based on the simple exponential
smoothing method.