Training

Information Technology

Advanced Excel

Start: Oct 2017       Type: Evening / Weekend Classes       Duration (hr): 18

COURSE DESCRIPTION:

In this module students will learn fundamentals of Advanced Excel techniques, includingincluding 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
 
*18 instructor-led class hours
 
Prerequisites:
Basic knowledge of Microsoft Excel 
  
 
 
 

Related Courses