| Concept | Description |
|---|---|
| Setup and Description | |
| Enabling the ToolPak | An add-in switched off by default, absent entirely from Excel for the web |
| ToolPak Output Is Static | Results are pasted values that do not update when the source data changes |
| Descriptive Statistics | Mean, median, SD, variance, skewness, excess kurtosis, range and confidence interval per column |
| Correlation and Its Limits | Pearson only, no p-values reported, and no built-in Spearman rank correlation |
| Comparing Two Groups | |
| Choosing Among the Three t-Tests | Paired for repeated measures on the same units, independent forms for separate groups |
| Testing Variance Equality First | An F-test decides between equal and unequal variance versions; Welch is a safe default |
| Reading t-Test Output | Check the two-tail p-value, compare to alpha, then read direction and size from the means |
| One-Tailed versus Two-Tailed | One-tailed is legitimate only when the direction was specified before seeing the data |
| The z-Test | Requires known population variance, so it is rarely applicable to real data |
| ANOVA | |
| Why ANOVA Rather Than Repeated t-Tests | Three pairwise t-tests inflate the false positive rate to roughly 14 percent |
| Reading ANOVA Output | A significant F says at least one mean differs, without identifying which |
| No Post-Hoc Test in Excel | Tukey's HSD and other post-hoc procedures are unavailable and must be done in R |
| Two-Factor ANOVA and Interaction | With replication, the interaction row tests whether one factor's effect depends on the other |
| Regression | |
| Regression Output: Significance F and R Square | Significance F validates the whole model; adjusted R square compares models fairly |
| Regression Coefficients and Their Units | Each coefficient is a change per unit holding others fixed, and is not comparable across units |
| Confidence Intervals and Residual Plots | Intervals are more informative than p-values; residual plots reveal variance and curvature faults |
| Functions and Limits | |
| Live Function Equivalents | T.TEST, F.TEST, CORREL, LINEST and FORECAST.LINEAR stay live where ToolPak output does not |
| Where the ToolPak Stops | No post-hoc tests, non-parametrics, logistic regression, effect sizes, VIF, or cross-validation |
19 Statistical Analysis with the Analysis ToolPak
Excel ships with a statistics add-in that is switched off by default. Once enabled, the Analysis ToolPak runs descriptive statistics, correlation, t-tests, z-tests, ANOVA, and multiple linear regression from dialog boxes, and prints output that looks much like any statistical package’s.
The theory behind these tests is covered in Fundamentals of Statistical Tests and the tests themselves in Inferential Statistical Techniques. This section is about running them in Excel and reading what comes back.
All examples use the TrialResults sheet:
| Range | Contents |
|---|---|
A1:C16 |
Three fertilizer treatments, 15 plots each: Control, BioFertilizer, NPK_Plus
|
E1:G16 |
Paired plots: PlotID, Before, After
|
I1:K11 |
Two-factor layout: irrigation down the rows, variety across the columns, 5 replicates per cell |
19.1 Installing the ToolPak
Windows. File, Options, Add-ins. At the bottom, set Manage to Excel Add-ins and click Go. Tick Analysis ToolPak and confirm. A Data Analysis button appears at the right-hand end of the Data tab.
Mac. Tools, Excel Add-ins, tick Analysis ToolPak, then OK. On some builds it appears under Data, Analysis Tools.
Excel for the web does not include the ToolPak. The worksheet functions listed further down do work there, and they cover most of what the dialogs produce.
One characteristic of every ToolPak tool matters before starting. The output is static. It is a block of pasted values, not live formulas. Change the source data and the results do not update, and nothing indicates that they are now stale. Re-run the tool after any change to the data, and delete superseded output rather than leaving two blocks on the sheet.
19.2 Descriptive Statistics
Data, Data Analysis, Descriptive Statistics. Set the Input Range to A1:C16, tick Labels in first row, choose an output location, and tick Summary statistics. Tick Confidence Level for Mean as well, which adds the margin of error at 95 percent.
The output gives, for each column: mean, standard error, median, mode, standard deviation, sample variance, kurtosis, skewness, range, minimum, maximum, sum, and count.
Skewness and kurtosis are worth reading rather than skipping. Skewness near zero and kurtosis near zero indicate a roughly normal shape, which is one of the assumptions behind the t-test and ANOVA that follow. Strong skewness is a signal to consider a non-parametric alternative, as set out in Choosing the Right Statistical Test. Excel reports excess kurtosis, so a normal distribution gives approximately 0 rather than 3.
The same quantities individually, as live formulas:
=AVERAGE(A2:A16) =MEDIAN(A2:A16) =MODE.SNGL(A2:A16)
=STDEV.S(A2:A16) =VAR.S(A2:A16) =SKEW(A2:A16)
=KURT(A2:A16) =COUNT(A2:A16)
=STDEV.S(A2:A16)/SQRT(COUNT(A2:A16)) Standard error of the mean
=CONFIDENCE.T(0.05, STDEV.S(A2:A16), COUNT(A2:A16))19.3 Correlation
Data, Data Analysis, Correlation. Give it a range of two or more numeric columns and it returns a correlation matrix.
On FarmData, running it across Rainfall, NDVI, SoilMoisture, FertilizerSpend, and YieldQtPerHa produces the lower triangle of pairwise Pearson correlations in one block.
As formulas:
=CORREL(G2:G61, J2:J61)
=PEARSON(G2:G61, J2:J61) Identical result
=RSQ(J2:J61, G2:G61) The square of the correlationTwo limitations of the ToolPak’s version. It reports the coefficients but no p-values, so significance has to be computed separately. And it computes Pearson correlation only, which assumes a linear relationship and is sensitive to outliers. For ranked or non-normal data, Spearman’s rank correlation is the right choice, and Excel has no built-in function for it; it is computed by applying RANK.AVG to both columns and running CORREL on the ranks.
Testing whether a correlation differs significantly from zero:
=CORREL(G2:G61,J2:J61)*SQRT(COUNT(G2:G61)-2)/SQRT(1-CORREL(G2:G61,J2:J61)^2) t statistic
=T.DIST.2T(ABS(N5), COUNT(G2:G61)-2) two-tailed pwhere N5 holds the t statistic from the first formula.
On the sample data, yield correlates 0.64 with NDVI, 0.41 with soil moisture, 0.39 with rainfall, and 0.30 with fertilizer spend. Those numbers say the pairs move together. They do not say the first causes the second, and on farm data a spend-to-yield correlation very often reflects that larger, better-capitalised farms both spend more and grow in better conditions. Separating the two requires holding the other variables constant, which is exactly what the regression further down does and what a correlation cannot.
19.4 Comparing Two Groups: t-Tests
The ToolPak offers three t-tests, and picking the wrong one is the most common error in this whole section.
| Tool | Use when |
|---|---|
| Paired Two Sample for Means | The same plots measured twice, before and after |
| Two-Sample Assuming Equal Variances | Two independent groups with similar spread |
| Two-Sample Assuming Unequal Variances | Two independent groups, spread not assumed equal |
Paired applies when each value in one column corresponds to a specific value in the other. The Before and After columns on the same 15 plots are paired. Treating them as independent throws away the pairing and loses most of the test’s power.
Independent samples apply when the two columns are different plots. Control against BioFertilizer is independent.
19.4.1 Which Independent Test
Test the variances before choosing. Data, Data Analysis, F-Test Two-Sample for Variances, with the two ranges as inputs.
A significant F-test (p below 0.05) means the variances differ, so the unequal-variances test is required. A non-significant result permits the equal-variances version.
=F.TEST(A2:A16, B2:B16) Two-tailed p-value for equal variancesIn practice, the unequal-variances test (Welch’s) is a sound default. It costs very little power when variances are in fact equal, and it remains valid when they are not. Statisticians increasingly recommend simply using it.
19.4.2 Running and Reading a t-Test
Data, Data Analysis, t-Test: Two-Sample Assuming Unequal Variances. Variable 1 Range is A1:A16, Variable 2 Range is B1:B16, Hypothesized Mean Difference is 0, tick Labels, and set Alpha to 0.05.
The output takes this form:
t-Test: Two-Sample Assuming Unequal Variances
Control BioFertilizer
Mean 33.4213 38.9820
Variance 23.5067 9.0739
Observations 15 15
Hypothesized Mean Difference 0
df 23
t Stat -3.7731
P(T<=t) one-tail 0.000480
t Critical one-tail 1.71387
P(T<=t) two-tail 0.000960
t Critical two-tail 2.06866Read it in this order.
Which p-value applies. Use the two-tail value unless the hypothesis was directional before the data was seen. “Does BioFertilizer change yield” is two-tailed. “Does BioFertilizer increase yield” is one-tailed, and only legitimate if that direction was specified in advance. Choosing the one-tailed value after seeing which way the difference went halves the p-value dishonestly.
Compare to alpha. Here 0.000960 is below 0.05, so the null hypothesis of equal means is rejected.
Read the direction and size from the means. BioFertilizer averages 38.98 against Control’s 33.42, a difference of 5.56 quintals per hectare. The p-value establishes that the difference is unlikely to be chance; it says nothing about whether 5.56 is worth the cost of the input. That second question is the one a farmer actually asks, and it is answered by the means and by the price of the fertilizer, not by the significance test.
The sign of t Stat follows from the order of the ranges, not from anything about the data. Swapping Variable 1 and Variable 2 flips it. Only the magnitude matters.
As a single formula, where the third argument is tails and the fourth is test type (1 paired, 2 equal variance, 3 unequal variance):
=T.TEST(A2:A16, B2:B16, 2, 3) Two-tailed, unequal variances
=T.TEST(F2:F16, G2:G16, 2, 1) Two-tailed, paired
=T.TEST(A2:A16, B2:B16, 2, 2) Two-tailed, equal variancesThe paired output adds a Pearson Correlation row showing how strongly the two measurements track each other. A high value there confirms the pairing was worth using.
19.4.3 z-Test
Data, Data Analysis, z-Test: Two Sample for Means. It requires the population variances to be entered, which is what separates it from the t-test.
That requirement is why it is rarely used on real data: knowing the true population variance while not knowing the population mean is an unusual situation. With samples above roughly 30, the t-distribution converges on the normal anyway and the two tests agree closely. Use the t-test unless there is a specific reason the population variance is genuinely known.
=Z.TEST(A2:A16, 35) One-tailed p, testing against a hypothesised mean of 35
=1 - Z.TEST(A2:A16, 35) The other tail
=2 * MIN(Z.TEST(A2:A16, 35), 1 - Z.TEST(A2:A16, 35)) Two-tailed19.5 Comparing More Than Two Groups: ANOVA
Three treatments cannot be compared with three t-tests. Each test carries a 5 percent chance of a false positive, and three of them together carry roughly 14 percent. ANOVA tests all groups at once while holding the overall error rate at 5 percent. The reasoning is set out in Errors and Power.
Single Factor compares group means for one grouping variable. Data, Data Analysis, ANOVA: Single Factor, Input Range A1:C16, Grouped By Columns, Labels in first row.
ANOVA: Single Factor
SUMMARY
Groups Count Sum Average Variance
Control 15 501.32 33.4213 23.5067
BioFertilizer 15 584.73 38.9820 9.0739
NPK_Plus 15 604.43 40.2953 26.3103
ANOVA
Source of Variation SS df MS F P-value F crit
Between Groups 399.489 2 199.744 10.175 0.000249 3.2199
Within Groups 824.472 42 19.630
Total 1223.961 44The p-value of 0.000249 is below 0.05, so at least one treatment mean differs from the others.
That is the whole of what ANOVA says. It does not identify which pair differs, and reading the summary averages to conclude that NPK_Plus beats Control is not a test, it is an eyeball. Identifying specific pairs requires a post-hoc procedure such as Tukey’s HSD, which Excel does not provide. R does, through TukeyHSD(), and this gap is one of the clearer practical reasons to move the analysis there.
F crit is the threshold the F statistic must exceed at the chosen alpha. Comparing F to F crit and comparing the p-value to alpha are the same decision expressed two ways.
Two-Factor Without Replication handles a design with one observation per combination, and is also the correct tool for a repeated-measures layout.
Two-Factor With Replication handles several observations per combination, and adds an interaction term. On the I1:K11 block, with irrigation down the rows and variety across the columns and 5 replicates per cell, set Rows per sample to 5. The output reports three effects: the row factor, the column factor, and the interaction between them.
The interaction row is the one worth the trouble. A significant interaction means the effect of variety depends on which irrigation type it is grown under, so reporting a single average effect for the variety would be misleading. The layout must be exactly as Excel expects, with the row factor labels in the first column at the start of each replicate block; an irregular layout produces either an error or quietly wrong output.
=F.DIST.RT(10.175, 2, 42) p-value from an F statistic and its two df
=F.INV.RT(0.05, 2, 42) The F crit value19.6 Regression
Data, Data Analysis, Regression. Input Y Range is the outcome, a single column. Input X Range is one or more predictor columns, which must be adjacent. Tick Labels, and tick Residuals and Residual Plots.
Predicting yield from rainfall, NDVI, and fertilizer spend on FarmData requires those three columns to sit side by side, which sometimes means copying them to a staging area first. That adjacency requirement is a real constraint of the tool.
SUMMARY OUTPUT
Regression Statistics
Multiple R 0.7720
R Square 0.5960
Adjusted R Square 0.5743
Standard Error 4.0568
Observations 60
ANOVA
df SS MS F Significance F
Regression 3 1359.57 453.19 27.536 4.50E-11
Residual 56 921.65 16.46
Total 59 2281.22
Coefficients Std Error t Stat P-value Lower 95% Upper 95%
Intercept 3.5248 3.7757 0.9336 0.35449 -4.0387 11.0883
Rainfall 0.0124 0.0044 2.8151 0.00672 0.0036 0.0212
NDVI 35.4676 5.0010 7.0921 2.44E-09 25.4494 45.4858
FertilizerSpend 0.4574 0.1120 4.0857 0.00014 0.2331 0.6817Read it in four passes.
Is the model worth anything at all? Look at Significance F, the p-value for the whole model. At 4.50E-11 this model explains significantly more than nothing. A Significance F above 0.05 means the individual coefficients are not worth reading.
How much does it explain? R Square of 0.5960 means the three predictors together account for about 60 percent of the variation in yield. Adjusted R Square (0.5743) penalises the model for the number of predictors, and is the figure to quote when comparing models with different numbers of variables, because plain R Square never falls when a predictor is added, however useless that predictor is.
Which predictors matter? Each row’s P-value tests whether that coefficient differs from zero while holding the others constant. All three predictors fall below 0.05. The intercept’s p-value of 0.354 is of no interest in itself; an intercept is a mathematical necessity, not a finding, and a farm with zero rainfall and zero NDVI is not a thing the model is being asked about.
What does each coefficient mean? A one-unit increase in the predictor changes predicted yield by the coefficient, holding the other predictors fixed. One additional millimetre of rainfall adds 0.0124 quintals per hectare. NDVI’s 35.4676 looks enormous until the scale is noticed: NDVI runs from 0 to 1 and spans 0.32 to 0.72 in this data, so a realistic swing of 0.1 corresponds to about 3.5 quintals per hectare. Coefficients cannot be compared across predictors measured in different units. Ranking them by size is a common and meaningless exercise.
The 95 percent confidence intervals are more informative than the p-values and are usually ignored. Rainfall’s interval of 0.0036 to 0.0212 says the data is consistent with an effect anywhere in that range, which spans roughly a factor of six and is worth saying out loud before anyone plans around the point estimate. An interval that spans zero corresponds exactly to a p-value above 0.05, which is why the intercept’s interval runs from negative to positive.
Residual plots, if ticked, show residuals against each predictor. Random scatter is what the model assumes. A funnel shape indicates non-constant variance and a curve indicates a missing non-linear term, both of which are treated properly in Regression Diagnostics and Model Evaluation.
19.6.1 Regression as Worksheet Functions
For a simple regression, these stay live when the data changes:
=SLOPE(J2:J61, G2:G61) Coefficient
=INTERCEPT(J2:J61, G2:G61) Constant
=RSQ(J2:J61, G2:G61) R squared
=STEYX(J2:J61, G2:G61) Standard error of the estimate
=FORECAST.LINEAR(750, J2:J61, G2:G61) Prediction at rainfall of 750LINEST handles multiple regression and returns a whole array of statistics. Entered in a cell on Microsoft 365, it spills automatically; on older versions, select a 5-row by 4-column range, type it, and confirm with Ctrl + Shift + Enter:
=LINEST(J2:J61, G2:I61, TRUE, TRUE)The result returns coefficients in reverse column order along the top row, standard errors beneath them, then R squared and the standard error of the estimate, then F and residual df, then the regression and residual sums of squares. It repays keeping a labelled template to hand, since the output carries no headers of its own.
19.7 Worksheet Function Equivalents
| Task | ToolPak tool | Function |
|---|---|---|
| Mean, SD, skewness | Descriptive Statistics |
AVERAGE, STDEV.S, SKEW, KURT
|
| Correlation | Correlation |
CORREL, PEARSON, RSQ
|
| Paired t-test | t-Test: Paired | T.TEST(r1, r2, 2, 1) |
| Equal-variance t-test | t-Test: Equal Variances | T.TEST(r1, r2, 2, 2) |
| Welch t-test | t-Test: Unequal Variances | T.TEST(r1, r2, 2, 3) |
| Variance equality | F-Test Two-Sample | F.TEST(r1, r2) |
| z-test | z-Test: Two Sample | Z.TEST(range, mu) |
| Chi-square test | Not available | CHISQ.TEST(actual, expected) |
| Simple regression | Regression |
SLOPE, INTERCEPT, RSQ, STEYX
|
| Multiple regression | Regression | LINEST |
| Prediction | Regression |
FORECAST.LINEAR, TREND
|
| p from a test statistic |
T.DIST.2T, F.DIST.RT, CHISQ.DIST.RT, NORM.S.DIST
|
|
| Critical value |
T.INV.2T, F.INV.RT, CHISQ.INV.RT, NORM.S.INV
|
The functions stay live when the data changes; the ToolPak output does not. For anything that will be rerun, the functions are the safer choice.
19.8 What Excel Cannot Do Here
The ToolPak stops well short of what a full analysis needs.
No post-hoc tests after a significant ANOVA, so the specific pair that differs is never identified. No non-parametric tests at all, meaning Mann-Whitney, Kruskal-Wallis, and Wilcoxon are unavailable when normality fails. No logistic regression, which rules out every binary outcome, including the Healthy against Stressed classification that Classification Models is built around. No formal normality test such as Shapiro-Wilk. No effect size measures, so Cohen’s d and eta squared have to be computed by hand. No multicollinearity diagnostics, so variance inflation factors are unavailable even though correlated predictors are routine in farm data. And no cross-validation, which means every R Square reported here is an optimistic in-sample figure, for reasons developed in K-Fold Cross-Validation.
Every one of those is a single function call in R. The next section runs this same regression there and compares the two outputs line by line.