Microsoft Excel Training Notes (Basic to Advanced) | Hands-On Guide 2026 tutorial, Excel formulas, VLOOKUP, Pivot Tables, Excel Dashboard, Data Validation, Excel training notes, Basic to Advanced

📊 Microsoft Excel Training Notes

Basic to Advanced | Hands-on Examples & Step-by-Step Exercises

📚 About this guide: A complete, structured Microsoft Excel course progressing from beginner to advanced levels. Covers formulas, cell references, VLOOKUP/XLOOKUP, Pivot Tables, Data Validation, and Interactive Dashboards. Perfect for a 20–30 hour instructor-led training or self-paced learning with practical exercises after every topic.

Module 1

Introduction to Excel

What is Excel? Spreadsheet software used to store data, perform calculations, analyze information, and build dashboards.

Key Interface Parts: Workbook (file), Worksheet (sheet), Row (horizontal, 1,2,3), Column (vertical, A,B,C), Cell (intersection), Formula Bar, Ribbon, Name Box.

🛠️ Hands-on Exercise 1 Create a Student Marks Sheet with columns: Student, Maths, Science, English. Enter sample data and save as Student_Marks.xlsx.

Module 2

Formatting

Learn to make data professional: Font styles, Borders, Fill Color, Text Color, Merge & Center, Wrap Text, and Alignment.

🛠️ Hands-on Exercise Format a "Product & Price" table: Blue heading, white text, bold, borders, and apply Currency Format to prices.

Module 3

Basic Formulas

Rule: Every formula starts with =.

Operator	Meaning	Example
+	Add	=A2+B2
-	Subtract	=A2-B2
*	Multiply	=A2*B2
/	Divide	=A2/B2
^	Power	=A2^2

Module 4

Common Functions

• =SUM(B2:B10) → Total of a range.
• =AVERAGE(B2:B10) → Mean value.
• =MAX(B2:B10) → Highest value.
• =MIN(B2:B10) → Lowest value.
• =COUNT(B2:B10) → Counts cells with numbers.
• =COUNTA(B2:B10) → Counts non-empty cells.

Module 5

Cell References

• Relative (A2): Changes automatically when copied.
• Absolute ($A$2): Never changes (locks row & column). Use for fixed rates like VAT.
• Mixed (A$2 or $A2): Locks either the row or the column only.

🛠️ Hands-on Exercise Calculate VAT (18%). Price in A2, VAT Rate in $D$1. Formula: =A2*$D$1

Module 6

AutoFill

Use the small square at the bottom-right of a cell to drag and automatically fill numbers, months, days, or copy formulas.

🛠️ Hands-on Exercise Type "January" in a cell and drag the fill handle down. Observe how Excel automatically fills February, March, etc.

Module 7

Sorting & Filtering

Sort: A-Z, Z-A, Smallest to Largest, Largest to Smallest.
Filter: Temporarily hide data to view specific criteria (e.g., show only "IT Department" or "Salary > 50000").

Module 8

Conditional Formatting

Automatically highlight cells based on rules: Duplicate values, Greater/Less than, Top 10, or Color Scales.

🛠️ Hands-on Exercise Select a column of student marks. Apply Conditional Formatting to highlight all marks greater than 75 in green.

Module 9

IF Function

Syntax: =IF(condition, value_if_true, value_if_false)

Example: =IF(B2>=50, "Pass", "Fail")

🛠️ Hands-on Exercise If a student's mark is >75, display "Excellent". Otherwise, display "Average".

Module 10

AND & OR Functions

• =AND(A2>50, B2>50) → True only if BOTH conditions are met.
• =OR(A2>50, B2>50) → True if AT LEAST ONE condition is met.

Module 11

Date Functions

• =TODAY() → Current date.
• =NOW() → Current date and time.
• =YEAR(A2), =MONTH(A2), =DAY(A2) → Extract specific parts of a date.

Module 12

Text Functions

• =LEFT(A2,3) → Extracts first 3 characters.
• =RIGHT(A2,2) → Extracts last 2 characters.
• =MID(A2,2,5) → Extracts 5 characters starting from position 2.
• =LEN(A2) → Counts total characters.
• =TRIM(A2) → Removes extra spaces.
• =UPPER(), =LOWER(), =PROPER() → Change text case.

🛠️ Hands-on Exercise Convert "john smith" into "John Smith" using the =PROPER() function.

Module 13

Lookup Functions

• VLOOKUP: =VLOOKUP(lookup_value, table_array, col_index_num, FALSE). Searches vertically.
• XLOOKUP (Excel 365): =XLOOKUP(lookup_value, lookup_array, return_array). More flexible, searches any direction.
• HLOOKUP: Searches horizontally across the first row.

Module 14

Data Validation

Restrict what can be entered into a cell. Common uses: Drop-down lists, number limits, date limits.

🛠️ Hands-on Exercise Create a drop-down list for "Department" with options: Sales, HR, Finance, IT. (Data Tab → Data Validation → List).

Module 15

Charts

Visualize data using Bar Charts, Pie Charts, Line Charts, and Column Charts. Select data → Insert Tab → Choose Chart.

Modules 16-19

Pivot Tables, Charts & Tables

• Pivot Tables: Summarize large datasets instantly (Insert → Pivot Table). Drag fields to Rows, Columns, and Values.
• Pivot Charts: Visual representation of a Pivot Table.
• Excel Tables: (Ctrl+T) Provides automatic formatting, built-in filters, and structured references.
• Freeze Panes: (View → Freeze Panes) Keeps headers visible while scrolling.

Modules 20-22

Protection, Named Ranges & What-If

• Protect Sheet: (Review → Protect Sheet) Prevents unauthorized edits. Can set a password.
• Named Ranges: Assign a name (e.g., "Sales") to a range (e.g., A2:A20). Use =SUM(Sales) for readability.
• What-If Analysis: Goal Seek (find input for a desired output), Scenario Manager, Data Tables.

Modules 23-24

Advanced & Financial Functions

• INDEX & MATCH: Powerful alternative to VLOOKUP. =INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
• PMT: Calculate loan EMI. =PMT(rate, nper, pv)

Module 25 + Final Project

Interactive Sales Dashboard

Dataset: Date, Product, Region, Salesperson, Qty, Price.

Tasks:

1. Format as Table & Calculate Total Sales (=Qty*Price).
2. Apply Currency Format & Conditional Formatting (> Rs.100,000).
3. Create Data Validation Drop-downs & Sort/Filter.
4. Build Pivot Table (Sales by Region) & Pivot Chart.
5. Insert Slicers (Product, Region) for an interactive Dashboard.