Course outline for
Microsoft Excel 2016: Part Three

About This Course

Course Prerequisites

Course Overview

Course Objectives

How To Use This Book

Lesson 1:
Automating Worksheet Functionality

Workbook Properties

Activity 1-1

Macros

The Record Macro Dialog Box

Naming Macros

Visual Basic for Applications

Copying Macros Between Workbooks

Macro Security Settings

Activity 1-2

Conditional Formatting

Conditional Formats

The Conditional Formatting Rules Manager Dialog Box

The New Formatting Rule Dialog Box

Clear Rules

Activity 1-3

Data Validation

The Data Validation Dialog Box

Activity 1-4

Summary

Review Questions

Lesson 2:
Auditing Worksheets

The Trace Cells Feature

Tracer Arrows

Activity 2-1

Invalid Data

The Error Checking Command

Error Types

Activity 2-2

The Watch Window

Formula Evaluation

Activity 2-3

Outlines

The Outline Group

Activity 2-4

Summary

Review Questions

Lesson 3:
Analyzing and Presenting Data

Sparklines

Types of Sparklines

The Sparkline Tools – Design Tab

Activity 3-1

Scenarios

The What-If Analysis Tools

The Scenario Manager Dialog Box

Activity 3-2

Add-In Types

Goal Seek Feature

The Solver Tool

Activity 3-3

Analysis ToolPak

The Data Analysis Dialog Box

Activity 3-4

The Power View Add-In

Enabling Power View

Creating a Power View

Activity 3-5

Summary

Review Questions

Lesson 4:
Working with Multiple Workbooks

Data Consolidation

The Consolidate Dialog Box

Consolidation Functions

Activity 4-1

External References

Activity 4-2

The Compare and Merge Workbooks Feature

Activity 4-3

Summary

Review Questions

Lesson 5:
Exporting Excel Data

The Export Process

Activity 5-1

The Import Process

The Get External Data Group

Delimited Text Files

Methods of Importing Text Files

Activity 5-2

The File Publishing Process

Publish as Web Page Dialog Box

Activity 5-3

Web Queries

The New Web Query Dialog Box

Activity 5-4

Summary

Review Questions

Lesson 6:
Importing and Exporting XML Data

XML

XML Components

XML Schemas

XML Maps

The XML Source Task Pane

Import and Export XML Data

Activity 6-1

Summary

Review Questions

Lesson Lab 1-1

Lesson Lab 1-2

Lesson Lab 2-1

Lesson Lab 2-2

Lesson Lab 3-1

Lesson Lab 3-2

Lesson Lab 4-1

Lesson Lab 5-1

Lesson Lab 5-2

Lesson Lab 6-1

Appendices

Keyboard Shortcut Quick Reference Sheet

Glossary

Index