Microsoft Excel Statistics & Data Analysis – Zero to Analyst

Discover how statistics turn messy data into clear signals you can act on, using descriptive and inferential methods to summarize samples and generalize to populations with Excel formulas.

Explore population versus sample, and how statistics infer parameters from a sample. Master key terms like parameter, statistic, mean, standard deviation, correlation, margin of error, and confidence level with Excel.

Learn to write formulas with the equals sign, use dynamic and absolute references, and apply sum for ranges, then copy results with the fill handle for tax calculations.

Convert ranges to tables with Ctrl T to enable headers, sorting, filtering, and automatic formulas. Navigate data with shortcuts like Ctrl+arrow and Ctrl+A, and use Flash Fill to merge names.

Explore the statistics workflow in Excel, starting with descriptive statistics and data cleaning, then examine probability distribution, central limit theorem, hypothesis tests with a p-value, and regression for confident decisions.

Explore descriptive statistics in Excel by analyzing shape, center, and spread using frequency distributions, z-scores, skewness, kurtosis, and the data analysis tool pack.

Analyze a column of numbers by identifying the distribution, central tendency, and variability—shape, middle, and spread—using histograms, frequency tables, and measures like mean, median, and mode.

Build a numerical frequency table in Excel to count scores by grade ranges (0-59, 60-69, 70-79, 80-89, 90-100), using the frequency function and countifs, then create histograms.

Convert your frequency table into a clean histogram to visualize data distribution, set bin labels, add axis titles, and refine formatting for a clear, at-a-glance chart.

Explore the mean, median, and mode as three centers of data, using Excel functions average, median, and mode (with mode.sngl and mode.multi) to interpret typical values.

Learn how to find the mean, median, and mode in Excel using sample datasets, interpret their meaning, and read the center of your data.

Learn how to use z-scores to describe individual data points relative to the mean and standard deviation, with examples from call lengths and a simple calculation formula.

Explore skewness and kurtosis to picture data shape, identify right or left skew and peak or flat distributions, and see how Excel computes these metrics for a 60-day dataset.

Apply the empirical rule to real-world nba player heights by calculating mean and standard deviation, converting heights to z-scores, and confirming 68-95-99.7% coverage with an outlier.

Enable the Data Analysis Toolpack in Excel to perform descriptive statistics, including mean, median, mode, standard deviation, and summary statistics, for quick data exploration.

Calculate the mean, median, and stdev.s of employee bonuses, flag outliers with a two-standard-deviation rule, and interpret the results to explain spread and anomalies.

Apply IQR-based outlier detection on skewed data using Q1, Q3, and 1.5 times IQR to set lower and upper fences, via the box plot whisker rule.

Learn the basics of probability, from 0–1 and 0.5 in a fair coin to empirical and subjective probabilities, sample space, and calculating event likelihoods using data.

Explore percentiles, from the 25th, 50th, and 75th to any percentile, and learn how to compute percentile ranks and values using inclusive and exclusive methods in Excel.

Explore probability with multiple events, distinguishing independent and dependent cases, using addition and multiplication rules, and verify ideas through simple Excel simulations and countif, randbetween, and countifs.

Master the multiplication rule of probability by multiplying stepwise chances for independent events and adjusting fractions for dependent draws, illustrated with dice and card draws.

Explore discrete distributions by comparing the Binomial and Poisson models, learning how to assign probabilities to outcomes, distinguish fixed-trial versus rate-based counting, and apply Excel functions binom.dist and Poisson.dist.

Use the binomial distribution to assess 4% defects in 30-bag batches. Compute P(X ≥ 4) and interpret results across 25 batches to distinguish normal variation from anomaly.

Explore the Poisson distribution for counting events in a fixed interval using lambda, and apply Excel Poisson.dist for exact or cumulative probabilities in staffing and arrival modeling.

calculate the discrete mean from a frequency table in Excel using SUMPRODUCT, weighting counts by customers, then divide by total customers; introduces standard deviation and the bell curve.

Discover the normal distribution, a symmetrical bell curve centered on the mean where most values cluster, with standard deviation measuring spread, the 68-95-99.7 rule, and Excel calculations.

Explore the normal distribution in Excel using histograms to visualize the bell curve. Learn to compute mean and standard deviation and interpret 68%, 95%, and 99.7% ranges.

Turn heights into z-scores, then use Excel's norm.s.dist and norm.inv to find percentages below or above a value and identify percentile cutoffs based on the mean and standard deviation.

Create a smooth standard normal curve in Excel by generating z-scores from -3 to 3, calculating density and cumulative values with norm.s.dist, then charting a scatter with smooth lines.

Learn how to sample from a population using random, reasonably large samples in Excel to support descriptive and inferential analysis, including confidence intervals and hypothesis tests.

Explore how the central limit theorem makes the distribution of sample means approach a normal bell curve as sample size grows, enabling confidence intervals and hypothesis tests.

Explore how the standard error quantifies the typical scatter of p-hat around the population proportion p, and how larger samples (n) tighten the band, as in 60%.

Compare z and t statistics: use z with known population sd and large samples; use t with small samples and unknown spread. Rely on df = n−1, Excel t functions.

Learn to construct confidence intervals for a proportion using p-hat, standard error, and z-score, with practical examples showing upper and lower limits and a 95% confidence level.

Learn to estimate a population proportion using Excel by drawing random samples, computing p-hat, and constructing a two-sided 95% confidence interval with standard error and margin of error.

Rebuild the confidence interval for the proportion of on-time packages using a random sample of 50, then compare it to the 10-sample result to illustrate narrower intervals at 95%.

Compute confidence intervals for means using z when sigma is known and the sample is large, or t when sigma is unknown, applying standard error and the margin of error.

Learn how hypothesis testing uses sample data to evaluate a population claim by treating the sample as evidence, applying the sample average, size, variability, and distribution to determine believability.

Master the five steps of hypothesis testing, from stating null and alternative hypotheses to interpreting p-values and decisions to reject or not reject the null, across means, proportions, and ANOVA.

Explore one-tailed and two-tailed hypothesis tests using the four-minute mean example, identifying when the null and alternative hypotheses imply left or right tails, and how Excel handles tails.

Use excel to test means with z tests (known sigma) or t tests (unknown sigma, small samples), turning sample means into test statistics and p-values.

Explore hypothesis testing for proportions with Excel to determine if a sample proportion exceeds 0.5 in a right-tailed test, using z-score and p-value.

Learn how hypothesis tests use null and alternative hypotheses, alpha levels, and a p-value to identify type I (false alarm) and type II (missed problem) errors in data analysis.

Compare two independent populations using sample proportions or means, test whether observed differences are real via hypothesis testing, and use Excel to perform calculations.

Compare two population proportions to determine if packing methods A and B differ in damage rates; with alpha 0.05, p-value 0.206 means we do not reject the null.

Compare two samples to determine if training methods A and B yield different mean delivery times. Apply a two-sample z-test with known sigma, left-tail, alpha 0.05, and p-values in Excel.

Compare variances with the f-test for variances to assess consistency between two production lines, and conclude line b is significantly more variable at alpha 0.05.

Master the paired t-test for connected data, using before-and-after measurements to assess intervention effects with a one-sample t-test on differences.

Learn to apply the g-square test by comparing observed counts to expected counts in categories. Distinguish between goodness-of-fit and independence tests and follow a 5-step hypothesis framework to interpret p-values.

Use chi-square goodness-of-fit test in Excel to assess whether observed counts across seven days match an equal distribution, including computing expected counts, chi-square statistic, p-value, and decision with alpha 0.05.

Apply the g-square test of independence to a 3x3 contingency table to determine if age group and drink choice are related, using expected counts, p-values, and marketing implications.

Learn how ANOVA compares three or more groups, examining within-group and between-group variation to determine significance via p-values and F statistics.

Anova compares three or more group means by examining between and within variation, testing the null that all means are equal with alternative that at least one differs, via F.

Learn how regression predicts Y from X, starting with simple regression and moving to multiple regression. Examine linear regression and the regression equation, with R squared and hypothesis testing.

Learn how Pearson's correlation coefficient r measures whether two variables move together, how to interpret values from -1 to 1, and why scatter plots matter before inferring causation.

Use the least squares method to fit the regression line, estimate slope and intercept, and interpret residuals to predict y from x.

Explore how R-squared measures goodness of fit in simple linear regression by comparing explained versus total variation. Learn about interpretation, limitations, and standard error of the estimate for model assessment.

Test the regression slope with a two-tailed hypothesis test of zero slope, interpret the t-statistic, p-value, and 95% confidence interval, and assess model significance using Excel's data analysis toolpak.

Explore multiple regression with predictors foot traffic, sunshine, marketing spend, and staff, and see coefficients, adjusted R-squared, and multicollinearity checks to sharpen revenue predictions in Excel.

Predict new days’ revenue with a validated multiple regression model by inputting predictors to obtain a point estimate and a prediction interval. Avoid extrapolation beyond the training data.

Master the full regression workflow, from correlation and simple regression to multiple regression, interpret coefficients, check assumptions, and produce interval predictions for continuous outcomes.