Skip to main content

Expanding Your Knowledge of Excel Functions

Back to Course Schedule
Date(s): Nov 30, 2021
Time: 8:00AM - 4:00PM
Registration Fee: $99.00
Cancellation Date: Nov 23, 2021
Location: Online

Course Description

In our Intermediate/Advanced Excel class we learn the more common functions used by many intermediate Excel users.  This class will expand on that knowledge introducing Excel users to even more helpful functions. 

This class with expand of your knowledge of Date Functions, Logical Functions, Lookup and Reference Functions, Math Functions, Statistical Functions and Text Functions.  The functions taught in the class will work in most current versions of Excel such as 2013, 2016, 2019 and Microsoft 365.

Potential CPE Credits: 7.0

Instruction Type: Live
Experience Level:
Category: Computer Software and Applications

Course Objectives

Upon completion of this course, participants will be able to:

Use and build more advanced Excel Functions.

Detailed Course Outline:

Lesson 1 – Date Functions

Understanding the Function Library

Reviewing Absolute References

Working with Dates in Excel

DATE - Turning non-dates into dates

EDATE - Calculate a date on the same day of the month, x months in the past or future

NETWORKDAYS - Returns the number of whole working days between a start date and an end date.

WEEKDAY - Returns a number between 1-7 representing the day of week.

WORKDAY - Calculate the number of days from a start date excluding weekends and possible holidays.


Lesson 2 – Logical Functions

AND - A logical function used to determine if all conditions in a test are TRUE.

Using the Evaluate Functions Auditing Tool

OR - A logical function used to determine if any conditions in a test are TRUE.

IFERROR - Returns a value you specify if a formula evaluates to an error; otherwise, it returns the result of the formula.


Lesson 3 – Lookup and Reference Functions

FORMULATEXT – Displays the formula

INDIRECT - Returns the reference specified by a text string

TRANSPOSE - Changes the layout of data, from horizontal to vertical, or from vertical to horizontal

Compare the Transpose function to the Copy and Paste Transpose

INDEX - Returns a value or the reference to a value from within a table or range

MATCH - Searches for a specified item in a range of cells, and then returns the relative position of that item in the range.


Lesson 4 – Math Functions

CEILING.MATH – Rounds a number up to the nearest integer or to the nearest multiple of significance

FLOOR.MATH – Rounds a number down to the nearest integer or to the nearest multiple of significance

INT – Returns the integer part of a decimal number

RAND -Returns an evenly distributed random real number greater than or equal to 0 and less than 1

RANDBETWEEN - Returns a random integer number between the numbers you specify. A new random integer number is returned every time the worksheet is calculated

SUMIF - Returns the sum of cells that meet a single condition

SUMPRODUCT - Returns the sum of the products of corresponding ranges or arrays.


Lesson 5 – Statistical Functions

RANK.AVG – Returns the rank of a number in a list

RANK.EQ – Also Returns the rank of a number in a list

SMALL - Returns the k-th smallest value in a data set

LARGE - Returns the k-th largest value in a data set

Lesson 6 – Text Functions

TRIM - Removes all spaces from text except for single spaces between words

TEXT – This function allows you change the way a number appears by applying formatting to it with format codes

SUBSTITUTE - Substitutes new text for old text in a text string

REPLACE - Replaces part of a text string, based on the number of characters you specify, with a different text string.

Additional Information

TAC Rule 523.142(g) requires the CPE Sponsor to monitor individual attendance and assign the correct number of CPE credits. Participants will be asked to document their time of arrival and departure in compliance with this Rule. Additionally, attendance will be monitored throughout the day and CPE certificates will reflect actual attendance of each participant.

To see answers to our Frequently Asked Questions, visit


Back to Course Schedule