Microsoft Excel: Complete In-Depth Master Notes
Master Microsoft Excel from basic spreadsheet concepts, ribbon interface anatomy, cell referencing, data cleaning, advanced functions (VLOOKUP, XLOOKUP, INDEX-MATCH, SUMIFS), Pivot Tables, data visualization, What-If Analysis, to 5 real-world business projects.
Module 1: Introduction to Microsoft Excel & UI Interface Anatomy
What is Microsoft Excel?
Microsoft Excel is a powerful electronic spreadsheet application developed by Microsoft. It organizes data into rows and columns, allowing users to perform complex mathematical calculations, data analysis, financial modeling, auditing, and visual charting.
Core Applications of Excel
- Data Analysis & Analytics: Sorting, filtering, aggregating large datasets.
- Financial Modeling & Accounting: Budgets, income statements, cash flows.
- Business Operations: Inventory tracking, sales reporting, project timelines.
Anatomy of the Excel User Interface (UI)
Microsoft Excel - Book1.xlsx
By Instructor
Home
Insert
Page Layout
Formulas
Data
Review
View
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Sales | Target | Status |
| 2 | Laptop | 1200 | 1000 | Passed |
| 3 | Mouse | 450 | 500 | Pending |
- Title Bar: Displays current workbook name (e.g.,
Book1.xlsx). - Ribbon & Tabs: Contains tabs (Home, Insert, Formulas, Data) hosting tools organized into logical groups.
- Quick Access Toolbar (QAT): Customizable bar for quick one-click commands (Save, Undo, Redo).
- Name Box: Displays the cell coordinate of the active selection (e.g.,
B2). - Formula Bar: Displays data, text, or formulas entered into the currently active cell.
- Worksheet Grid: The grid consisting of columns (letters A-Z, AA-XFD) and rows (numbers 1-1,048,576).
- Status Bar: Displays operational status and quick metrics (Average, Count, Sum of selected cells).
Module 2: Workbook Fundamentals & Cell Referencing
Every Excel file is called a Workbook, which contains one or more individual Worksheets (tabs). The grid consists of exactly 1,048,576 Rows and 16,384 Columns (ending at column XFD).
Types of Cell References (The Dollar Sign $ Rule)
| Reference Type | Syntax | Behavior when Copied Across Cells |
|---|---|---|
| Relative Reference | A1 |
Both column and row change relative to the target copy position. |
| Absolute Reference | $A$1 |
Both column and row remain completely fixed. Dollar sign freezes position. |
| Mixed (Fixed Row) | A$1 |
Column A changes when copied horizontally; row 1 remains fixed. |
| Mixed (Fixed Column) | $A1 |
Column A remains fixed; row 1 changes when copied vertically. |
Excel Formula Syntax - Relative vs Absolute Referencing
Relative Formula: =B2 * C2 Absolute Formula (Tax Rate in Cell E1): =B2 * $E$1 Mixed Formula (Multiplication Table Header): =$A2 * B$1
Module 3: Data Formatting & Conditional Rules
Number Formatting Types
- General: Default unformatted text or number.
- Currency / Accounting: Adds currency symbols ($) and aligns decimals.
- Percentage: Multiplies cell value by 100 and adds % sign.
- Custom Format: e.g.,
+00 (000) 000-0000for phone numbers.
Conditional Formatting Rules
Automatically changes cell fill color, font, or icon based on underlying cell value logic.
- Highlight Cell Rules: Greater than, Less than, Between, Equal to.
- Top/Bottom Rules: Top 10 items, Top 10% items, Above Average.
- Data Bars & Color Scales: Visual gradient bars inside cells.
Module 4: Essential Excel Formulas & Functions
All formulas in Microsoft Excel MUST begin with an equals sign =.
| Category | Function Name | Syntax & Description |
|---|---|---|
| Math | SUM |
=SUM(range) → Adds all numbers in range. |
| Math | ROUND |
=ROUND(number, num_digits) → Rounds number to decimals. |
| Stats | AVERAGE |
=AVERAGE(range) → Returns mean average of numbers. |
| Stats | COUNT / COUNTA |
COUNT (numeric cells only), COUNTA (non-empty cells). |
| Text | CONCAT / TEXTJOIN |
=TEXTJOIN(delimiter, ignore_empty, range) → Joins text string with delimiter. |
| Text | LEFT / RIGHT / MID |
=MID(text, start_num, num_chars) → Extracts sub-text. |
| Text | TRIM / CLEAN |
=TRIM(text) → Removes leading, trailing, and extra spaces. |
Excel Formula Examples - Mathematical & Text Operations
Sum Sales Range: =SUM(B2:B100)
Average Test Scores: =AVERAGE(C2:C50)
Extract First Name (Left of Space): =LEFT(A2, FIND(" ", A2) - 1)
Capitalize Text Properly: =PROPER(TRIM(A2))
Module 5: Logical Functions & Conditional Aggregation
Logical Decision Functions
=IF(logical_test, value_if_true, value_if_false)
=IFS(test1, val1, test2, val2, ...) (Evaluates multiple conditions in order).
=IFERROR(value, value_if_error) (Suppresses unsightly errors like DIV/0 Error or N/A Error).
Conditional Aggregation
=SUMIF(range, criteria, [sum_range])
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
=COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2)
Excel Formula Examples - Logical & Conditional Calculations
Grade Evaluation: =IF(A2>=90, "A", IF(A2>=80, "B", IF(A2>=70, "C", "F"))) IFS Modern Grade: =IFS(A2>=90, "A", A2>=80, "B", A2>=70, "C", TRUE, "F") Safe Division: =IFERROR(A2 / B2, 0) Sum Sales for North Region: =SUMIF(RegionRange, "North", SalesRange) Multi-Condition Sales: =SUMIFS(SalesRange, RegionRange, "North", YearRange, 2026)
Formula Output Visualization
Grade Evaluation: A
Safe Division (when B2 is 0): 0
Multi-Condition Sales (North, 2026): $45,000
Safe Division (when B2 is 0): 0
Multi-Condition Sales (North, 2026): $45,000
Module 6: Lookup & Reference Functions (VLOOKUP, INDEX-MATCH, XLOOKUP)
| Lookup Function | Syntax Formula | Key Capabilities & Limitations |
|---|---|---|
| VLOOKUP | =VLOOKUP(lookup_val, table_array, col_index_num, [range_lookup]) |
Searches first column only. Cannot lookup data to the left. Requires column index number. |
| INDEX + MATCH | =INDEX(return_range, MATCH(lookup_val, lookup_range, 0)) |
Lookups to the left and right. Dynamic column changes do not break formula. Faster performance. |
| XLOOKUP | =XLOOKUP(lookup_val, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
Modern replacement for VLOOKUP. Defaults to exact match, lookups left/right, handles missing values inline. |
Excel Formula Examples - Lookup Comparisons
VLOOKUP (Exact Match): =VLOOKUP("EMP101", A2:D100, 3, FALSE)
INDEX-MATCH (Left Lookup): =INDEX(A2:A100, MATCH("EMP101", C2:C100, 0))
XLOOKUP (Modern Left Lookup with Fallback): =XLOOKUP("EMP101", C2:C100, A2:A100, "Employee Not Found")
Module 7: Date & Time Functions
Excel stores dates as sequential serial numbers starting from 1 (which represents January 1, 1900).
| Function | Syntax | Description & Use Case |
|---|---|---|
TODAY() |
=TODAY() |
Returns current system date (updates automatically). |
NOW() |
=NOW() |
Returns current system date and timestamp. |
EOMONTH |
=EOMONTH(start_date, months) |
Returns last day of the month before or after specified months. |
DATEDIF |
=DATEDIF(start_date, end_date, "Y") |
Calculates difference in Years ("Y"), Months ("M"), or Days ("D"). Useful for age calculation. |
NETWORKDAYS |
=NETWORKDAYS(start_date, end_date, [holidays]) |
Calculates net working days excluding weekends (Sat/Sun) and optional holidays. |
Module 8: Data Tools, Validation & Filtering Techniques
Data Validation Dropdown Lists
Restricts data entry into cells. Path: Data Tab → Data Validation → Allow: List → Source: Dept1, Dept2, Dept3.
Data Cleaning Tools
- Text to Columns: Splits full names or CSVs by delimiters (comma, space).
- Remove Duplicates: Scans rows and removes redundant duplicate entries.
- Flash Fill (Ctrl + E): AI pattern recognition to auto-extract or format text.
Module 9: Pivot Tables & Interactive Slicers
A Pivot Table is Excel's most powerful data summarization tool. It allows you to transform thousands of rows of unorganized raw data into summarized reports within seconds without writing formulas.
The 4 Quadrants of a Pivot Table Field List
- Filters (Report Filter): Filters the entire pivot table report based on selected criteria.
- Columns: Displays field categories as vertical column headers.
- Rows: Displays field categories as horizontal row labels.
- Values: Contains numeric fields to aggregate (Sum, Average, Count, Max, Min).
Pivot Table Summary Report - Regional Sales
| Row Labels | Sum of Sales | Average Discount | Count of Orders |
|---|---|---|---|
| East | $145,000 | 10% | 320 |
| North | $210,000 | 12% | 450 |
| South | $98,000 | 8% | 210 |
| West | $175,000 | 15% | 380 |
| Grand Total | $628,000 | 11.25% | 1,360 |
Module 10: Data Visualization & Charting Types
| Chart Type | Best Use Case Scenario |
|---|---|
| Column / Bar Chart | Comparing discrete categories or discrete items across groups. |
| Line Chart | Displaying continuous trends over time (months, quarters, years). |
| Pie / Doughnut Chart | Showing relative proportions or percentages of a total (best with 5 or fewer slices). |
| Scatter Plot (X,Y) | Analyzing relationships or correlations between two continuous numeric variables. |
| Waterfall Chart | Visualizing financial income statements showing cumulative gains and losses. |
Module 11: Advanced What-If Analysis & Excel Power Tools
What-If Analysis Tools
- Goal Seek: Back-calculates the required input value to achieve a specific target output cell.
- Data Tables: Sensitivity analysis tool evaluating 1-variable or 2-variable inputs.
- Scenario Manager: Creates and saves multiple sets of input scenarios (Best Case, Worst Case, Base Case).
Excel Power Tools & Automation
- Power Query (Get & Transform): ETL tool to import, clean, unpivot, and transform external data.
- Power Pivot & DAX: Data modeling tool connecting multiple tables using relational keys.
- VBA & Macros: Automates repetitive manual workflow tasks using Visual Basic for Applications scripts.
Module 12: Keyboard Shortcuts Cheat Sheet & Best Practices
| Shortcut Key | Action Performed |
|---|---|
Ctrl + A |
Selects entire worksheet or active data table. |
Ctrl + C / Ctrl + V |
Copy / Paste selection. |
Ctrl + Z / Ctrl + Y |
Undo / Redo previous action. |
Ctrl + F / Ctrl + H |
Find / Replace dialog. |
F4 |
Toggles absolute/relative cell reference ($) while editing formula, or repeats last action. |
Alt + = |
AutoSum selected adjacent cell numbers. |
Ctrl + Shift + L |
Toggles AutoFilter arrows on header row. |
Ctrl + T |
Converts range into an official formatted Excel Data Table. |
Ctrl + E |
Triggers Flash Fill automatic pattern recognition. |
Ctrl + 1 |
Opens Format Cells dialog window. |
Module 13: 5 Real-World Practical Business Projects
Project 1: Employee Payroll & Tax Calculator System
Payroll Calculation Table
| Emp ID | Name | Basic Salary | HRA (20%) | Gross Pay | Tax (10%) | Net Pay |
|---|---|---|---|---|---|---|
| EMP101 | Aman Sharma | $50,000 | $10,000 | $60,000 | $6,000 | $54,000 |
| EMP102 | Priya Patel | $65,000 | $13,000 | $78,000 | $7,800 | $70,200 |
Project 1 Formula Implementation
HRA Formula: =C2 * 0.20 Gross Pay Formula: =C2 + D2 Tax Formula: =IF(E2>50000, E2 * 0.10, 0) Net Pay Formula: =E2 - F2
Project 2: Sales Performance & Commission Dashboard
Project 2 Formula Implementation
Total Sales: =SUM(C2:C50) Commission Tier Formula: =IFS(C2>=100000, C2*0.10, C2>=50000, C2*0.05, TRUE, 0) Target Status: =IF(C2>=D2, "Target Met", "Below Target") Top Performer Lookup: =XLOOKUP(MAX(C2:C50), C2:C50, A2:A50)
Project 3: Financial Income Statement & Profit Margin Analyzer
Project 3 Formula Implementation
Gross Profit: =TotalRevenue - CostOfGoodsSold Operating Expenses: =SUM(E2:E10) Net Profit Before Tax: =GrossProfit - OperatingExpenses Net Profit Margin %: =NetProfit / TotalRevenue
Project 4: Inventory Tracking & Stock Reorder Alert System
Project 4 Formula Implementation
Current Stock Value: =UnitsInStock * UnitCost Reorder Status Alert: =IF(UnitsInStock <= ReorderPoint, "REORDER NOW", "Stock Sufficient") Days of Supply Remaining: =UnitsInStock / AverageDailySales
Project 5: Interactive Student Grade & Rank Evaluation System
Project 5 Formula Implementation
Total Score: =SUM(C2:G2) Percentage Marks: =AVERAGE(C2:G2) Final Letter Grade: =IFS(I2>=90, "A+", I2>=80, "A", I2>=70, "B", I2>=60, "C", TRUE, "F") Student Class Rank: =RANK.EQ(H2, H$2:H$50, 0)