Advanced Excel

START

Dec 2021

TYPE

Data Analysis and Data Visualization*

Type of Training: Remote (Online) / In-Person; Time: Evenings & Weekends; Length: 30 hr;

COURSE DESCRIPTION:
In this module students will learn fundamentals of Advanced Excel techniques, including Math Functions, Logical Functions, Statistical Lookup Functions, Sort/Filter Data, Pivot Tables and Pivot Charts.

COURSE CONTENT:

Date Functions
Background on Dates
Entering Dates & Times
Date Components
Convert Dates to Month End Dates
Using Month Names
Using Weekdays
Using Week Numbers
Convert Text to Dates
Work Days
Year Fractions

Text Functions
Convert Text, Dates and Numbers
Combine Text from Multiple Cells
Text Display (upper/lower case)
Remove Spaces from Text
Determine Text Length
Extract Characters from Text
Find Characters in Text
Replace Characters in Text
Search for Characters in Text

Math Functions
Auto Sum
Sum Values
Absolute Values
Rounding Values
Integer Values
Random Values

Logical Functions
IF Function
Nesting IF Functions
AND Function
OR Function
NOT Function

Statistical Functions
SUMIF Function
COUNTIF Function
SUMPRODUCT Function
Sum Based on Multiple Criteria
Average Value
Minimum Value
Maximum Value
Median Value
Mode Value

Lookup Functions
Lookup Cell Values
Lookup Cell Values by Column

Sort / Filter Data
Sort Data on a Single Column
Sort Data on Multiple Columns
Apply a Data Filter
Filter by Selected Cell
Remove a Data Filter
Sort Data with Filter
Apply a Custom Filter
Advanced Filter
Removing All Filters

Pivot Tables & Pivot Charts
Source Data Layout
Pivot Table Tools Tab
Change Source Data
Refresh a Pivot Table
Change Pivot Table Layout
Filter Pivot Table Data
Pivot Table Data Drill-Down
Pivot Table Format Values
Pivot Table Field Settings
Pivot Table Options
Pivot Table Column Width
Create a Pivot Chart

30 instructor-led class hours

Prerequisites:
Basic knowledge of Microsoft Excel