Microsoft Excel 2016: Part Two Course Outline

Microsoft Excel 2016: Part Two

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1: Creating Advanced Formulas

TOPIC A: Apply Range Names

Range Names

Adding Range Names Using the Name Box

Adding Range Names Using the New Name Dialog Box

Editing a Range Name and Deleting a Range Name

Using Range Names in Formulas

Activity 1-1

TOPIC B: Use Specialized Functions

Function Categories

The Excel Function Reference

Function Syntax

Function Entry Dialog Boxes

Using Nested Functions

Automatic Workbook Calculations

Showing and Hiding Formulas

Enabling Iterative Calculations

Activity 1-2


Review Questions

Lesson 2: Analyzing Data with Logical and Lookup Functions

TOPIC A: Use Text Functions

Text Functions

The LEFT and RIGHT Functions

The MID Function

The LEN Function

The TRIM Function

The UPPER, LOWER, and PROPER Functions


The TRANSPOSE Function

Activity 2-1

TOPIC B: Use Logical Functions

Logical Functions

Logical Operators

The AND Function

The OR Function

The IF Function

Activity 2-2

TOPIC C: Use Lookup Functions

Lookup Functions

The LOOKUP Function

The VLOOKUP Function

The HLOOKUP Function

Activity 2-3

TOPIC D: Use Date Functions

The TODAY Function

The NOW Function

Serializing Dates and Times with Functions

Activity 2-4

TOPIC E: Use Financial Functions

The IPMT Function

The PPMT Function

The NPV Function

The FV Function

Activity 2-5


Review Questions

Lesson 3: Organizing Worksheet Data with Tables

TOPIC A: Create and Modify Tables


Table Components

The Create Table Dialog Box

The Table Tools – Design Contextual Tab

Styles and Quick Style Sets

Customizing Row Display

Table Modification Options

Activity 3-1

TOPIC B: Sort and Filter Data

The Difference Between Sorting and Filtering

Sorting Data

Advanced Filtering

Filter Operators

Removing Duplicate Values

Activity 3-2

TOPIC C: Use Subtotal and Database Functions to Calculate Data

SUBTOTAL Functions

The Subtotal Dialog Box

Summary Functions in Tables

Database Functions

Activity 3-3


Review Questions

Lesson 4: Visualizing Data with Charts

TOPIC A: Create Charts


Chart Types

Chart Insertion Methods

Resizing and Moving the Chart

Adding Additional Data

Switching Between Rows and Columns

Activity 4-1

TOPIC B: Modify and Format Charts

The Difference Between Modifying and Formatting

Chart Elements

Minimize Extraneous Chart Elements

The Chart Tools Contextual Tabs

Formatting the Chart with a Style

Adding a Legend to the Chart

Activity 4-2

TOPIC C: Create a Trendline


Types of Trendlines

Adding a Trendline

The Format Trendline Task Pane

Activity 4-3

TOPIC D: Create Advanced Charts

Dual Axis Charts

Creating Custom Chart Templates

Viewing Chart Animations

Activity 4-4


Review Questions

Lesson 5: Analyzing Data with PivotTables, Slicers, and PivotCharts

TOPIC A: Create a PivotTable


Start with Questions, End with Structure

The Create PivotTable Dialog Box

The PivotTable Fields Pane

Summarize Data in a PivotTable

The “Show Values As” Functionality of a PivotTable

External Data


PowerPivot Functions

Activity 5-1

TOPIC B: Filter Data by Using Slicers


The Insert Slicers Dialog Box

Activity 5-2

TOPIC C: Analyze Data with PivotCharts


Creating PivotCharts

Applying a Style to a PivotChart

Activity 5-3


Review Questions

Lesson 6: Inserting Graphics

TOPIC A: Insert and Modify Graphic Objects

Graphical Objects

Inserting Shapes

Inserting WordArt

Inserting Text Boxes

Inserting Images

The Picture Tools – Format Contextual Tab

The Drawing Tools – Format Contextual Tab

The SmartArt Tools Contextual Tabs

Activity 6-1

TOPIC B: Layer and Group Graphic Objects

Layering Objects

Grouping Objects

Positioning Objects

Activity 6-2

TOPIC C: Incorporate SmartArt

About SmartArt

The Choose a SmartArt Graphic Dialog Box

About the Text Pane

Activity 6-3


Review Questions

Lesson 7: Enhancing Workbooks

TOPIC A: Customize Workbooks




Background Pictures

Activity 7-1

TOPIC B: Manage Themes

About Themes

Customizing Themes

Activity 7-2

TOPIC C: Create and Use Templates


Template Types

Creating a Template

Modifying a Template

Activity 7-3

TOPIC D: Protect Files

Recovering Lost Data

The Changes Group

Worksheet and Workbook Protection

The Protect Worksheet Option

The Protect Workbook Option

Activity 7-4

TOPIC E: Preparing a Workbook for Multiple Audiences

Displaying Data in Multiple International Formats

Utilize International Symbols

Modifying Worksheets Using the Accessibility Checker

Managing Fonts

Activity 7-5


Review Questions

Lesson Labs

Lesson 1

Lesson Lab 1-1

Lesson 2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson 3

Lesson Lab 3-1

Lesson 4

Lesson Lab 4-1

Lesson Lab 4-2

Lesson 5

Lesson Lab 5-1

Lesson Lab 5-2

Lesson 6

Lesson Lab 6-1

Lesson 7

Lesson Lab 7-1

Lesson Lab 7-2

Course Wrap-Up

Course Summary

Next Steps


Keyboard Shortcut Quick Reference Sheet



Download this course as a demo

Complete the form below and we'll email you an evaluation copy of this course:

What You Get with Velsoft Courseware

More Course Outlines in this Series