Source document. This page reproduces the syllabus this course was written to, as published — its semesters, credits and paper numbers are that document’s, not this site’s. The course itself is studied on its own, in any order.

Course Information

Title	Statistical Data Analysis using MS-Excel
Theory Credits	3 (3 hrs/week)
Practical Credits	1 (2 hrs/week)

Course Outcomes

1. Understand data entry & formatting, manage worksheets, apply arithmetic and logical operations, perform sorting/filtering/validation, and use Excel add-ins for analysis.
2. Create and interpret different types of charts and graphs, prepare frequency tables, and summarise data using PivotTables and PivotCharts.
3. Calculate and interpret measures of central tendency and dispersion, apply ranking and position measures, analyse skewness and kurtosis, and generate descriptive statistics reports.
4. Analyse relationships using correlation and regression techniques, interpret regression outputs, and apply forecasting methods to predict future trends.
5. Perform hypothesis tests such as Z, t, F, ANOVA and chi-square; interpret Excel outputs and apply results in real-world decision making.

Theory — Five Units

Unit 1: Excel Basics for Data Analysis

Data entry, formatting, worksheet management. Basic Excel functions — Arithmetic (SUM, PRODUCT, QUOTIENT, MOD), Logical (IF, AND, OR, IFERROR), Lookup (VLOOKUP, HLOOKUP, XLOOKUP, INDEX, MATCH). Cell referencing: relative, absolute, mixed. Data management: sorting, filtering, conditional formatting. Data validation. Excel add-ins: Analysis ToolPak, Solver.

Open Unit 1 →

Unit 2: Data Visualization & Frequency Analysis

Charts and graphs: Bar, Column, Line, Pie, Area, Scatter, Histogram. Frequency tables: COUNT, COUNTIF, COUNTIFS, FREQUENCY. PivotTables and PivotCharts for data summarisation.

Open Unit 2 →

Unit 3: Descriptive Statistics in Excel

Central tendency (AVERAGE, MEDIAN, MODE.SNGL, MODE.MULT, GEOMEAN, HARMEAN). Dispersion (MAX-MIN, VAR.S/P, STDEV.S/P, Coefficient of Variation). Position & ranking (RANK.EQ, RANK.AVG, PERCENTRANK.INC/EXC). Shape (SKEW, KURT). Descriptive Statistics report via ToolPak.

Open Unit 3 →

Unit 4: Correlation, Regression & Forecasting

Pearson correlation (CORREL), covariance (COVARIANCE.P/S), Spearman rank using RANK.AVG + CORREL. Simple Linear Regression via ToolPak; SLOPE, INTERCEPT, FORECAST.LINEAR. Trend & forecasting using TREND.

Open Unit 4 →

Unit 5: Hypothesis Testing in Excel

Z-Test (Z.TEST), t-Test via ToolPak (Paired, Two-Sample Equal/Unequal Variance), F-Test via ToolPak, ANOVA via ToolPak (Single Factor, Two-Factor). Goodness of Fit & Association: CHISQ.TEST. Real-world case studies in business, health sciences, social sciences. Interpreting Excel output for decision making.

Open Unit 5 →

Practical — List of Experiments (10)

1. Central Tendency — calculate mean, median, and mode for a given dataset.
2. Dispersion — variance, standard deviation, and coefficient of variation.
3. Skewness and Kurtosis — compute and interpret distribution shape.
4. Correlation Analysis — simple correlation and correlation matrix for multiple variables (height, weight, age).
5. Simple Linear Regression — fit a regression line and estimate the dependent variable.
6. Z and t-Tests — one-sample and two-sample tests (large and small samples).
7. Paired t-test — before-and-after comparison.
8. F-test — equality of variances between two independent samples.
9. Chi-Square Test — goodness of fit.
10. ANOVA — one-way and two-way to test differences among groups.

Note: MS-Excel practical problems must be done in the Computer Lab at least 4 hours per month.

Open practical course material →

Text Books / References

1. K. V. S. Sharma — Statistics Made Simple: Do it yourself on PC.
2. N. Balakrishnan, K. Chandrasekaran & M. Saravanavel — Practical Statistics using Microsoft Excel, Sultan Chand & Sons.
3. S. P. Gupta & Archana Gupta — Statistical Methods, Sultan Chand & Sons.
4. J. K. Sharma — Business Statistics: Problems and Solutions using Excel, Vikas Publishing House.
5. P. N. Arora & S. Arora — Statistics for Management with Excel Applications, S. Chand.
6. N. D. Vohra — Business Statistics, McGraw Hill (India).

Suggested Co-Curricular Activities

1. Training of students by related industrial experts.
2. Assignments including technical assignments, if any.
3. Seminars, group discussions, quiz, debates etc. on related topics.
4. Preparation of audio and videos on tools of diagrammatic and graphical representations.
5. Collection of material / figures / photos of related topics.
6. Invited lectures and presentations of stalwarts on those topics.
7. Visits / field trips of firms, research organizations etc.

Exam Blueprint (recommended)

Unit	Topic	Approx. Weightage
1	Excel Basics	15 %
2	Visualization & Frequency	15 %
3	Descriptive Statistics	20 %
4	Correlation & Regression	25 %
5	Hypothesis Testing	25 %

Quick Reference — Excel Formulas at a Glance

Need	Formula
Mean	=AVERAGE(range)
Median	=MEDIAN(range)
Mode	=MODE.SNGL(range)
Variance / SD (sample)	=VAR.S / =STDEV.S
Skewness / Kurtosis	=SKEW / =KURT
Rank / Percentile	=RANK.AVG / =PERCENTILE.INC
Correlation / Covariance	=CORREL / =COVARIANCE.S
Slope / Intercept	=SLOPE / =INTERCEPT
Forecast	=FORECAST.LINEAR / =TREND
Z-test (one-tailed p)	=Z.TEST(array, x, sigma)
t-test (p only)	=T.TEST(arr1, arr2, tails, type)
F-test (two-tailed p)	=F.TEST(arr1, arr2)
Chi-Square (p)	=CHISQ.TEST(observed, expected)
ToolPak menu	Data → Data Analysis (after enabling)