Microsoft Excel Formulas & Functions

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

Price

PoA

Delivery method

Classroom, Instructor-led

Duration

1 day

Timetable

9:30 am - 5:00 pm

What's included?

Courseware and lunch

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.

Advanced functions in formulas

  • 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.

Exam information

Exam code

NA

Exam price

NA

Number of questions

NA

Question type

NA

Duration

NA

Enrol now to save your place