Advanced Microsoft Excel Formulas & Functions for Data Analysts and Business Professionals

Module 1: Foundation of Advanced Formulas

ü  Introduction to formula syntax and best practices

ü  Cell and range naming for clarity

ü  Relative vs. absolute vs. mixed references

ü  Understanding formula errors and how to fix them

ü  Nesting multiple functions

ü  Practical Session: Building a nested formula to calculate tiered commissions

 

Module 2: Mastering Logical and Text Functions

ü  The IF statement and its variations (IFS, AND, OR)

ü  Using SUMIF, COUNTIF, and AVERAGEIF functions

ü  Concatenating text with CONCAT and the ampersand

ü  Extracting text with LEFT, RIGHT, and MID

ü  Data cleaning with TRIM, LOWER, UPPER, and PROPER

ü  Practical Session: Cleaning a dataset with messy text and applying conditional logic

 

Module 3: Advanced LOOKUP Functions

ü  Limitations of VLOOKUP and an introduction to its alternatives

ü  Mastering XLOOKUP and its powerful features

ü  The combination of INDEX and MATCH for flexible lookups

ü  Using HLOOKUP for horizontal data retrieval

ü  Performing multi-criteria lookups

ü  Practical Session: Using XLOOKUP to find data in a large employee database

 

Module 4: Working with Dynamic Arrays

ü  Introduction to dynamic arrays and the "spill" feature

ü  SORT and SORTBY for dynamic sorting

ü  UNIQUE for extracting unique values from a range

ü  FILTER for dynamic data filtering

ü  RANDARRAY and SEQUENCE for generating dynamic lists

ü  Practical Session: Building a live, filtered list of sales data using FILTER and UNIQUE

 

Module 5: Data Validation and Conditional Formatting

ü  Setting up data validation rules to control data entry

ü  Creating dynamic drop-down lists

ü  Highlighting cells with conditional formatting based on rules

ü  Using formula-based rules for advanced conditional formatting

ü  Preventing invalid data with custom error alerts

ü  Practical Session: Designing a spreadsheet with data validation and conditional formatting to prevent entry errors

 

Module 6: Introduction to Power Query

ü  What is Power Query and why is it essential?

ü  The Power Query interface and its tools

ü  Importing data from different sources (Excel, CSV, web)

ü  The M language basics

ü  An overview of the ETL (Extract, Transform, Load) process

ü  Practical Session: Importing and combining data from multiple workbooks using Power Query

 

Module 7: Data Cleaning and Transformation with Power Query

ü  Unpivoting and pivoting data

ü  Splitting and merging columns

ü  Handling missing values and errors

ü  Creating custom columns with formulas

ü  Grouping and aggregating data

ü  Practical Session: Cleaning and transforming a messy sales report using a series of Power Query steps

 

Module 8: Data Modeling and Relationships

ü  The concept of a data model

ü  Creating relationships between tables

ü  Using relationships in PivotTables

ü  The DAX language basics

ü  Building a basic data model for analysis

ü  Practical Session: Creating a data model with two related tables and a PivotTable

 

Module 9: Advanced PivotTables and PivotCharts

ü  Creating complex PivotTables from multiple data sources

ü  Using calculated fields and items

ü  Grouping data by custom ranges and dates

ü  Creating dynamic PivotCharts

ü  Slicers and timelines for interactive analysis

ü  Practical Session: Building a comprehensive sales dashboard with Slicers and a PivotChart

 

Module 10: Using Array Formulas

ü  The concept of array formulas (CSE formulas)

ü  When to use array formulas

ü  Single-cell and multi-cell array formulas

ü  Using array formulas with SUM, AVERAGE, and other functions

ü  The future of array formulas with dynamic arrays

ü  Practical Session: Writing a complex array formula to calculate a weighted average

 

Module 11: Financial Functions

ü  Calculating loan payments with PMT

ü  Determining future value with FV

ü  Calculating present value with PV

ü  Finding the interest rate with RATE

ü  Depreciation and amortization calculations

ü  Practical Session: Building a loan amortization schedule using financial functions

 

Module 12: Statistical Functions

ü  Descriptive statistics with AVERAGE, STDEV, and MEDIAN

ü  Counting unique values with a combination of functions

ü  Using FREQUENCY to create histograms

ü  Calculating correlation and regression

ü  T.TEST and other statistical analysis functions

ü  Practical Session: Analyzing a dataset to find the most frequent sales amount and its standard deviation

 

Module 13: Date and Time Functions

ü  Calculating the difference between dates with DATEDIF

ü  Extracting parts of a date with DAY, MONTH, and YEAR

ü  Working with business days using WORKDAY and NETWORKDAYS

ü  Creating dynamic timelines and schedules

ü  Calculating age or duration in various units

ü  Practical Session: Creating a project timeline that automatically excludes weekends and holidays

 

Module 14: Engineering and Cube Functions

ü  Using functions for unit conversions

ü  Working with binary, decimal, and hexadecimal numbers

ü  Introduction to CUBE functions for pulling data from OLAP cubes

ü  Retrieving data from external sources

ü  Advanced data connection and refresh

ü  Practical Session: Connecting to an external data source and retrieving data using CUBE functions

 

Module 15: Auditing and Debugging Formulas

ü  The "Trace Precedents" and "Trace Dependents" tools

ü  The "Evaluate Formula" tool for step-by-step debugging

ü  Watching cell values with the "Watch Window"

ü  Formula-driven error checking

ü  Best practices for building clean and auditable spreadsheets

ü  Practical Session: Debugging a complex, multi-layered formula to find the source of an error

 

Module 16: What-If Analysis Tools

ü  Using Goal Seek to find a target value

ü  The Scenario Manager for comparing different scenarios

ü  The Data Table for analyzing how inputs affect outputs

ü  Solver add-in for optimization problems

ü  Combining What-If analysis with other functions

ü  Practical Session: Using Goal Seek to determine the sales target needed to reach a specific profit margin

 

Module 17: Collaboration and Data Protection

ü  Protecting worksheets and workbooks with passwords

ü  Sharing and co-authoring files with version control

ü  Tracking changes and adding comments

ü  Best practices for sharing spreadsheets with others

ü  Data privacy and security considerations

ü  Practical Session: Setting up a shared workbook with version tracking and cell protection

 

Module 18: Dashboard Design Principles

ü  The purpose of a dashboard

ü  Key elements of an effective dashboard

ü  Choosing the right chart type for your data

ü  Building an interactive dashboard with slicers and sparklines

ü  Storytelling with data

ü  Practical Session: Building a complete, professional dashboard from scratch using all the tools learned in the course

About Our Trainers

Our trainers are certified Microsoft Office Specialists with a minimum of 10 years of hands-on experience in corporate environments. They are not only experts in Excel but also experienced professionals who have applied these skills in real-world business, finance, and data analysis roles. They bring a practical, problem-solving approach to training, ensuring that you learn techniques you can immediately apply to your job.

 

Quality Statement

Phoenix Training Center is committed to providing a superior learning experience. Our curriculum is regularly updated to reflect the latest Excel features and industry trends. We maintain a low student-to-trainer ratio to ensure personalized attention and support. Our goal is to empower you with the skills and confidence to excel in your professional role.

Admission Criteria

ü  Participants should be reasonably proficient in English. 

ü  Applicants must live up to Phoenix Center for Policy, Research and Training 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@phoenixtrainingcenter.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 / +254737296202

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: Phoenix Center for Policy, Research and Training 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

We understand that every organization has unique challenges and opportunities as well as unique training needs. Phoenix Training Center offers tailor-made courses designed to address specific requirements and challenges faced by your team or organization. Whether you need a customized curriculum, a specific duration, or on-site delivery, we can adapt our expertise to provide a training solution that perfectly aligns with your objectives.

We can customize this Course to focus on your industry, specific risk profile, or internal stakeholder dynamics. Contact us to discuss how we can create a bespoke training program that maximizes value and impact for your team. For further inquiries, please contact us on Tel: +254720272325 / +254737296202 or Email training@phoenixtrainingcenter.com

 

Accommodation and Airport Pick-up

For physical training attendees, we can assist with recommendations for accommodation near the training venue. Airport pick-up services can also be arranged upon request to ensure a smooth arrival. Please inform us of your travel details in advance if you require these services. For reservations contact the Training Officer on Email: training@phoenixtrainingcenter.com or on Tel: +254720272325 / +254737296202