OUTLINE

DURATION:  2 DAYS (16 HOURS)

DIFFICULTY LEVEL: INTERMEDIATE

1. Introduction

Absolute and relative cell references

The Formula Bar and the Insert Function tool

The Name Box, naming and editing cell ranges

The Status Bar

Basic Excel function syntax

Nested formulas

2. The new Array formula platform

Evaluating array formulas v normal formulas

Spilled array behavior of formulas

What’s best 

3. Logical functions

IF

IFS

AND

4. Sum and Count functions

SUMIF, SUMIFS, DSUM

AVERAGE, AVERAGEA

COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS

SUMPRODUCT

5. Text functions

CONCATENATE

CONCAT

TEXTJOIN

LEFT, RIGHT, MID, TEXT

LOWER, UPPER, PROPER

TRIM, CLEAN

SUBSTITUTE

FIND

TEXT

6. Date and Time functions

The date format and value of date cells

EDATE, EOMONTH, DAYS, DAYS360, NETWORKDAYS, NETWORKDAYS.INTL, NOW

7. Drop-down boxes

Creating drop-downs with Data Validation List

8. Lookup-related functions

VLOOKUP

HLOOKUP

XLOOKUP

TRANPOSE

UNIQUE

FILTER

SORT

Working functions with Form Controls

9. Other import tools on formulas

Formula Auditing

Displaying Formulas

Error Checking and tracing errors

Evaluating formulas

Calculation options

Copying and pasting formulas

Comparing conditional sum, count functions with database functions

9.Quick Summation methods

Using shortcut keys and AutoSum

10.      Customizing Number Formats

Examining number formats and changing them

Customizing number formats with advanced methods