Microsoft Excel Advanced Functions and Formulas

Course Length:

6 hours (1 day)

Learning Objectives

After completing this course, students will be able to:

Identify which function to use to generate results
Employ a number of strategies for creating functions
Build their own functions using the fx tool
Add multiple worksheets together
Utilize array formulas

Target Student

This course is designed for Excel users with some knowledge of entering financial data but would like to explore more mathematical functions.

Course Outline

Section 1: Working with Basic Formulas, Functions and Named Ranges

Creating Basic Functions

Absolute & Relative Cell References

Updating Values without Formulas (Paste Special)

Displaying & Highlighting Formulas

Named Ranges

Logical Functions

IF Logical Tests

COUNTIF, SUMIF, SUMIFS, and AVERAGEIF

Section 2: Text Functions

Locating Data with FIND, SEARCH, EXACT

Extracting Data with LEFT, RIGHT, and MID

Flash Fill

Combine Data from Different Cells Using CONCATENATE

Changing the Case of Text Using PROPER, UPPER, and LOWER

Section 3: Information Functions

ISERR, ISERROR, IFERROR

Section 4: Lookup Functions

VLOOKUP

MATCH (2 list comparison)

INDEX (pull info from a list based on column and row headings)

Section 5: Date and Time Functions

DAYS

WORKDAY

WORKDAY.INTL

NETWORKDAYS

Retrieving Dates in Excel

WEEKDAY

WEEKNUM

DATEDIF

Section 6: Statistical Functions

MEDIAN

MODE

RANK

COUNTBLANK

Section 7: Math Functions and Arrays

ROUND

ARRAY FORMULAS

Basic Array Formulas

Array Formulas with Basic Functions

Array Formulas with VLookup

Section 8: Database Functions

DSUM, DCOUNT, DAVERAGE

A seal with “Guaranteed to Run” written on it. In the middle is a person giving a thumbs up, standing behind a checkmark

Microsoft Excel Advanced Functions and Formulas

Upcoming Classes

All Public classes are held online
with a live instructor

Dates	Price	Qty
November 22, 2023 9:00 AM to 4:00 PM ET	$475.00 Online
March 6, 2024 9:00 AM to 4:00 PM ET	$475.00 Online

* Applicable taxes will be applied on checkout

I want to talk to someone about private or customized training

Have questions about this course? See below or reach out to our training coordinators.

Group of 4 or more? Consider private training

The Great Canadian Training & Consulting Company Advantage

When you train with us, your training will not just check a box. We make sure it is GREAT!

Experienced Trainers
Guaranteed to run classes
Learning Guarantee

Anytime after-training support for 30 days
Bilingual training, manuals & post-training support available

This course comes with

PDF Reference Guide

Take the Reference Guide anywhere and save the environment with an electronic copy of your training Reference Guide for use on any of your computers or devices

Use Your Own Files

Whether it's one-on-one or group training you can give us files to include in your training session and help you apply what you learn even more!

Printed Reference Guide

Instead of the PDF that comes with your course, purchase a printed paper Reference Guide for use during your class and future reference