EXCEL SYLLABUS 📊

1. EXCEL FROM SCRATCH

• Touring the Interface 🔍

• Entering Data & Saving Workbook 💾

• Applying Formulas ➕

• Changing a Worksheet’s Structure 🔧

• Find, Replace & Spell Check 🔎

• Applying Fonts, Background Colors, and Borders 🎨

• Formatting Numbers and Dates 📅

• Making the Pieces Fit 🧩

• Inserting Headers & Footers and Repeating Rows 📝

• Managing Large Workbooks 📚

• Quiz 1 ❓

2. EXCEL ESSENTIALS

• Formula Writing ✍️

• Exercise of Formula Writing (Don’t Miss This) ⚡

• Excel Files for Practice 💻

• Quiz 2 ❓

3. SUM, COUNT, AVERAGE FUNCTIONALITY (WITH CONDITIONS)

• SumIf & SumIfs ➕

• Exercise of SumIf & SumIfs 📝

• Count Functions 🔢

• Exercise of Count Functions 📝

• AverageIf & AverageIfs 📊

• Exercise of AverageIf & AverageIfs 📝

• Excel Files for Practice 💻

4. TEXT FUNCTIONS (MANIPULATING TEXT)

• Change Case (Upper, Lower, Proper) 🔠

• Exercise of Change Case (Upper, Lower, Proper) 📝

• Separating Text on Symmetrical Data ✂️

• Exercise of Separating Text on Symmetrical Data 📝

• Separating Text on Non-Symmetrical Data ✂️

• Exercise of Separating Text on Non-Symmetrical Data 📝

• Excel Files for Practice 💻

5. LOGICAL FUNCTIONS (WORKING WITH CONDITION)

• If Functionality 🔀

• Exercise of If Functionality 📝

• If with AND ➕

• Exercise of If with AND 📝

• If with OR 🔀

• Exercise of If with OR 📝

• If with AND & OR, IF with Countif 🔄

• Exercise of If with AND & OR, IF with Countif 📝

• Nested If (A Genie with 3 Wishes) 🧞‍♂️

• Exercise of Nested If 📝

• Excel Files for Practice 💻

6. LOOKUP FUNCTIONALITY (BURN SIMPLE AND TECHNIQUE)

• Vlookup (Exact Match) #1 🔍

• Exercise: Vlookup (Exact Match) #1 📝

• Type of References (Use of $ Sign) 💲

• Vlookup (Exact Match) #2 🔍

• Exercise: Vlookup (Exact Match) #2 📝

• Double Vlookup 🔍➕🔍

• Exercise: Double Vlookup 📝

• Vlookup on Duplicate Values 🔍

• Exercise: Vlookup on Duplicate Values 📝

• Vlookup (Approximate Match) 🔍~

• Exercise: Vlookup (Approximate Match) 📝

• Vlookup with IF (Conditional Vlookup) 🔍📝

• Exercise: Vlookup with IF (Conditional Vlookup) 📝

• Hlookup (Exact Match) 📊

• Exercise: Hlookup (Exact Match) 📝

• Hlookup (Approximate Match) 📊~

• Exercise: Hlookup (Approximate Match) 📝

• Vlookup & Match (Create Magical Vlookup) ✨

• Exercise: Vlookup & Match (Create Magical Vlookup) 📝

• Match (Gives us Column & Row Number) 📏

• Exercise: Match (Gives us Column & Row Number) 📝

• Index & Match (Made for Each Other) 🔗

• Exercise: Index & Match (Made for Each Other) 📝

• Index & Match (Revisited) 🔗

• Exercise: Index & Match (Revisited) 📝

• Excel Files for Practice 💻

7. DATE & TIME FUNCTION (A TRICKY GAME)

• How Excel Records Date & Time ⏰

• Now, Today & Autofill 📆

• DateValue & TimeValue 🗓️

• Calculate Working Days 📅

• DateIf 🔄

• Excel Files for Practice 💻

8. FINANCIAL FUNCTIONS (USEFUL FOR NON-FINANCE GUYS TOO)

• Loan Calculation (PMT, PPMT, IPMT, etc.) 💸

• Exercise: Loan Calculation 📝

• Creating a Loan Table 📊

• Exercise: Creating a Loan Table 📝

• Calculate Depreciation 📉

• Exercise: Calculate Depreciation 📝

• Excel Files for Practice 💻

9. SORT & FILTER (COMMON FOR ALL)

• Basic Sorting & Shortcut Keys ↕️

• Exercise: Basic Sorting & Shortcut Keys 📝

• Sorting Using Custom List 🔢

• Exercise: Sorting Using Custom List 📝

• Horizontal Sorting ➡️

• Exercise: Horizontal Sorting 📝

• Basic Filter & Shortcut Keys 🔍

• Exercise: Basic Filter & Shortcut Keys 📝

• Filter Problem #1 (Copy Filtered Data Without Hidden Rows) 🚫

• Exercise: Filter Problem #1 📝

• Filter Problem #2 (Pasting Values on Filtered Data) 📋

• Exercise: Filter Problem #2 📝

• Filter Problem #3 (Performing Calculation on Filtered Data) 🔢

• Exercise: Filter Problem #3 📝

• Advanced Filter (Using Complex Criteria’s) ⚙️

• Exercise: Advanced Filter 📝

• Excel Files for Practice 💻

10. EXCEL CHARTS (IT'S TIME FOR VISUAL PRESENTATION)

• Excel Charts Introduction 📊

• How to Create a Chart 🎨

• Link a Chart Title 🔗

• How to Show Axis Title 📈

• How to Show Data Label 💡

• Column Chart 📊

• Bar Chart 📊

• Line Chart 1 📈

• Line Chart 2 📈

• Area Chart 📉

• Pie Chart 🥧

• Pie of Pie or Bar of Pie 🥧📊

• Line Chart with Log Scale 📈

• Bubble Chart 💬

• Selecting Chart Element 🖱️

• Use of Format Dialog Box ⚙️

• Modifying Data Series 🔄

• How to Handle Missing Data ❓

• Modifying 3-D Charts 🌀

• Creating a Chart Template 📑

• Creating Picture Charts 🖼️

• Creating a Combination of up to 5 Different Charts 📊

• Show Data with Chart in Data Table 📋

• Play with Design & Layout of Chart 🎨

• Excel Files for Practice 💻

11. DATA CONSOLIDATION (MULTIPLE WORKBOOKS/WORKSHEETS)

• Consolidate Data using Excel Inbuilt Function 🔗

• Exercise: Consolidate Data 📝

• Excel Files for Practice 💻

12. DATA VALIDATION

• Applying Data Validation ✅

• Exercise: Applying Data Validation 📝

• Dependent Dropdown List 🔽

• Exercise 📝

• Using Data Validation with Vlookup & Match 🔍

• Other Data Validation Examples 💡

• Excel Files for Practice 💻

13. PROTECTION OF WORKSHEET & WORKBOOK

• Worksheet Level Protection 🔒

• Exercise 📝

• Cell Level Protection 🔐

• Exercise 📝

• Hiding Formulas from Formula Bar 👀

• Exercise 📝

• Protecting Workbook Structure 🏛️

• Exercise 📝

• Workbook Level Protection 🔒

• Excel Files for Practice 💻

14. PIVOT TABLE & TECHNIQUE

• Understanding Pivot Tables 📊

• Using Pivot Tables in Real Scenarios 💼

• Exercise 📝

• Grouping in Pivot Tables 🔢

• Exercise 📝

• Using Slicers (A Visually Attractive Filter) 🎨

• Exercise 📝

• Excel Files for Practice 💻

15. WHAT-IF ANALYSIS & SOLVER ADD-IN

• Goal Seek (A Reverse Approach) 🎯

• Exercise 📝

• One-way Data Table 📊

• Two-way Data Table 📊

• Scenario Manager 📝

• Solver Add-In 🔧

• Excel Files for Practice 💻