Advanced Level Data Analytics

Microsoft Excel Training

Equips professionals with the skills to handle complex datasets, perform advanced analysis, and generate impactful insights. Through practical, hands-on modules, participants build mastery in Excel’s powerful functions, pivot tables, data visualization, and analytics tools to support evidence-based decision-making

Objective: Empower participants with deep Excel knowledge for professional data analysis, Visualization, and optimization.

Course Fee

  • Individuals UGX 300,000
  • Group of 3 People at 10% discount UGX 800,000
  • Duration: 5 weeks (12-15 sessions, 90 Mins/session)
  • Time: Week Days 7:30PM – 9:00PM and Weekends 1:00PM – 2:30PM
  • Meeting: 2-3 times a week
  • Requirements: Computer/Laptop, Microsoft Excel 2016+ Version/Office 365, Internet
Module 1: Advanced Formulas & Functions

Outcome: Build and apply advanced formulas using logical, lookup, conditional, text, and date functions to solve business problems.

  • Logical & Decision Functions: IF, IFS, AND, OR, SWITCH, IFERROR
  • Lookup & Reference: VLOOKUP, HLOOKUP, XLOOKUP, INDEX + MATCH
  • Conditional Aggregates: SUMIF(S), COUNTIF(S), AVERAGEIF(S), MAXIFS, MINIFS
  • Text Functions: LEFT, RIGHT, MID, LEN, TRIM, TEXT, CONCAT, TEXTJOIN
  • Date & Time: TODAY(), NOW(), DATEDIF, NETWORKDAYS, WORKDAY

Outcome: Use advanced filtering, sorting, validation, and formatting tools to dynamically control and analyze structured data.

2.1 Advanced Sorting

  • Custom sort orders (e.g., Jan–Dec)
  • Sort by color/font/icon
  • Multi-level sorting (e.g., Region → Sales)
  • Row-wise sort (Left-to-Right)

2.2 Advanced Filtering

  • Number, text, and date filters
  • Filter by color and conditional formatting
  • Advanced Filter tool: Criteria range, output range

2.3 Conditional Formatting (Advanced)

  • Use formulas to trigger custom rules
  • Color scales, icon sets, data bars for visuals
  • Highlight duplicates, outliers, thresholds

2.4 Advanced Data Validation

  • Dynamic dropdowns using INDIRECT
  • Restricting inputs with formulas (e.g., only weekdays)
  • Input messages and custom error alerts

2.5 Named Ranges

  • Static vs Dynamic named ranges (OFFSET, INDEX)
  • Use in formulas and data validation
  • Manage names for auditing

Outcome: Perform multi-level data analysis and create interactive reporting dashboards.

Topics:

  • Creating and customizing PivotTables
  • Grouping by date, number, or custom categories
  • Value field settings: summarize by, show values as
  • Calculated Fields and custom formulas in Pivot
  • Pivot Charts: combo charts, slicers, timelines
  • Drill-down and interactivity features

Outcome: Build professional dashboards with dynamic charts and interactive components.

Topics:

  • Combo charts, dual-axis charts
  • Sparklines for trend lines in rows
  • KPI visuals (gauges, bullet charts via bar/column tweaks)
  • Use of form controls (dropdowns, checkboxes) to filter visuals
  • Dashboard design tips: layout, interactivity, performance

Outcome: Protect your files, control user access, and boost workbook performance.

Topics:

5.1 Workbook & Worksheet Protection

  • Locking/unlocking cells before protection
  • Password-protecting sheets, ranges, and files
  • Restricting structure changes (adding, deleting sheets)
  • Mark as Final, Read-only modes

5.2 Performance Optimization

  • Minimize volatile functions (INDIRECT, OFFSET, NOW)
  • Efficient use of Tables and named ranges
  • Replacing array formulas with modern dynamic arrays
  • Using helper columns for speed
  • Best practices for large datasets (avoid whole-column references, reduce conditional formatting)

5.3 Workbook Clean-up & Review

  • Remove unused names, links, or formulas
  • Trace and eliminate circular references
  • Use of “Inquire” add-in (if available)
  • Practical Assessment
  • Certificate of Completion
Apply Now