HRD Corp Claimable
Microsoft Excel: Intermediate & Advanced Level with AI Tools

- Duration
- 2 Days
- Modules
- 10
- Language
- English
- HRD Corp
- Claimable
Learning outcomes
- Validate and control data entry using Excel's Data Validation tools to ensure accuracy, flag errors, and guide users with custom alerts
- Format and visualize data trends by applying advanced conditional formatting rules and sparklines for immediate pattern recognition
- Manipulate and clean text and dates using built-in functions to extract, format, and prepare data for analysis and reporting
- Build logical and mathematical formulas that combine IF, AND, OR, COUNTIFS, SUMIFS, and AVERAGEIFS to perform targeted, criteria-based calculations
- Retrieve and summarize data dynamically using lookup functions (VLOOKUP, XLOOKUP, INDEX/MATCH), named ranges, and Pivot Tables with interactive charts and slicers
- Automate repetitive tasks by recording, applying, and assigning macros to buttons, streamlining workflows and enhancing productivity
Methodology
Interactive lectures
Hands-on exercises
Case studies
Group discussions
Course outline
Day 1
- Module 1: Data Validation
- Validating various data types
- Identify invalid data
- Creating alert messages and error messages
- Module 2: Conditional Formatting & Sparklines
- Advanced Highlight Cells Rules
- Conditional formatting the entire row
- Visualising trends with sparklines
- Module 3: Text Functions
- Text manipulation functions (LEFT, RIGHT, MID, CONCAT, LEN)
- Text formatting functions (UPPER, LOWER, PROPER)
- Text cleaning functions (TRIM, CLEAN)
- Module 4: Date Functions
- Date extraction functions (YEAR, MONTH, DAY)
- Date calculation functions (DATEDIF, NETWORKDAYS, WORKDAY)
- Date formatting functions (DATE, TEXT)
- Module 5: Named Cells & Named Ranges
- Introducing named cells and ranges
- Creating and managing named cells and ranges
- Using named cells and ranges in formulas
Day 2
- Module 6: Logical Functions
- Using and combining IF, AND, OR functions
- Nested and multiple IFs
- Handling errors (IFERROR, IFNA)
- Module 7: Mathematical Functions
- Counting cells with criteria (COUNTIFS)
- Adding cell values with criteria (SUMIFS)
- Averaging cell values with criteria (AVERAGEIFS)
- Module 8: Lookup Functions
- Range lookup with VLOOKUP
- Exact lookup with XLOOKUP
- Dynamic lookup with INDEX and MATCH
- Module 9: Pivot Tables & Pivot Charts
- Creating and modifying a Pivot Table
- Sorting, grouping and filtering a Pivot Table
- Pivot Charts and slicers
- Module 10: Macros
- Recording macros
- Applying macros
- Assigning macros to buttons
Run this programme for your team
Message us on WhatsApp to schedule it in person or online for your organisation.
