Module 1: Advanced Logical Functions

• Nested IF statements
• Using AND, OR, and NOT functions
• Advanced error handling with IFERROR
• Using SWITCH function for multiple conditions
• Combining logical functions with other formulas
• Practical session: Building a dynamic decision-making tool using nested IF and logical functions.

Module 2: Dynamic Array Formulas

• Understanding dynamic arrays
• Using SEQUENCE and RANDARRAY functions
• FILTER, SORT, and UNIQUE functions
• Advanced use of array formulas with spill ranges
• Combining dynamic array formulas with other functions
• Practical session: Creating a dynamic dashboard using dynamic array formulas.

Module 3: Advanced Text Functions

• Using REGEX functions (if available in your Excel version)
• Advanced text manipulation with FIND and SEARCH
• Combining text functions for complex data extraction
• Using TEXTJOIN and CONCAT functions
• Advanced data cleaning with text functions
• Practical session: Automating data cleaning using advanced text functions.

Module 4: Date and Time Functions

• Advanced date and time calculations
• Using WORKDAY and NETWORKDAYS functions
• Combining date and time functions with other formulas
• Creating dynamic date ranges
• Working with time zones
• Practical session: Building a project timeline using advanced date and time functions.

Module 5: Lookup and Reference Functions

• Advanced INDEX and MATCH combinations
• Using OFFSET and INDIRECT functions
• Advanced use of CHOOSE and VLOOKUP/HLOOKUP
• Using XLOOKUP for complex lookups
• Creating dynamic lookup tables
• Practical session: Building a dynamic product lookup system.

Module 6: Financial Functions

• Advanced NPV and IRR calculations
• Using PMT, IPMT, and PPMT functions
• Calculating depreciation with various methods
• Using FV and PV functions for complex financial models
• Building financial scenario analysis models
• Practical session: Creating a loan amortization schedule and financial model.

Module 7: Statistical Functions

• Advanced statistical analysis with TREND and FORECAST
• Using advanced aggregation functions like AGGREGATE
• Calculating confidence intervals and hypothesis testing
• Using statistical functions for data modeling
• Understanding and using descriptive statistics
• Practical session: Performing statistical analysis on sales data.

Module 8: Data Validation and Auditing

• Advanced data validation techniques
• Using conditional formatting for data validation
• Auditing formulas and data dependencies
• Using the Formula Auditing toolbar
• Error checking and troubleshooting
• Practical session: Implementing advanced data validation rules for a form.

Module 9: Power Query for Data Transformation

• Advanced data import and transformation using Power Query
• Merging and appending queries
• Advanced data cleaning and shaping
• Creating custom columns and calculations
• Automating data refresh
• Practical session: Transforming and cleaning data from multiple sources.

Module 10: PivotTables and PivotCharts

• Advanced PivotTable techniques
• Creating calculated fields and items
• Using slicers and timelines for interactive analysis
• Creating advanced PivotCharts
• Power Pivot and Data Modeling basics
• Practical session: Building an interactive sales dashboard using PivotTables and PivotCharts.

Module 11: Advanced Conditional Formatting

• Using formulas in conditional formatting
• Creating complex conditional formatting rules
• Using data bars, color scales, and icon sets
• Conditional formatting for data visualization
• Using conditional formatting with other functions
• Practical session: Visualizing sales performance using advanced conditional formatting.

Module 12: Macros and VBA Basics

• Recording and editing macros
• Understanding VBA syntax and structure
• Creating custom functions with VBA
• Automating repetitive tasks with VBA
• Basic error handling in VBA
• Practical session: Creating a macro to automate data formatting.

Module 13: Advanced VBA Programming

• Working with objects and collections in VBA
• Using loops and conditional statements in VBA
• Creating user forms and dialog boxes
• Advanced error handling and debugging in VBA
• Interacting with other applications using VBA
• Practical session: Building a user form for data input.

Module 14: Data Analysis ToolPak

• Using the Analysis ToolPak for advanced statistical analysis
• Performing regression analysis
• Using ANOVA and correlation analysis
• Generating random numbers and distributions
• Understanding and using histograms
• Practical session: Performing regression analysis on market data.

Module 15: Power Pivot Advanced Data Modeling

• Creating relationships between tables
• Using DAX formulas for advanced calculations
• Creating calculated columns and measures
• Advanced data modeling techniques
• Building interactive dashboards with Power Pivot
• Practical session: Building a data model for sales analysis.

Module 16: Advanced Charting Techniques

• Creating advanced chart types (e.g., waterfall, funnel)
• Using custom chart elements and formatting
• Creating dynamic charts with formulas
• Combining charts and tables for effective reporting
• Advanced data visualization principles
• Practical session: Creating a dynamic sales performance chart.

Module 17: Scenario Management and Goal Seek

• Using Scenario Manager for what-if analysis
• Using Goal Seek for target calculations
• Using Solver for optimization problems
• Creating data tables for sensitivity analysis
• Applying these tools to financial modeling
• Practical session: Using Solver to optimize a budget.

Module 18: Advanced Data Import and Export

• Importing data from various sources (e.g., CSV, TXT, databases)
• Exporting data to different formats
• Using web queries to import data from websites
• Automating data import and export with VBA
• Best practices for data integration
• Practical session: Importing and cleaning data from a web source.

 Requirements:

·       Participants should be reasonably proficient in English.

·       Applicants must live up to Armstrong Global Institute admission criteria.

Terms and Conditions

1. Discounts: Organizations sponsoring Four Participants will have the 5th attend Free

2. What is catered for by the Course Fees: Fees cater for all requirements for the training – Learning materials, Lunches, Teas, Snacks and Certification. All participants will additionally cater for their travel and accommodation expenses, visa application, insurance, and other personal expenses.

3. Certificate Awarded: Participants are awarded Certificates of Participation at the end of the training.

4. The program content shown here is for guidance purposes only. Our continuous course improvement process may lead to changes in topics and course structure.

5. Approval of Course: Our Programs are NITA Approved. Participating organizations can therefore claim reimbursement on fees paid in accordance with NITA Rules.

Booking for Training

Simply send an email to the Training Officer on training@armstrongglobalinstitute.com and we will send you a registration form. We advise you to book early to avoid missing a seat to this training.

Or call us on +254720272325 / +254725012095 / +254724452588

Payment Options

We provide 3 payment options, choose one for your convenience, and kindly make payments at least 5 days before the Training start date to reserve your seat:

1. Groups of 5 People and Above – Cheque Payments to: Armstrong Global Training & Development Center Limited should be paid in advance, 5 days to the training.

2. Invoice: We can send a bill directly to you or your company.

3. Deposit directly into Bank Account (Account details provided upon request)

Cancellation Policy

1. Payment for all courses includes a registration fee, which is non-refundable, and equals 15% of the total sum of the course fee.

2. Participants may cancel attendance 14 days or more prior to the training commencement date.

3. No refunds will be made 14 days or less before the training commencement date. However, participants who are unable to attend may opt to attend a similar training course at a later date or send a substitute participant provided the participation criteria have been met.

Tailor Made Courses

This training course can also be customized for your institution upon request for a minimum of 5 participants. You can have it conducted at our Training Centre or at a convenient location. For further inquiries, please contact us on Tel: +254720272325 / +254725012095 / +254724452588 or Email training@armstrongglobalinstitute.com

Accommodation and Airport Transfer

Accommodation and Airport Transfer is arranged upon request and at extra cost. For reservations contact the Training Officer on Email: training@armstrongglobalinstitute.com or on Tel: +254720272325 / +254725012095 / +254724452588