Course Description
This Level 3 course will build on the previous Excel courses, introducing more advanced topics.
Target Student
Previous experience with Microsoft Excel 2016 has given a solid foundation in working with this package. In this course, you will learn about some of the more advanced features of Excel, including PivotTables, Data Validation, and Advanced Formulas.
Pre-requisites
Before starting this course, students are should be able to do the following:
- Use Named Ranges
- Work with Excel Tables
- Sort and Filter Data
- Apply Conditional Formatting
- Apply Filters
- Create Charts
- Collaborate with others
Performance-Based Objectives
Upon successful completion of this course, students will be able to:
- Validate Data
- Use Advanced Formulas
- Use Auditing Tools
- Create and manipulate PivotTables and PivotCharts
- Work with Templates
- Insert Illustrations
Course Content
Lesson 1: Data Validation
- Number Validation
- Data List Validation
- Message Prompts and Alerts
- Conditional Data Validation
- Data Validation Errors
Lesson 2: Advanced Formulas
- Statistical Functions to include MAXIFS, MINIFS
- Working with Text Functions
- Calculating with Financial Functions
- Date and Time Functions
- Database Functions
Lesson 3: Auditing a Worksheet
- Display formulas in worksheet cells
- Show precedent and dependent cells
- Finding and Resolving Errors
- Using the Watch Window
- Evaluating a Formula
Lesson 4: Appendix - Inserting Illustrations
- SmartArt
- Insert Images into a Spreadsheet and/or with correct orientation
- Insert Shapes into a Spreadsheet
- Group and Layer Graphics
- Quick Shape Formatting
Lesson 5: Creating PivotTables, PivotCharts and Slicers
- Creating a PivotTable
- Amending the fields in a PivotTable
- Updating the PivotTable
- Adding Calculations to a PivotTable
- Add Grouping to a PivotChart
- Creating a PivotChart
- Slicers
- PivotTable Timelines
- Creating from Tables and Related Tables.
- PowerPivot
Lesson 6: Working with Templates
- Create a Hyperlink
- Creating and adding Cell Comments
