\$29.99
##### Certification

Industry recognized certification enables you to add this credential to your resume upon completion of all courses

Get Quote
##### Call Us

Toll Free (844) 397-3739

#### Intellezy Trainers

Improving individual or company performance begins with the right training resources. Keep your team’s Microsoft and Adobe skills fresh with our engaging, short-segment videos. Intellezy’s training is delivered by certified instructors and industry experts. Their friendly, engaging style breaks down complex ideas into bite-sized learning nuggets. And, because they never leave the screen, the learner’s attention is held longer. Start watching and find out how valuable 3 minutes can be.

## Expand your Excel Abilities with Charts, Outlines, Workbooks and Much More.

• Analyze data with common logical functions.
• Outline with subtotals and grouping.
• Understand PivotTables and PivotCharts and filter with slicers.

## Duration: 4h 1m

### Course Description

This course is designed to be the intermediate level of Excel 2016. Students will learn how to link workbooks and worksheets, create named ranges and utilize them in formulas, build Logical functions such as IF, AND, and OR, and use Lookup functions to locate and compare data. Students will also be introduced to and work with Excel’s Table feature, learning to create and modify Tables. Students will also create and modify PivotTables and PivotCharts to analyze large data sets, sort the data, and use Slicers and Timeline Slicers to filter the data. Additionally, students will create and modify Charts, work with Flash Fill, work with subtotals and outlining, and learn how to customize the Excel environment. Instructor: Ed McCrae Student data files are available under Downloads in the course dashboard.

#### What am I going to get from this course?

• Custom Excel
• Work with Range Names
• Analyze Data with Common Logical Functions
• Analyze Data with Common Lookup Functions
• Sort and Filter Range Data
• Analyze and Organize with Tables
• Use Conditional Formatting
• Outline with Subtotals and Grouping
• Display Data Graphically
• Understand PivotTables and PivotCharts and Filter with Slicers
• Work with Flash Fill
• Work with Date and Time Functions
• Work with Common Text Functions

### Prerequisites and Target Audience

#### What will students need to know or do before starting this course?

Excel 2016 Introduction course or equivalent knowledge or experience

#### Who should take this course? Who should not?

Students who want to take their Excel knowledge to the next level

### Curriculum

#### Module 1: Introduction

01:24
Lecture 1 Introduction
01:24

Resource 1 Student Data Files

#### Module 2: Customizing Excel

21:08
Lecture 2 Customizing the Ribbon
06:09
03:46
Lecture 4 Customizing the General and Formula Options
02:28
Lecture 5 Customizing the AutoCorrect Options
03:31
Lecture 6 Customizing the Save Defaults
02:25
Lecture 7 Customizing Advanced Excel Options
02:49

#### Module 3: Linking Workbooks and Worksheets

12:33
Lecture 8 Linking Workbooks and Worksheets
04:55
Lecture 9 Linking Worksheets with 3D References
02:54
Lecture 10 Understanding the Consolidate Feature
04:44

#### Module 4: Working with Range Names

12:08
Lecture 11 What are Range Names?
02:43
Lecture 12 Creating Range Names Using the Name Box and Define Name
06:46
Lecture 13 Creating Range Names Using Create from Selection
02:39

#### Module 5: Analyzing Data with Common Logical Functions

19:09
Lecture 14 Working with the Most Common Logical Functions
02:31
Lecture 15 Evaluating Data with the AND Function
03:15
Lecture 16 Evaluating Data with the OR Function
03:07
Lecture 17 Understanding IF Functions
04:19
Lecture 18 Nesting AND and OR Inside an IF Function
05:57

#### Module 6: Analyzing Data with Common Lookup Functions

11:42
Lecture 19 What are Lookup Functions?
01:41
Lecture 20 Using VLOOKUP
07:39
Lecture 21 Using HLOOKUP
02:22

#### Module 7: Sorting and Filtering Range Data

08:50
Lecture 22 Understanding the Differences Between Sorting and Filtering
01:43
Lecture 23 Sorting Lists
03:49
Lecture 24 Filtering Lists
03:18

#### Module 8: Analyzing and Organizing with Tables

23:10
Lecture 25 Creating Tables and Understanding the Benefits
02:28
Lecture 26 Understanding the Elements of a Table
02:38
Lecture 27 Formatting a Table
02:36
Lecture 28 Sorting Tables
02:05
Lecture 29 Filtering Tables
02:38
Lecture 30 Calculating with Tables
03:42
Lecture 31 Filtering with Slicers
02:49
Lecture 32 Removing Erroneous Table Data
02:08
Lecture 33 Exporting, Refreshing, and Converting Tables
02:06

#### Module 9: Using Conditional Formatting

16:41
Lecture 34 What is Conditional Formatting?
01:18
Lecture 35 Conditional Formatting: Highlight Cells and Top Bottom Rules
04:23
Lecture 36 Conditional Formatting: Data Bars, Icon Sets, and Color Scales
03:55
Lecture 37 Using Custom Fonts and Colors
02:15
Lecture 38 Using Custom Conditional Formatting
02:46
Lecture 39 Modifying or Removing Conditional Formatting
02:04

#### Module 10: Outlining with Subtotals and Grouping

06:08
Lecture 40 What are Subtotals and Grouping?
01:06
Lecture 41 Creating Subtotals
03:00
Lecture 42 Grouping and Ungrouping Data
02:02

#### Module 11: Displaying Data Graphically

46:27
Lecture 43 What are Charts?
03:42
Lecture 44 Creating Charts
04:02
Lecture 45 Understanding Chart Elements
02:57
Lecture 46 Modifying Chart Elements
07:24
Lecture 47 Changing and Moving Charts
03:42
Lecture 48 Filtering a Chart
02:25
Lecture 49 Formatting Charts
06:44
03:19
Lecture 51 Creating Dual Axis Charts
02:14
Lecture 52 Forecasting with Trendlines
04:30
Lecture 53 Creating a Chart Template
01:10
Lecture 54 Displaying Trends with Sparklines
04:18

#### Module 12: Understanding PivotTables, PivotCharts, and Slicers

36:05
Lecture 55 What is a PivotTable?
03:57
Lecture 56 Creating a PivotTable
02:56
Lecture 57 Working with the PivotTable Fields Pane
06:44
Lecture 58 Basic Data Organization and Analysis with PivotTables
03:42
Lecture 59 Formatting PivotTables
05:31
Lecture 60 Creating a PivotChart
03:33
Lecture 61 Modifying and Formatting a PivotChart
03:13
Lecture 62 Adding Slicers and Timeline Slicers
03:28
Lecture 63 Formatting Slicers
03:01

#### Module 13: Working with Flash Fill

08:13
Lecture 64 What is Flash Fill?
01:12
Lecture 65 Using Flash Fill and Autofill
02:41
Lecture 66 Filling Various Series
04:20

#### Module 14: Working with Date and Time Functions

05:58
Lecture 67 What are Date and Time Functions?
01:31
Lecture 68 Using TODAY, NOW, and DAY Functions
02:04
Lecture 69 Using NETWORKDAYS and YEARFRAC Functions
02:23

#### Module 15: Working with Common Text Functions

09:36
Lecture 70 What are Text Functions?
00:57
Lecture 71 Using CONCATENATE
02:35
Lecture 72 Using Text to Columns
02:26
Lecture 73 Using LEFT, RIGHT, and MID Functions
01:59
Lecture 74 Using UPPER, LOWER, and PROPER Functions
01:39

#### Module 16: Conclusion

01:32
Lecture 75 Course Recap
01:32