Microsoft Excel Formulas and Functions

Time & Location

Contact us to Schedule Courses
Virtual Instructor Led Hands on Courses

About the Course

Microsoft Excel is a spreadsheet program included in the Microsoft Office suite of applications. This module concentrates on formulas. It begins with the creation of basic formulas and quickly moves to more advanced functions, covering the eight categories of the Function Library.

PREREQUISITE

Participants are at an intermediate level and have a working knowledge of developing basic functions in Excel. A strong interest to improve one’s understanding of formulas and to learn more complex functions.

COURSE CONTENT

TIPS AND TRICKS

· Valuable and helpful shortcuts to move around Excel much more efficiently

SIMPLE FORMULA

· How to type formulas in Excel

INTRODUCTION TO ARRAY FORMULAS

· Create an Array formula

· Using embedded Excel Array formulas

RELATIVE AND ABSOLUTE REFERENCES

· Deciding which cell reference is needed when copying a formula

COMPLEX FORMULA

· Appreciating the order of operations

· How to read and understand formulas, its simplicity and complexity.

FUNCTIONS

· What is a function?

CREATING AND UNDERSTANDING FUNCTIONS

Logical Functions

· IF, NESTEDIF, IFS, NOT, OR, AND, XOR

· Creating compound logical tests using AND, OR, NOT functions with IF statements

Financial Functions

· PMT, CUMIPMT, CUMPRINC

Database Functions

· DSUM, DAVERAGE, DCOUNT

Text Functions

· CONCATENATE or FLASHFILL

· LEFT, MID, RIGHT, LEN, LOWER, UPPER, PROPER, TEXT, TRIM

Date and Time

· Smarter ways to calculate date and time: DATE, NETWORKDAYS, DATEDIF, NOW,

TODAY

Lookup and Reference Functions

· VLOOKUP, VLOOKUP with ARRAY, VLOOKUP with MATCH

· INDEX/MATCH combination might be your solution!

Math and Trig Functions

· ABS, SUMPRODUCT, RAND, RANDBETWEEN, ROUND, SUBTOTAL

· Using SUMIF, SUMIFS, to tabulate data based on single/multiple criteria

Statistical Functions

· AVERAGE, AVERAGEA, AVERAGEIF, AVERAGEIFS

· COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS

· FREQUENCY, MEDIAN, MODE, MIN, MAX

· PERCENTILE, RANK

Auditing Formulas

· How to diagnose and fix errors

· Tracing formula precedents, dependents, and evaluating

· Correcting errors in formulas

Information Functions

· ISERROR, ISTEXT, ISEVEN, ISNA, ISODD

Register
Price
Quantity
Total
  • March Mayhem Pricing
    $99
    +$12.87 HST
    $99
    +$12.87 HST
    0
    $0
  • 4 or more Course Participants
    $299
    +$38.87 HST
    $299
    +$38.87 HST
    0
    $0
  • 2 - 3 Course Participants
    $399
    +$51.87 HST
    $399
    +$51.87 HST
    0
    $0
  • Course Calendar Date Pricing
    $299
    +$38.87 HST
    $299
    +$38.87 HST
    0
    $0
  • 1 on 1 Training
    $499
    +$64.87 HST
    $499
    +$64.87 HST
    0
    $0
Total$0

Share This Event