TRAIENT Experience
HRD Corp Claimable

Microsoft Excel: Intermediate & Advanced Level with AI Tools

A TRAIENT training session in progress
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.

WhatsApp for more info