Course Overview
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.
Course Objectives
Linking Workbooks and Worksheets, Working with Range Names, Sorting and Filtering Range Data, Using Conditional Formatting, Displaying Data Graphically, Understanding PivotTables, PivotCharts, and Slicers, Working with Advanced PivotTables and PowerPivot Features, Analyzing and Organizing with Tables
Agenda
1 - Introduction
2 - Linking Workbooks and Worksheets
- Linking Workbooks and Worksheets
- Linking Worksheets with 3D References
- Understanding the Consolidate Feature
3 - Working with Range Names
- What are Range Names?
- Using the Name Box and Define Name
- Using Create from Selection
4 - Sorting and Filtering Range Data
- Differences Between Sorting and Filtering
- Sorting Lists
- Filtering Lists
- Creating Subtotals
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
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
7 - Displaying Data Graphically
- What are Charts?
- Creating Charts
- Understanding Chart Elements
- Modifying Chart Elements
- Changing and Moving Charts
- Filtering a Chart
- Formatting Charts
- Adjusting Numbering
- Creating Dual Axis Charts
- Forecasting with Trendlines
- Creating a Chart Template
- Displaying Trends with Sparklines
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
- Formatting PivotTables
- Creating a PivotChart
- Modifying and Formatting a PivotChart
- Using Slicers and Timeline Slicers
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
10 - Conclusion