Microsoft Excel 2016
Part
3
Course Code: AD805

This is a course for experienced Excel users.  Learn how to use many advanced features and functions of the program, such as linking workbooks, creating templates and pivot tables, and adding formulas to multiple workbooks.

Objectives
After completing the course, participants will be able to:

  • Importing and Exporting Data to and from Excel

  • Use various functions including VLOOKUP and HLOOKUP

  • Create a workbook based on a template

  • Utilise different Protection options

  • Audit Workbooks

  • Create a Pivot Table and Pivot Chart

  • Link Multiple Workbooks through Formulas

  • Save Excel Workbooks in various Formats

  • Use DSum and DAverage functions

What you will learn
Topics covered by this course include:

Advanced Data Entry & Formatting Techniques

  • Restricting Cell Entries

  • Applying Custom Number Formats

  • Applying Conditional Formatting

Naming Ranges

  • Defining a Range Name

  • Using a Range Name in a Formula

  • Selecting a Named Range

Using Advanced Functions

  • Using the VLOOKUP Function

  • Using the HLOOKUP Function

  • Using DSum and Daverage

Creating a Custom Workbook Template

  • Creating & Modifying a Template

  • Basing a New Workbook on a Custom Workbook Template

Auditing a Worksheet

  • Tracing Precedents & Dependents

  • Tracing Errors

  • Watch and Evaluate formulas

Linking Multiple Workbooks

  • Entering A Formula to Link Multiple Workbooks

  • Creating a Workspace

Filtering and Summarizing Worksheet Data

  • Adding Subtotals to a list

  • Applying an Advanced Filter to a list

  • Outlining a Worksheet

Analysing Data

  • Creating a Pivot Table and Pivot Chart

  • Creating and Displaying a Scenario

  • Using the Analysis ToolPak

  • Using Solver

Importing & Exporting Data

Working with Excel and other Excel Users

  • Tracking Changes

  • Sharing and Protecting Workbooks

  • Merging Revised copies of Workbooks

Additional Advanced Excel Features

  • Using the Consolidation Feature

  • Adding a Trendline to a Chart

  • Format Chart and Graphics

  • Modify default settings

 

Prerequisites
Course participants should have completed Excel 2016 Part 2 (or equivalent).

Dates and times
Please refer to your Fourth R centre for dates and times.

© Copyright The Fourth R®

 [Back to MS Office Training Courses]