Microsoft Excel 2016 – Advanced

Code INF 1617

Table of Contents

Setting Excel Options

Understanding Excel Options

Personalising Excel

Setting the Default Font

Setting Formula Options

Understanding Save Options

Setting Save Options

Setting the Default File Location

Setting Advanced Options

Protecting Data

Understanding Data Protection

Providing Total Access to Cells

Protecting a Worksheet

Working With a Protected Worksheet

Disabling Worksheet Protection

Providing Restricted Access to Cells

Password Protecting a Workbook

Opening a Password Protected Workbook

Removing a Password From a Workbook

Importing and Exporting

Understanding Data Importing

Importing From an Earlier Version

Understanding Text File Formats

Importing Tab Delimited Text

Importing Comma Delimited Text

Importing Space Delimited Text

Importing Access Data

Working With Connected Data

Unlinking Connections

Exporting to Microsoft Word

Exporting Data as Text Inserting a Picture

Modifying an Inserted Picture

Data Linking

Understanding Data Linking

Linking Between Worksheets

Linking Between Workbooks

Updating Links Between Workbooks

Grouping and Outlining

Understanding Grouping and Outlining

Creating an Automatic Outline

Working With an Outline

Creating a Manual Group

Grouping by Columns

Summarising and Subtotalling

Creating Subtotals

Using a Subtotalled Worksheet

Creating Nested Subtotals

Copying Subtotals

Using Subtotals With AutoFilter

Creating Relative Names for Subtotals

Using Relative Names for Subtotals

Data Consolidation

Understanding Data Consolidation

Consolidating With Identical Layouts

Creating A Linked Consolidation

Consolidating From Different Layouts

Consolidating Data Using The SUM Function

Data Consolidation

Understanding Data Consolidation

Consolidating With Identical Layouts

Creating a Linked Consolidation

Consolidating From Different Layouts

Consolidating Data Using the SUM Function

Data Tables

Understanding Data Tables and What-If Models

Using a Simple What-If Model

Creating a One-Variable Table

Using One-Variable Data Tables

Creating a Two-Variable Data Table

Scenarios

Understanding Scenarios

Creating a Default Scenario

Creating Scenarios

Using Names in Scenarios

Displaying Scenarios

Creating a Scenario Summary Report

Merging Scenarios

PivotTables

Understanding PivotTables

Recommended PivotTables

Creating Your Own PivotTable

Defining the PivotTable Structure

Filtering a PivotTable

Clearing a Report Filter

Switching PivotTable Fields

Formatting a PivotTable

Understanding Slicers

Creating Slicers

Inserting a Timeline Filter

Challenge Exercise

Challenge Exercise Sample

PivotTable Features

Using Compound Fields

Counting in a PivotTable

Formatting PivotTable Values

Working With PivotTable Grand Totals

Working With PivotTable Subtotals

Finding the Percentage of Total

Finding the Difference From

Grouping in PivotTable Reports

Creating Running Totals

Creating Calculated Fields

Providing Custom Names

Creating Calculated Items

PivotTable Options

Sorting in a PivotTable

PivotCharts

Inserting a PivotChart

Defining the PivotChart Structure

Changing the PivotChart Type

Using the PivotChart Filter Field Buttons

Moving PivotCharts to Chart Sheets

Validating Data

Understanding Data Validation

Creating A Number Range Validation

Testing A Validation

Creating An Input Message

Creating A Drop Down List

Using Formulas As Validation Criteria

Circling Invalid Data

Removing Invalid Circles

Copying Validation Settings

Advanced Filters

Understanding Advanced Filtering

Using an Advanced Filter Extracting Records With Advanced Filter

Using Formulas in Criteria

Understanding Database Functions

Using Database Functions

Using DSUM

Using the DMIN Function

Using the DMAX Function

Using the DCOUNT Function

Controls

Adding a Combo Box Control

Changing Control Properties

Using the Cell Link to Display the Selection

Adding a List Box Control

Adding a Scroll Bar Control

Adding a Spin Button Control

Adding Option Button Controls

Adding a Group Box Control

Adding a Check Box Control

Protecting a Worksheet With Controls

Recorded Macros

Understanding Excel Macros

Setting Macro Security

Saving A Document As Macro Enabled

Recording A Simple Macro

Running A Recorded Macro

Relative Cell References

Running A Macro With Relative References

Viewing A Macro

Editing A Macro

Assigning A Macro To The Toolbar

Running A Macro From The Toolbar

Assigning A Macro To The Ribbon

Assigning A Keyboard Shortcut To A Macro

Deleting A Macro

Copying A Macro

Sharing Workbooks

Sharing Workbooks via the Network

Sharing Workbooks via OneDrive

Saving to OneDrive

Sharing Workbooks

Opening Shared Workbooks

Enabling Tracked Changes

Accepting or Rejecting Changes

Disabling Tracked Changes

Adding Worksheet Comments

Navigating Worksheet Comments

Editing Worksheet Comments

Deleting Comments

Download this outline as a PDF.

Advanced Microsoft Excel 2016