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.
Module 2: Linking Workbooks and Worksheets
Linking Workbooks and Worksheets
Linking Workbooks with 3D References
Understanding the Consolidate Feature
Module 3: Working with Range Names
What are Range Names?
Using the Name Box and Define Name
Using Create from Selection
Module 4: Sorting, Filtering, and Subtotaling
Differences Between Sorting and Filtering
Module 5: Analyzing and Organizing with Tables
Creating Tables and Understanding the Benefits
Understanding the Elements of a Table
Formatting a Table
Sorting and Filtering Tables
Filtering with Slicers
Calculating with Tables
Removing Erroneous Table Data
Exporting, Refreshing, and Converting Tables
Module 6: Using Conditional Formatting
What is Conditional Formatting?
Highlight Cells and Top Bottom Rules
Data Bars, Icon Sets, and Color Scales
Using Custom Fonts and Colors
Using Custom Conditional Formatting
Modifying or Removing Conditional Formatting
Module 7: Displaying Data Graphically
What are Charts?
Understanding Chart Elements
Modifying Chart Elements
Changing and Moving Charts
Filtering a Chart
Creating Dual Axis Charts
Forecasting with Trendlines
Creating a Chart Template
Displaying Trends with Sparklines
Module 8: Understanding PivotTables, PivotCharts, and Slicers
What is a PivotTable?
Creating a PivotTable
Working with the PivotTable Fields Pane
Data Organization and Analysis with PivotTables
Creating a PivotChart
Modifying and Formatting a PivotChart
Adding Slicers and Timeline Slicers
Using the Ideas Feature
Module 9: Working with Advanced PivotTables and PowerPivot Features
Using the PivotTable and PivotChart Wizard
Adding a Calculated Field
Adding a Calculated Item
Applying Conditional Formatting to a PivotTable
Creating Filter Pages for a PivotTable
Enabling the PowerPivot Add-In
Final Assessment Quiz - Excel 365 Intermediate