Microsoft Excel 2016, Advanced

Customize Workbooks

  • Comments
  • Hyperlinks
  • Watermarks
  • Background Pictures

Manage Themes

  • About Themes
  • Customizing Themes

Create and Use Templates

  • Templates
  • Template Types
  • Creating a Template
  • Modifying a Template 

Protect Files

  • Recovering Lost Data
  • The Changes Group
  • Worksheet and Workbook Protection
  • The Protect Worksheet Option
  • The Protect Workbook Option

Preparing a Workbook for Multiple Audiences

  • Displaying Data in Multiple International Formats
  • Utilize International Symbols
  • Modifying Worksheets Using the Accessibility Checker
  • Managing Fonts


Auditing Worksheets

Trace Cells

  • The Trace Cells Feature
  • Tracer Arrows
  • Troubleshoot Invalid Data and Formula Errors
  • Invalid Data
  • The Error Checking Command
  • Error Types

Watch and Evaluate Formulas

  • The Watch Window
  • Formula Evaluation

Create a Data List Outline

  • Outlines
  • The Outline Group


VLOOKUP Review

Working with Multiple Worksheets and Workbooks 

Consolidate Data

  • Data Consolidation
  • The Consolidate Dialog Box
  • Consolidation Functions

Link Cells in Different Workbooks

  • External References

Merge Workbooks 

  • The Compare and Merge Workbooks Feature



Exporting Excel Data

Export Excel Data

• The Export Process

Import a Delimited Text File

  • The Import Process
  • The Get External Data Group
  • Delimited Text Files
  • Methods of Importing Text Files

Integrate Excel Data with the Web

  • The File Publishing Process
  • Publish as Web Page Dialog Box

Create a Web Query

  • Web Queries
  • The New Web Query Dialog Box

Analyzing Data with PivotTables, Slicers, and PivotCharts

Create a PivotTable

  • PivotTables
  • 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
  • PowerPivot Functions

Filter Data by Using Slicers

  • Slicers
  • The Insert Slicers Dialog Box

Analyze Data with PivotCharts

  • PivotCharts
  • Creating PivotCharts
  • Applying a Style to a PivotChart

Automating Worksheet Functionality

Update Workbook Properties

  • Workbook Properties

Create and Edit a Macro

  • Macros
  • The Record Macro Dialog Box
  • Naming Macros
  • Visual Basic for Applications
  • Copying Macros Between Workbooks
  • Macro Security Settings

Apply Conditional Formatting

  • Conditional Formatting
  • Conditional Formats
  • The Conditional Formatting Rules Manager Dialog Box
  • The New Formatting Rule Dialog Box
  • Clear Rules

Add Data Validation Criteria

  • Data Validation
  • The Data Validation Dialog Box