Microsoft Excel Intermediate for Professional Success

0900-1300

1. Pre-Course Assessment
2. Course Overview
3. Ice Breaker Activities

Lesson 1 – Advanced Formulas and Functions

• IF statements, nested functions, and logical functions.
• Introduction to VLOOKUP and HLOOKUP.

Learning Outcome

• Participants will be able to construct logical formulas and retrieve data dynamically.

Topics Covered

• Logical Functions: IF, AND, OR
• Nested IF Statements
• Error Handling: IFERROR
• Lookup Functions: VLOOKUP, HLOOKUP
• Introduction to XLOOKUP (if Excel version supports)
• Combining functions (e.g., IF + VLOOKUP

Lesson 2 – Data Sorting and Filtering Technique

• Sorting data based on multiple criteria.
• Applying advanced filters for specific data subsets.

Learning Outcome

• Participants will organize and extract meaningful subsets from large datasets.

Topics Covered

• Multi-level Sorting (by department, date, amount)
• Custom Sorting (e.g., priority levels)
• Basic vs Advanced Filter
• Filtering using multiple criteria
• Using Filter with wildcard characters
• Removing duplicates

1300-1400 Lunch Break

1400-1700

Lesson 3 – Conditional Formatting for Data Visualization

• Using conditional formatting for highlighting data trends.
• Creating data bars, colour scales, and icon sets.

Learning Outcome

• Participants will visually highlight trends, risks, and performance indicators.

Topics Covered

• Highlight Cells Rules
• Top/Bottom Rules
• Data Bars
• Color Scales
• Icon Sets (Traffic lights, arrows)
• Custom Conditional Formatting using formulas
• Managing & Editing Rules

Lesson 4 -Introduction to PivotTables*

Creating basic PivotTables for data summarization.

Applying filters and formatting to PivotTables.

Learning Outcome

• Participants will summarize large datasets into meaningful reports.

Topics Covered

• Creating PivotTables from raw data
• Understanding Rows, Columns, Values, Filters
• Summarizing data (Sum, Count, Average)
• Grouping data (by month, quarter)
• Sorting & filtering inside PivotTables
• Formatting PivotTables professionally
• Refreshing PivotTables
• Introduction to Pivot Charts

0900-1300

Lesson 5 - Data Validation and Accuracy

• Implementing data validation rules for consistent data entry.
• Using dropdown lists and custom validation criteria.

Learning Outcome

Participants will design controlled data-entry systems to minimize errors.

Topics Covered

• Creating Dropdown Lists
• List Validation from Tables
• Whole number & date restrictions
• Custom validation using formulas
• Input Messages & Error Alerts
• Preventing duplicate entries
• Dependent Dropdown Lists (basic)

Lesson 6: Advanced Charting and Graphs

Creating advanced charts (combo charts, sparklines).

Customizing charts for better data representation.

Learning Outcome

Participants will design controlled data-entry systems to minimize errors.

Topics Covered

• Creating Dropdown Lists
• List Validation from Tables
• Whole number & date restrictions
• Custom validation using formulas
• Input Messages & Error Alerts
• Preventing duplicate entries
• Dependent Dropdown Lists (basic)

1300-1400 Lunch Break

1400-1700

Lesson 7: Tips for Efficient Data Entry and Navigation

Time-saving data entry shortcuts.

Navigational tips and tricks for large datasets.

Learning Outcome

Participants will significantly reduce time spent on repetitive Excel tasks.

Topics Covered

• Essential Keyboard Shortcuts
• Flash Fill
• Freeze Panes
• Go To Special
• Format Painter
• Tables (Ctrl + T)
• Quick Analysis Tool
• AutoFill techniques
• Working with large datasets efficiently

Lesson 8: Collaboration and Sharing Features

Sharing workbooks and collaborating on Excel files.

Protecting worksheets and workbooks for data security.

Learning Outcome

Participants will securely share and collaborate on Excel files.

Topics Covered

• Sharing Workbooks via OneDrive / SharePoint
• Comments and Notes
• Track Changes (where applicable)
• Protecting Sheets & Workbooks
• Locking cells
• Setting passwords
• Version history
• Exporting to PDF

Wrap-up and Conclusion