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