# Microsoft Excel Formulas & Functions

## Course Code:

| PoA

In this Microsoft Excel Formulas & Functions training course you will learn statistical and complex logical functions and understand sophisticated formulas to work with data-heavy spreadsheets

Delivery method

Classroom, Instructor-led

Duration

1 day

Timetable

9:30 am - 5:00 pm

What's included?

Courseware and lunch

This course will give you the opportunity to build on your existing Microsoft Excel skills and gain knowledge and experience using advanced formulas and calculations in spreadsheets. You will become familiar with:

Building complex formulas

Understand how to use nested IF statements, nested LOOKUP functions and nested formula containing INDEX, MATCH and MATCH (two-way lookup). Understand to creating compound logical tests by using AND, OR, NOT functions with IF statements. Understand reference function by using MATCH function to locate data and using INDEX function for retrieving information by location.

Understand tabulating data based on single/multiple criteria by using using COUNTIFS,SUMIFS Understand statistical functions, including MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD Understand how to use a variety of Financial functions such PMT, FV, IRR Understand smarter ways to calculate date and time by using TODAY, NETWORKDAYS, WORKDAY, DATEDIF Understand how to use TYPE to identify data type of existing cell contents, and apply text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN, and perform TRIM excess space in cells
Error check and audit

Understand Excel error values, and how to trace formula precedents, dependents, and errors Understand the error checking functions ISERR, ISERROR, and IFERROR and combining IF with VLOOKUP to suppress error messages
Array formulas

Understand how to use embedded Excel Array formula Understand how to create single cell array formulas and multi-cell array formulas

## Course overview

This course will give you the opportunity to build on your existing Microsoft Excel skills and gain knowledge and experience using advanced formulas and calculations in spreadsheets. You will become familiar with:

Building complex formulas

• Understand how to use nested IF statements, nested LOOKUP functions and nested formula containing INDEX, MATCH and MATCH (two-way lookup).
• Understand to creating compound logical tests by using AND, OR, NOT functions with IF statements.
• Understand reference function by using MATCH function to locate data and using INDEX function for retrieving information by location.

• Understand tabulating data based on single/multiple criteria by using using COUNTIFS,SUMIFS
• Understand statistical functions, including MEDIAN, MODE, RANK, LARGE, SMALL, ROUND, MOD
• Understand how to use a variety of Financial functions such PMT, FV, IRR
• Understand smarter ways to calculate date and time by using TODAY, NETWORKDAYS, WORKDAY, DATEDIF
• Understand how to use TYPE to identify data type of existing cell contents, and apply text functions; UPPER, PROPER, FIND, MID, SEARCH, LEFT, RIGHT, LEN, and perform TRIM excess space in cells

Error check and audit

• Understand Excel error values, and how to trace formula precedents, dependents, and errors
• Understand the error checking functions  ISERR, ISERROR, and  IFERROR and combining IF with VLOOKUP to suppress error messages

Array formulas

• Understand how to use embedded Excel Array formula
•  Understand how to create single cell array formulas and multi-cell array formulas

## Prerequisites

Students should have an intermediate level of competency using Excel. You are advised to complete the Excel Intermediate training course and Excel Advanced training course prior to enrolling or be familiar with the content covered in these courses.

## Outcomes

Upon completion of this course you will be well equipped to work with a range of sophisticated functions and formulas in Microsoft Excel. You will gain the necessary skills to successfully:

• Be confident in using advanced statistical and complex logical functions;

• Feel comfortable writing formulas to handle errors, and audit a workbook to find error sources;

• Understand how to use a range of different lookup functions to cover a variety of list.

NA

NA

NA

NA

NA