Course Overview
This course is designed to be the intermediate level of Excel 2016. Students will learn how to link workbooks and worksheets, work with named ranges, and intermediate Logical and Lookup functions and formulas. Students will also be introduced to and work with Tables and PivotTables, including sorting and filtering. Additionally, students will work with Charts, work with Flash Fill, work with subtotals and outlining, and learn how to customize the Excel environment.
Course Objectives
Customizing Excel, Linking Workbooks and Worksheets, Working with Range Names, Analyzing Data with Common Logical Functions, Analyzing Data with Common Lookup Functions, Sorting and Filtering Range Data, Analyzing and Organizing with Tables, Using Conditional Formatting, Outlining with Subtotals and Grouping, Displaying Data Graphically, Understanding PivotTables and PivotCharts and Filtering with Slicers, Working with Flash Fill, Working with Date and Time Functions, Working with Common Text Functions
- Top-rated instructors: Our crew of subject matter experts have an average instructor rating of 4.8 out of 5 across thousands of reviews.
- Authorized content: We maintain more than 35 Authorized Training Partnerships with the top players in tech, ensuring your course materials contain the most relevant and up-to date information.
- Interactive classroom participation: Our virtual training includes live lectures, demonstrations and virtual labs that allow you to participate in discussions with your instructor and fellow classmates to get real-time feedback.
- Post Class Resources: Review your class content, catch up on any material you may have missed or perfect your new skills with access to resources after your course is complete.
- Private Group Training: Let our world-class instructors deliver exclusive training courses just for your employees. Our private group training is designed to promote your team’s shared growth and skill development.
- Tailored Training Solutions: Our subject matter experts can customize the class to specifically address the unique goals of your team.
Agenda
1 - Introduction
2 - Customizing Excel
- Customizing the Ribbon
- Customizing the Quick Access Toolbar
- Customizing the General and Formula Options
- Customizing the AutoCorrect Options
- Customizing the Save Defaults
- Customizing Advanced Excel Options
3 - Linking Workbooks and Worksheets
- Linking Workbooks and Worksheets
- Linking Worksheets with 3D References
- Understanding the Consolidate Feature
4 - Working with Range Names
- What are Range Names?
- Creating Range Names Using the Name Box and Define Name
- Creating Range Names Using Create from Selection
5 - Analyzing Data with Common Logical Functions
- Working with the Most Common Logical Functions
- Evaluating Data with the AND Function
- Evaluating Data with the OR Function
- Understanding IF Functions
- Nesting AND and OR Inside an IF Function
6 - Analyzing Data with Common Lookup Functions
- What are Lookup Functions?
- Using VLOOKUP
- Using HLOOKUP
7 - Sorting and Filtering Range Data
- Understanding the Differences Between Sorting and Filtering
- Sorting Lists
- Filtering Lists
8 - Analyzing and Organizing with Tables
- Creating Tables and Understanding the Benefits
- Understanding the Elements of a Table
- Formatting a Table
- Sorting Tables
- Filtering Tables
- Filtering with Slicers
- Calculating with Tables
- Removing Erroneous Table Data
- Exporting, Refreshing, and Converting Tables
9 - Using Conditional Formatting
- What is Conditional Formatting?
- Conditional Formatting: Highlight Cells and Top Bottom Rules
- Conditional Formatting: Data Bars, Icon Sets, and Color Scales
- Using Custom Fonts and Colors
- Using Custom Conditional Formatting
- Modifying or Removing Conditional Formatting
10 - Outlining with Subtotals and Grouping
- What are Subtotals and Grouping?
- Creating Subtotals
- Grouping and Ungrouping Data
11 - 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
12 - Understanding PivotTables and PivotCharts and Filtering with Slicers
- What is a PivotTable?
- Creating a PivotTable
- Working with the PivotTable Fields Pane
- Basic Data Organization and Analysis with PivotTables
- Formatting PivotTables
- Creating a PivotChart
- Modifying and Formatting a PivotChart
- Adding Slicers and Timeline Slicers
- Formatting Slicers
13 - Working with Flash Fill
- What is Flash Fill?
- Using Flash Fill and Autofill
- Filling Various Series
14 - Working with Date and Time Functions
- What are Date and Time Functions?
- Using TODAY, NOW, and DAY Functions
- Using NETWORKDAYS and YEARFRAC Functions
15 - Working with Common Text Functions
- What are Text Functions?
- Using CONCATENATE
- Using Text to Columns
- Using LEFT, RIGHT, and MID Functions
- Using UPPER, LOWER, and PROPER Functions
16 - Conclusion