Statistics and data analysis with Excel

Calculate the minimum, maximum, and count of values in a numerical column using Excel functions MIN, MAX, and COUNT. Explore determining a range's statistics and overall data insights.

Learn to compute the mean value in Excel using the average formula on a worksheet. Use the result to view the dataset center as a key high-level statistic.

Learn to compute variance and standard deviation in Excel for population and sample data. Use variance formulas: population uses N, sample uses N-1; standard deviation is the square root.

Compute skewness to measure the asymmetry of a data distribution, using population and sample formulas; larger samples approach the population, revealing the dataset is skewed.

Explore quartiles and percentiles in Excel, comparing inclusive and exclusive extremes to assess the shape of the distribution, with the median as the second quartile and percentiles for outlier detection.

Explore the interquartile range, defined as the third quartile minus the first quartile, representing the 50 percent data spread. Compute the quartiles to obtain the interquartile range and identify outliers.

Apply conditional operations on a column to count positives and negatives with count if, sum if, and average if, using criteria greater than zero or less than zero.

Detect outliers with the interquartile range by computing q1 and q3 and bounds q3+1.5*IQR and q1-1.5*IQR, then label them in Excel with conditional formatting.

learn how to compute moving averages as rolling averages of the last n points, including or excluding the current point, to analyze time series and forecast trends in algorithmic trading.

Calculate rolling maximum or minimum over the last five or ten periods, including the current period, by applying the formula to the data in Excel, useful for stock prices.

Calculate the percentage change between periods using the change rate, i.e., current minus previous divided by previous, and format the result as a percent for year-over-year comparisons.

Compute the mean’s confidence intervals using either the t distribution (small samples) or normal distribution (large samples), via mean ± critical × s / sqrt(n).

Compute confidence intervals for a proportion using the normal approximation, with p as positives over total and bounds p-hat plus or minus z times the standard deviation (1.96 for 95%).

Learn how the Pearson linear correlation coefficient measures the strength of a linear relationship between two variables, from -1 to 1, including perfect and zero correlations.

Explore how to perform linear regression in Excel, using a single x variable to create a trend line, display its equation graphically, and compute slope, intercept, and predictions with forecast.

Compute the p-value for a one-sample t-test by comparing a sample mean to an expected value, including degrees of freedom, standard deviation, and two-tailed or one-tailed tests in Excel.

Learn how to perform two-sample t-tests in Excel, including paired t-tests, equal-variance t-tests, and Welch's test, with one- and two-tail p-values and interpretation.

Perform an f-test in Excel to compare two sample variances, test the null hypothesis that they are equal, and interpret a low p-value as evidence against equality.

Use the chi-square test in Excel to compare observed and expected frequencies in a contingency table, assessing independence via the p-value, with expected frequencies above 15–20 for reliability.

Learn to compute critical z-values for the normal distribution and build a z-table using Excel, covering left and right tail probabilities and inverse cdf.

Calculate critical t-values for the Student's t distribution by varying degrees of freedom and tail probabilities, noting that larger df makes the distribution closer to normal and building a t-table.

Learn to compute sums, averages, and maxima for a filtered subset of table records using conditional formulas on a column, such as name equals John or Mary.

Understand how to use vlookup in Excel to merge a table with another by a common key, using exact match and absolute references.

Explore database operations in Excel by filtering a table with criteria and computing sum, average, minimum, maximum, count, product, and standard deviation on selected columns.

Explore how to create pivot tables in Excel to analyze revenue by name or country, use dimensions and measures, and apply drill down and various value settings.

Filter pivot table calculations by country and name to isolate revenue for selected countries. Apply value filters and clear filters to extract targeted insights, such as revenue greater than 600.

Explore pivot tables that summarize revenue across both rows and columns, using country and name to reveal totals and enable filters for exploring two-dimensional data.

Represent and analyze revenue data by creating a people table, then apply value field settings to show percentages of grand total and column totals for clear insights.