Microsoft Excel offers a broad range of functions for analysing, transforming, comparing, and summarising data. This course is designed for experienced Excel users who already work with formulas and want to expand their knowledge of functions for more advanced data analysis.

Throughout the course, participants will explore a wide selection of Excel functions and learn how to select, combine, and apply them to practical business scenarios. The training progresses from commonly used statistical and calculation functions to conditional calculations, dynamic arrays, advanced lookups, date and time analysis, text manipulation, and modern functions such as LET and LAMBDA.

The emphasis is not only on understanding individual functions, but also on learning how multiple functions can work together to solve more complex data analysis requirements.

For corporate groups, we can organize this training as an on-site private group.

Prerequisites

This course is intended for existing Excel users who are already comfortable working with formulas and structured datasets.

Participants should be able to:

Create, edit, and format Excel worksheets
Navigate between worksheets and workbooks
Create functions using the Insert Function feature
Work with absolute references such as $A$1
Create formulas using functions such as IF and VLOOKUP
Create and use named ranges
Create and work with Excel Tables
Sort and filter datasets

What You Will Learn

By the end of this course, participants will be able to:

Apply commonly used Excel functions to more advanced analytical scenarios
Select appropriate functions for different data analysis requirements
Combine and nest multiple functions within formulas
Perform calculations based on single or multiple criteria
Rank values and analyse datasets using quartiles and percentiles
Apply logical functions to create flexible decision-making formulas
Use advanced lookup and reference techniques, including XLOOKUP, INDEX, MATCH and XMATCH
Work effectively with Excel date, time, and text functions
Clean, transform, and restructure data using formulas
Understand and apply Dynamic Array functions
Use functions such as FILTER, UNIQUE, SORT and SEQUENCE to create dynamic results
Build more structured and reusable formulas using LET and LAMBDA
Develop more efficient formula-based solutions for analysing real-world Excel data

Training Outline

Module 1: Reviewing Excel Functions

Understanding the structure and purpose of functions
Reading and interpreting function syntax
Using named ranges within formulas
Referencing Excel Tables in calculations
Reviewing frequently used functions:
- SUM
- AVERAGE and AVERAGEA
- MEDIAN
- MODE and MODE.SNGL
- MIN and MAX
- COUNT, COUNTA and COUNTBLANK
Selecting the appropriate function for different types of calculations

Module 2: Ranking, Percentiles and Data Aggregation

Ranking and comparing values within datasets
Identifying smaller and larger values with SMALL and LARGE
Working with MINA and MAXA
Ranking values with RANK, RANK.AVG and RANK.EQ
Analysing data distribution using quartiles:
- QUARTILE
- QUARTILE.INC
- QUARTILE.EXC
Calculating percentiles with:
- PERCENTILE
- PERCENTILE.INC
- PERCENTILE.EXC
Summarising datasets with SUBTOTAL
Performing flexible calculations with AGGREGATE

Module 3: Controlling and Rounding Numeric Values

Understanding different approaches to rounding numbers
Using ROUND, ROUNDUP and ROUNDDOWN
Rounding values to specified multiples with MROUND
Applying CEILING.MATH and FLOOR.MATH
Removing or controlling decimal values with INT and TRUNC
Choosing the appropriate rounding technique for different scenarios

Module 4: Combining and Nesting Functions

Understanding nested formulas
Using one function as an argument within another
Combining multiple functions in a single formula
Building formulas for multi-stage calculations
Understanding and troubleshooting complex nested formulas

Module 5: Conditional Calculations

Performing calculations based on multiple criteria
Counting records with COUNTIFS
Calculating conditional totals with SUMIFS
Calculating conditional averages with AVERAGEIFS
Finding conditional minimum and maximum values with MINIFS and MAXIFS
Building criteria for practical data analysis scenarios

Module 6: Logical and Conditional Functions

Building logical calculations with IF
Handling multiple conditions with IFS
Managing formula errors with IFERROR and IFNA
Combining conditions using AND, OR and XOR
Selecting results with CHOOSE
Evaluating multiple possible outcomes with SWITCH
Combining logical functions for more sophisticated decision-making formulas

Module 7: Arrays and Dynamic Array Functions

Understanding arrays in Excel
Introduction to traditional array formulas
Understanding Dynamic Arrays and spilled results
Returning distinct values with UNIQUE
Dynamically sorting results with SORT
Sorting according to another range using SORTBY
Extracting records based on conditions with FILTER
Generating sequences with SEQUENCE
Rearranging data with TRANSPOSE
Returning multiple modes with MODE.MULT
Using Dynamic Arrays to simplify analytical formulas

Module 8: Lookup and Reference Functions

Understanding different approaches to data lookup
Traditional lookups using VLOOKUP and HLOOKUP
Modern and flexible lookups with XLOOKUP
Combining INDEX and MATCH
Using XMATCH to locate values and positions
Retrieving information from PivotTables with GETPIVOTDATA
Creating dynamic references with OFFSET
Building references from text using INDIRECT
Displaying formulas with FORMULATEXT
Choosing the appropriate lookup technique for different datasets

Module 9: Date and Time Analysis

Understanding how Excel stores dates and times
Working with current date and time using TODAY and NOW
Extracting date components with DAY, MONTH and YEAR
Extracting time components with HOUR, MINUTE and SECOND
Constructing date and time values with DATE and TIME
Identifying days of the week with WEEKDAY
Moving between dates with EDATE
Calculating working dates with WORKDAY and WORKDAY.INTL
Measuring periods with DATEDIF
Calculating portions of a year with YEARFRAC
Calculating differences between dates
Applying date and time functions to practical analysis scenarios

Module 10: Working with Text Functions

Understanding text manipulation in Excel
Combining text with CONCAT and CONCATENATE
Joining multiple values with TEXTJOIN
Extracting characters using LEFT and RIGHT
Extracting sections of text with MID
Measuring text length with LEN
Locating characters and words using FIND and SEARCH
Replacing text with REPLACE and SUBSTITUTE
Cleaning inconsistent data using TRIM and CLEAN
Changing text case with UPPER, LOWER and PROPER
Converting text to numeric values using VALUE
Formatting values as text with TEXT
Combining text functions for data cleaning and transformation

Module 11: Building Reusable Formulas with LET and LAMBDA

Introduction to modern custom formula techniques
Using LET to define and reuse values within formulas
Improving the readability of complex formulas with LET
Introduction to the LAMBDA function
Creating reusable calculations with LAMBDA
Applying LET and LAMBDA to reduce formula complexity

Why Choose Us

Leading UK-based global training provider since 1995.

Experience Microsoft Excel Data Analysis Using Functions in Global through Bilginç IT Academy's live and interactive virtual classroom environment. Join a Public course as an individual delegate or arrange a dedicated Private / In-house online training program exclusively for your organization.

Delivery Method: Online Instructor-Led
Participation Model: Public / Private (In-house)
Live and Interactive Training: Connect with your instructor in real time and actively participate through discussions, Q&A sessions, practical exercises, and group activities.
Flexible Participation: Join the training from your home, office, or any location with a suitable internet connection.
Expert Trainer Network: Learn from experienced trainers with strong industry backgrounds and practical field expertise.
Over 30 Years of Training Expertise: Benefit from Bilginç IT Academy's professional training experience since 1995.
Worldwide Access: Join our live virtual classrooms from Global or anywhere else in the world, or arrange a dedicated online training program for your organization.

Experience Microsoft Excel Data Analysis Using Functions through face-to-face Classroom Based training in Global. Training can be delivered as a Public course open to individual delegates or as a dedicated Private / In-house class for your organization.

Delivery Method: Classroom Based
Participation Model: Public / Private (In-house)
Face-to-Face Learning: Interact directly with your instructor and fellow delegates in an engaging classroom environment.
Experienced Trainers: Learn from specialists with extensive industry experience and practical real-world knowledge.
Professional Training Environment: Attend training in comfortable, well-equipped classrooms designed to support effective learning.
Practical Learning: Depending on the course, reinforce your knowledge through hands-on exercises, scenarios, case studies, and instructor-led activities.

Arrange Microsoft Excel Data Analysis Using Functions in Global as a dedicated Onsite training program for your organization. Bilginç IT Academy trainers can deliver the training at your office or another location of your choice, with the program planned around your team's requirements and business objectives.

Delivery Method: Onsite
Participation Model: Private (In-house)
Training at Your Preferred Location: Organize the training at your company's office or another location selected by your organization.
Tailored Course Content: Adapt the training program to your projects, team structure, existing skill levels, and specific business requirements.
Team-Focused Learning: Develop your team around a shared knowledge base while strengthening internal collaboration and knowledge transfer.
Flexible Scheduling: Plan the training dates, location, and program according to your organization's operational requirements.
Worldwide Onsite Delivery: Arrange the training in Global or at another preferred location worldwide. Bilginç IT Academy trainers can travel to your selected location to deliver the dedicated training program.

Microsoft Excel Data Analysis Using Functions Training in Global

Prerequisites

What You Will Learn

Training Outline

Module 1: Reviewing Excel Functions

Module 2: Ranking, Percentiles and Data Aggregation

Module 3: Controlling and Rounding Numeric Values

Module 4: Combining and Nesting Functions

Module 5: Conditional Calculations

Module 6: Logical and Conditional Functions

Module 7: Arrays and Dynamic Array Functions

Module 8: Lookup and Reference Functions

Module 9: Date and Time Analysis

Module 10: Working with Text Functions

Module 11: Building Reusable Formulas with LET and LAMBDA

Why Choose Us

Microsoft Excel Data Analysis Using Functions Training Course in Global Schedule

Other trainings and courses related to the Microsoft Excel Data Analysis Using Functions

Microsoft Excel Data Analysis Using Functions Training in Global

Prerequisites

What You Will Learn

Training Outline

Module 1: Reviewing Excel Functions

Module 2: Ranking, Percentiles and Data Aggregation

Module 3: Controlling and Rounding Numeric Values

Module 4: Combining and Nesting Functions

Module 5: Conditional Calculations

Module 6: Logical and Conditional Functions

Module 7: Arrays and Dynamic Array Functions

Module 8: Lookup and Reference Functions

Module 9: Date and Time Analysis

Module 10: Working with Text Functions

Module 11: Building Reusable Formulas with LET and LAMBDA

Why Choose Us

Online Instructor-Led Training

Classroom Based Training

Onsite Training

Microsoft Excel Data Analysis Using Functions Training Course in Global Schedule

Other trainings and courses related to the Microsoft Excel Data Analysis Using Functions