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
- Link Workbooks and Worksheets
- 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
Resource 1
Student Data Files
Module 2: Customizing Excel
21:08
Lecture 2
Customizing the Ribbon
06:09
Lecture 3
Customizing the Quick Access Toolbar
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
Lecture 50
Adjusting Numbering
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
