Excel Skills for Business: Intermediate II Course Syllabus

Overview: This course builds on intermediate Excel skills, guiding learners through essential techniques for data validation, logical functions, lookups, formula auditing, data modeling, and macro automation. With a total time commitment of approximately 28 hours, the course combines hands-on exercises with real-world applications, enabling professionals to enhance data accuracy, automate workflows, and support decision-making. Modules are designed for flexible learning, ideal for working professionals seeking to strengthen their analytical capabilities in business environments.

Module 1: Data Validation

Estimated time: 5 hours

• Apply data validation rules to restrict data entry
• Use custom formulas in data validation
• Implement drop-down lists for consistent input
• Apply conditional formatting to highlight data patterns

Module 2: Conditional Logic

Estimated time: 4 hours

• Use IF, AND, and OR functions for logical tests
• Build nested IF statements for complex conditions
• Handle errors using IFERROR and ISERROR functions
• Combine logical functions with conditional formatting

Module 3: Automating Lookups

Estimated time: 4 hours

• Retrieve data using VLOOKUP and HLOOKUP
• Implement INDEX and MATCH for flexible lookups
• Nest INDEX and MATCH to replace VLOOKUP
• Use CHOOSE to select values from a list

Module 4: Formula Auditing and Protection

Estimated time: 4 hours

• Trace precedents and dependents to audit formulas
• Use error checking tools to identify formula issues
• Protect worksheets and workbooks to secure data
• Apply password protection to specific ranges

Module 5: Data Modelling

Estimated time: 4 hours

• Use Goal Seek for reverse what-if analysis
• Create one-variable and two-variable data tables
• Apply Scenario Manager to compare forecasting models

Module 6: Recording Macros

Estimated time: 4 hours

• Record macros to automate repetitive tasks
• Edit macro properties and assign shortcuts
• Manage and debug simple VBA macros

Module 7: Final Assessment

Estimated time: 3 hours

• Apply data validation and conditional formatting
• Integrate logical and lookup functions in a dataset
• Use auditing tools and protect final output

Prerequisites

• Familiarity with basic Excel functions and interface
• Understanding of formulas and cell references
• Previous experience entering and formatting data in spreadsheets

What You'll Be Able to Do After

• Implement data validation and conditional formatting to improve data integrity
• Apply logical functions to automate decision-making in spreadsheets
• Retrieve data efficiently using advanced lookup functions
• Diagnose and correct formula errors using auditing tools
• Automate repetitive tasks using macros and enhance workflow efficiency