Course Description
In this course, students will learn how to link workbooks and worksheets, work with range names, sort and filter range data, and analyze and organize with tables. Students will also apply conditional formatting, outline with subtotals and groups, display data graphically with charts and sparklines. Additionally, students will also understand PivotTables, PivotCharts, and slicers and work with advanced PivotTables and PowerPivot features.
What am I going to get from this course?
- Link Workbooks and Worksheets
- Work with Range Names
- Sort and Filter Range Data
- Analyze and Organize with Tables
- Use Conditional Formatting
- Outline with Subtotals and Groups
- Display Data Graphically
- Understand PivotTables, PivotCharts, and Slicers
- Advanced Use of PivotTables and PowerPivot
Prerequisites and Target Audience
What will students need to know or do before starting this course?
Excel 365 Introduction course or equivalent knowledge or experience.
Who should take this course? Who should not?
Those looking to increase Excel 365 knowledge base.
Curriculum
Module 2: Linking Workbooks and Worksheets
Lecture 2
Linking Workbooks and Worksheets
Lecture 3
Linking Workbooks with 3D References
Lecture 4
Understanding the Consolidate Feature
Module 3: Working with Range Names
Lecture 5
What are Range Names?
Lecture 6
Using the Name Box and Define Name
Lecture 7
Using Create from Selection
Module 4: Sorting, Filtering, and Subtotaling
Lecture 8
Differences Between Sorting and Filtering
Lecture 10
Filtering Lists
Lecture 11
Creating Subtotals
Module 5: Analyzing and Organizing with Tables
Lecture 12
Creating Tables and Understanding the Benefits
Lecture 13
Understanding the Elements of a Table
Lecture 14
Formatting a Table
Lecture 15
Sorting and Filtering Tables
Lecture 16
Filtering with Slicers
Lecture 17
Calculating with Tables
Lecture 18
Removing Erroneous Table Data
Lecture 19
Exporting, Refreshing, and Converting Tables
Module 6: Using Conditional Formatting
Lecture 20
What is Conditional Formatting?
Lecture 21
Highlight Cells and Top Bottom Rules
Lecture 22
Data Bars, Icon Sets, and Color Scales
Lecture 23
Using Custom Fonts and Colors
Lecture 24
Using Custom Conditional Formatting
Lecture 25
Modifying or Removing Conditional Formatting
Module 7: Displaying Data Graphically
Lecture 26
What are Charts?
Lecture 27
Creating Charts
Lecture 28
Understanding Chart Elements
Lecture 29
Modifying Chart Elements
Lecture 30
Changing and Moving Charts
Lecture 31
Filtering a Chart
Lecture 32
Formatting Charts
Lecture 33
Adjusting Numbering
Lecture 34
Creating Dual Axis Charts
Lecture 35
Forecasting with Trendlines
Lecture 36
Creating a Chart Template
Lecture 37
Displaying Trends with Sparklines
Module 8: Understanding PivotTables, PivotCharts, and Slicers
Lecture 38
What is a PivotTable?
Lecture 39
Creating a PivotTable
Lecture 40
Working with the PivotTable Fields Pane
Lecture 41
Data Organization and Analysis with PivotTables
Lecture 42
Formatting PivotTables
Lecture 43
Creating a PivotChart
Lecture 44
Modifying and Formatting a PivotChart
Lecture 45
Adding Slicers and Timeline Slicers
Lecture 46
Formatting Slicers
Lecture 47
Using the Ideas Feature
Module 9: Working with Advanced PivotTables and PowerPivot Features
Lecture 48
Using the PivotTable and PivotChart Wizard
Lecture 49
Adding a Calculated Field
Lecture 50
Adding a Calculated Item
Lecture 51
Applying Conditional Formatting to a PivotTable
Lecture 52
Creating Filter Pages for a PivotTable
Lecture 53
Enabling the PowerPivot Add-In
Quiz 1
Final Assessment Quiz - Excel 365 Intermediate
