Microsoft Excel – Advanced Excel Formulas & Functions – MS-CIT | Career-oriented KLiC Courses | MKCL Authorised Learning Centre

Getting Started

Excel Formula Syntax

11:03
Common Excel Error Types

08:47
Writing Efficient Formulas with Fixed Relative References

08:42
Formula Auditing Trace Precedents Dependents

10:14
Formula Auditing Evaluate Formula Error Checking (PC Only)

07:48
Navigating Excel Worksheets with Ctrl Shortcuts

07:30
Saving Time with Function Shortcuts

09:44
Accessing Tools with Alt Key Tips

04:16
PRO TIP Creating Drop-Down Menus with Data Validation

06:12
Congrats You’re a Developer

05:07
HOMEWORK Excel Formulas

00:25

Conditional Statements Logical Operators

Common Excel Statistical Functions

Basic Excel Statistical Functions

05:17
Extracting Values with SMALLLARGE RANKPERCENTRANK

06:15
Randomization with RAND() RANDBETWEEN

02:12
Row-Level Calculation with SUMPRODUCT

03:55
DEMO Calculating Shipping Costs with SUMPRODUCT

04:57
Conditional Aggregation with COUNTIFS SUMIFS AVERAGEIFS

04:49
DEMO Building a Basic Dashboard with COUNTIFS SUMIFS

08:09
DEMO Counting Duplicates with COUNTIF SUMPRODUCT

07:57
PRO TIP Data Profiling with COUNT SUMPRODUCT

16:06
HOMEWORK Excel Stats Functions

00:35

Lookup Reference Functions

Working with Named Ranges

03:30
Counting Rows Columns with ROWROWS COLUMNCOLUMNS

03:18
Introduction to VLOOKUP HLOOKUP

05:53
Joining Data with VLOOKUP

06:23
Fixing Errors with IFERROR VLOOKUP

04:30
VLOOKUP Reference Array Options

06:18
Approximate Match Lookups

05:05
Navigating Cell Ranges with INDEX

01:58
Matching Text Values with MATCH

02:32
Combining INDEX MATCH to Dynamically Search Ranges

06:08
Combining MATCH VLOOKUP for More Flexible Lookups

04:47
TROUBLESHOOTING VLOOKUP with Duplicate Keys

05:05
SNEAK PEEK XLOOKUP (Office 365 Only)

04:29
DEMO Building Dashboards with XLOOKUP (Office 365 Only)

18:16
Selecting List Items with CHOOSE

13:15
Defining Ranges with OFFSET

02:05
Combining OFFSET with COUNTA to Create a Flexible Range

02:58
PRO TIP Using OFFSET to Create Interactive Charts

09:51
HOMEWORK Excel Lookup Reference Functions

00:54

Text Functions

Capitalization with UPPER LOWER PROPER TRIM

03:32
Combining Text with CONCATENATE ()

03:26
Extracting Strings with LEFT MID RIGHT LEN

03:31
Converting Text to Values with TEXT VALUE

04:20
Searching Text String with SEARCH FIND

05:04
Categorizing Data with IF(ISNUMBER(SEARCH))

05:39
Combining RIGHT LEN and SEARCH

05:43
Replacing Text with SUBSTITUTE

02:21
HOMEWORK Excel Text Functions

00:59

Date Time Functions

Understanding Excel Date Syntax with DATEVALUE

05:05
Formatting Dates Filling Date Series

03:39
Creating Real-Time Functions with TODAY NOW

02:16
Extracting Time Periods with YEAR MONTH DAY HOUR MINUTE SECOND

03:04
Calculating the Month Start or End with EOMONTH

06:17
Calculating of Year with YEARFRAC

02:26
Defining Time Periods with WEEKDAY WORKDAY NETWORKDAYS

07:22
Calculating Differences Between Dates with DATEDIF (Legacy Function)

04:23
DEMO Building a Simple Budget Pacing Tool

11:07
HOMEWORK Excel Date Time Functions

00:35

Formula-Based Formatting

Array Formulas

Rules of Excel Array Functions

03:24
Pros Cons of Array Functions

02:15
Defining Vertical Horizontal and 2-Dimensional Arrays

06:26
Using Array Constants in Formulas

03:38
Defining Referencing Named Array Constants

04:31
Restructuring Data with TRANSPOSE

04:21
Linking Data Between Sheets (Array vs. Non-Array Comparison)

02:36
Returning the Largest Values in a Range

03:09
Counting Characters Across Cells

01:52
Creating a MAX IF Array Formula

03:09
Creating a MAX IF Array Formula with Multiple Criteria

06:54
Converting Boolean Values Using the Double Unary (–)

03:49
HOMEWORK Excel Array Functions

02:09

Course Content

Getting Started

Excel Formula Syntax

Common Excel Error Types

Writing Efficient Formulas with Fixed Relative References

Formula Auditing Trace Precedents Dependents

Formula Auditing Evaluate Formula Error Checking (PC Only)

Navigating Excel Worksheets with Ctrl Shortcuts

Saving Time with Function Shortcuts

Accessing Tools with Alt Key Tips

PRO TIP Creating Drop-Down Menus with Data Validation

Congrats You’re a Developer

HOMEWORK Excel Formulas

Conditional Statements Logical Operators

Introduction

Anatomy of the IF Statement

Nesting Multiple IF Statements

Adding Conditional ANDOR Operators

Using NOT Conditionals

Fixing Errors with IFERROR

Common IS Statements

HOMEWORK Excel Logical Operators

Common Excel Statistical Functions

Basic Excel Statistical Functions

Extracting Values with SMALLLARGE RANKPERCENTRANK

Randomization with RAND() RANDBETWEEN

Row-Level Calculation with SUMPRODUCT

DEMO Calculating Shipping Costs with SUMPRODUCT

Conditional Aggregation with COUNTIFS SUMIFS AVERAGEIFS

DEMO Building a Basic Dashboard with COUNTIFS SUMIFS

DEMO Counting Duplicates with COUNTIF SUMPRODUCT

PRO TIP Data Profiling with COUNT SUMPRODUCT

HOMEWORK Excel Stats Functions

Lookup Reference Functions

Working with Named Ranges

Counting Rows Columns with ROWROWS COLUMNCOLUMNS

Introduction to VLOOKUP HLOOKUP

Joining Data with VLOOKUP

Fixing Errors with IFERROR VLOOKUP

VLOOKUP Reference Array Options

Approximate Match Lookups

Navigating Cell Ranges with INDEX

Matching Text Values with MATCH

Combining INDEX MATCH to Dynamically Search Ranges

Combining MATCH VLOOKUP for More Flexible Lookups

TROUBLESHOOTING VLOOKUP with Duplicate Keys

SNEAK PEEK XLOOKUP (Office 365 Only)

DEMO Building Dashboards with XLOOKUP (Office 365 Only)

Selecting List Items with CHOOSE

Defining Ranges with OFFSET

Combining OFFSET with COUNTA to Create a Flexible Range

PRO TIP Using OFFSET to Create Interactive Charts

HOMEWORK Excel Lookup Reference Functions

Text Functions

Capitalization with UPPER LOWER PROPER TRIM

Combining Text with CONCATENATE ()

Extracting Strings with LEFT MID RIGHT LEN

Converting Text to Values with TEXT VALUE

Searching Text String with SEARCH FIND

Categorizing Data with IF(ISNUMBER(SEARCH))

Combining RIGHT LEN and SEARCH

Replacing Text with SUBSTITUTE

HOMEWORK Excel Text Functions

Date Time Functions

Understanding Excel Date Syntax with DATEVALUE

Formatting Dates Filling Date Series

Creating Real-Time Functions with TODAY NOW

Extracting Time Periods with YEAR MONTH DAY HOUR MINUTE SECOND

Calculating the Month Start or End with EOMONTH

Calculating of Year with YEARFRAC

Defining Time Periods with WEEKDAY WORKDAY NETWORKDAYS

Calculating Differences Between Dates with DATEDIF (Legacy Function)

DEMO Building a Simple Budget Pacing Tool

HOMEWORK Excel Date Time Functions

Formula-Based Formatting

Creating Editing Managing Formula-Based Rules

Highlighting Rows Using the MOD Function

Formatting Based on the Value of Another Cell

Formatting Cells Using Stats Functions

Formatting Cells Using Text Functions Logical Operators