Advanced Excel: Formulas, Functions and Data Analysis

Business analysts

Financial analysts

Data analysts

Project managers

Accountants

Researchers

Office administrators

Anyone seeking to improve their MS Excel skills

Complete understanding of the higher functions of Excel

Perform the most advanced calculations and functions, manipulate data

Build complex formulas using nested functions (such as IF statements)

Find quicker ways to customise formulas by taking advantage of the built-in functions available in Excel

Save time by learning more sophisticated search and auditing techniques when working with data-heavy spreadsheets

Using logical functions

• The IF function (recap)
• Creating nested IF functions
• The IFS function
• AND and OR functions
• Combining logical functions

Working with Lookups

• The VLOOKUP function
• The HLOOKUP function
• The XLOOKUP function (365)
• INDEX & MATCH

Data Validation

• Using data validation
• Setting criteria for entry of text, values and dates
• Creating a dropdown list

Building Complex Formulas

• Using nested IF statements
• Creating compound logical tests using AND, OR, NOT functions with IF statements
• Nesting LOOKUP functions
• Source table structure information using CHOOSE function
• Using MATCH function to locate data
• The INDEX function for retrieving information by location
• Using a nested formula containing INDEX, MATCH and MATCH (two-way lookup)

Advanced Functions in Formulas

• Using COUNTIFS,SUMIFS, & AVERAGEIFS for tabulating data based on single/multiple criteria
• Statistical functions; MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
• AGGREGATE function to sum data in ranges with errors
• Use a variety of Financial functions such PMT, FV, IRR

Data & Text Functions

• Smarter ways to calculate date & time; TODAY, NETWORKDAYS, WORKDAY, DATEDIF
• Use TYPE to identify data type of existing cell contents
• Text functions UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN
• TRIM excess space in cells

Introduction to Array Formulas

• Using embedded Excel Array formulas
• Create an Array formula

Auditing Formulas

• Tracing formula precedents, dependents, and errors
• Correcting errors in formulas
• Combining IF with VLOOKUP to suppress error messages
• Using the IS information function
• Error checking functions; ISERR, ISERROR, IFERROR

Advanced formula creation and editing

Data analysis and manipulation

Conditional formatting and data validation

Array formula mastery

Error-handling techniques

Automation and efficiency improvement