What you will learn

• Software engineering lab
• Project exercises
• UML

Course content (14 lessons)

1. Practical 1: Employee Dataset – Formulas, Logical Functions & Charts — Program Statement Create a worksheet named "Employee Analysis" with 10 employee records and perform the following tasks using Excel formulas and features. --- Task 1: Create the…
2. Practical 2: Excel Basics & Student Academic Record Formatting — Theoretical Questions a) Basic Components of an Excel Window Component Description ----------------- ------------- Title Bar Displays the workbook name and application name Ribbon…
3. Practical 3: Cell Referencing, IF Function & Grade Calculation — Theoretical Questions a) Cell Address in MS-Excel A cell address (or cell reference) identifies a specific cell in the worksheet using its column letter + row number . Example: B5…
4. Practical 4: Employee Table – Nested IF, Logical Functions & Formatting — Program Statement Create an Employee Table with the fields: EmpID, EName, Experience, Performance Rating (out of 5). Enter data for 15 employees and add computed columns using…
5. Practical 5: Pivot Tables – Nationality, Department & Client Analysis — Theoretical Questions a) What is a Pivot Table? A Pivot Table is an interactive Excel tool that summarizes, analyzes, and explores large datasets by allowing users to rearrange…
6. Practical 6: Charts & Data Visualization — Program Statement Create various Excel charts to visualize and interpret different datasets. --- Dataset 1: Student Subject Marks Student Maths Science English Hindi -----------…
7. Practical 7: VLOOKUP & HLOOKUP Functions — Theoretical: VLOOKUP & HLOOKUP Syntax VLOOKUP (Vertical Lookup) Searches for a value in the first column of a table and returns a value from the specified column. - lookup value :…
8. Practical 8: Goal Seek in MS-Excel — Theoretical: What is Goal Seek? Goal Seek is a What-If Analysis tool in Excel that works backwards — you specify the desired result of a formula and Excel finds the input value…
9. Practical 9: Statistical Functions in MS-Excel – 30 Operations — Theoretical Questions Q1: Importance of IT Tools in Data Processing IT tools (like MS-Excel, Python, R) automate repetitive calculations, reduce human errors, process large…
10. Practical 10: Correlation, Regression & Scatter Plot Analysis — Program Statement Perform correlation and regression analysis on multiple datasets using Excel statistical functions. --- Dataset 1: City Population vs Annual Income City…
11. Practical 11: Scenario Manager in MS-Excel — Theoretical: Importance of Scenario Manager Scenario Manager (Data → What-If Analysis → Scenario Manager) allows users to save multiple sets of input values (scenarios) and switch…
12. Practical 12: Data Table in MS-Excel — Theoretical Questions a) What is a Data Table in MS Excel? A Data Table is a What-If Analysis tool that calculates multiple results for one or two variable inputs simultaneously,…
13. Practical 13: Measures of Central Tendency & Frequency Distribution — Program Statement Calculate Mean, Median, Mode and create a Frequency Distribution table with Relative Frequency. --- Dataset: Marks of 25 Students S.No Marks S.No Marks S.No…
14. Practical 14: Data Analysis ToolPak – Descriptive Statistics & Correlation — Program Statement Install and use the Data Analysis ToolPak add-in for automated descriptive statistics and correlation analysis in Excel. --- Step 1: Install Data Analysis…

Practical 1: Employee Dataset – Formulas, Logical Functions & Charts

Program Statement

Create a worksheet named "Employee_Analysis" with 10 employee records and perform the following tasks using Excel formulas and features.

---

Task 1: Create the Dataset

Create a table with the columns below and enter 10 records:

---

Task 2: Mathematical Calculations

Add computed columns:

1. Annual Salary → =C2*12 (Basic Salary × 12)
2. Experience Bonus (%) → Nested IF:

• Experience ≥ 5 years → 10%
• Experience 2–4 years → 5%
• Experience < 2 years → 2%

1. Bonus Amount → =Annual Salary * Bonus %

---

Task 3: Logical Functions (IF & Nested IF)

1. Performance Category (Nested IF):

• Score > 4.5 → Excellent
• Score 3.5–4.4 → Very Good
• Score 2.5–3.4 → Good
• Below 2.5 → Needs Improvement

1. Bonus Eligibility:

• Eligible if Performance Score ≥ 3.5 AND Experience ≥ 3 years
• Else → Not Eligible

---

Task 4: Logical + Math Combination

1. Final Bonus Amount → If Eligible → Bonus Amount, else → 0
2. Revised Annual Salary → Annual Salary + Final Bonus Amount

---

Task 5: Advanced Logical Functions

1. Salary Grade (IFS / Nested IF):

• Revised Salary ≥ ₹6,00,000 → Grade A
• ₹4,00,000–₹5,99,999 → Grade B
• Below ₹4,00,000 → Grade C

1. Promotion Recommendation:

• Promote if Grade = A OR Performance Category = Excellent
• Else → Review Required

---

Task 6: Summary Calculations (at the bottom of the sheet)

---

Task 7: Conditional Formatting

• Green Fill → Performance Category = "Excellent"
• Blue Fill → Salary Grade = "A"
• Red Fill → Bonus Eligibility = "Not Eligible"

---

Task 8: Chart Creation

Create a Clustered Column Chart:

• Chart Title: Employee Revised Annual Salary
• X-Axis: Employee Name
• Y-Axis: Revised Annual Salary
• Add data labels showing salary values
• Place the chart in the same worksheet below the table

---

Key Formulas Used

=== Annual Salary ===
=D2*12

=== Experience Bonus % ===
=IF(E2>=5, 10%, IF(E2>=2, 5%, 2%))

=== Bonus Amount ===
=G2*H2

=== Performance Category ===
=IF(F2>4.5,"Excellent",IF(F2>=3.5,"Very Good",IF(F2>=2.5,"Good","Needs Improvement")))

=== Bonus Eligibility ===
=IF(AND(F2>=3.5, E2>=3), "Eligible", "Not Eligible")

=== Final Bonus Amount ===
=IF(J2="Eligible", I2, 0)

=== Revised Annual Salary ===
=G2+K2

=== Salary Grade ===
=IFS(L2>=600000,"A", L2>=400000,"B", L2<400000,"C")

=== Promotion Recommendation ===
=IF(OR(M2="A", N2="Excellent"), "Promote", "Review Required")

Practical 2: Excel Basics & Student Academic Record Formatting

Theoretical Questions

a) Basic Components of an Excel Window

b) Workbook vs Worksheet

c) AutoFill Feature in Excel AutoFill allows Excel to automatically fill a series of data (numbers, dates, weekdays, months, custom lists) by dragging the Fill Handle (small green square at the bottom-right of a selected cell).

• Number series: Enter 1, 2 → drag down → fills 3, 4, 5…
• Date series: Enter 01-Jan-2026 → drag → fills 02-Jan-2026, 03-Jan-2026…
• Months: January → drag → February, March…
• Formulas: Drag a formula cell to apply it to adjacent cells

---

Practical Task: Student Academic Record

Dataset (15 Students)

---

Formatting Steps

1. Merge & Center title "Student Academic Record" across all columns → Home → Merge & Center
2. Title: Bold, Font Size 16, Background color (e.g., Light Blue)
3. Column Headings: Bold all headers
4. Column Width & Row Height: Double-click column border to auto-fit
5. Borders: Select entire table → Home → Borders → All Borders
6. Center-align: S.No, Class, and all Marks columns
7. Wrap Text: College Name column → Home → Wrap Text
8. Highlight marks > 75: Select marks range → Conditional Formatting → Highlight Cell Rules → Greater Than → 75 → Yellow Fill
9. Number formatting: Select marks → Format Cells → Number → 0 decimal places

Frequently asked questions

Is the Statistical Analysis using Excel Lab course really free?

Yes. The entire Statistical Analysis using Excel Lab course on Siksha Sarovar is free to read with no account required. You can optionally sign in with Google to save your progress.

Do I get a certificate for Statistical Analysis using Excel Lab?

Yes — finish the lessons and pass the quiz to earn a free, verifiable certificate you can share on LinkedIn or with recruiters.

Can I run code while learning?

Yes. The built-in online compiler runs C, C++, Python, Java, PHP, JavaScript, C# and SQL directly in your browser — no installation needed.