Got a question? Call 1800 853 276   |   

Microsoft Excel 2016 – Part 2

  • Length 1 day
  • Price $440 inc GST
Course overview
View dates &
book now
  • Register interest

Why study this course

This course is intended to help all users get up to speed on the different features of Microsoft Excel and to become familiar with its more advanced selection of features.

We will cover how to create and use advanced formulas, analyse data, organise worksheet data with tables, visualise data with charts, insert graphics, and enhance workbooks.

This version of Microsoft Excel incorporates some new features and connectivity options in efforts to make collaboration and production as easy as possible.

Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.


What you’ll learn

At the completion of this course you should be able to:

  • Create advanced formulas

  • Analyse data with functions

  • Analyse data using functions and PivotTables

  • Work with tables

  • Visualise data with charts

  • Insert graphics

  • Enhance workbooks


Microsoft Office Applications at DDLS

DDLS is your best choice for training and certification in any of Microsoft’s leading technologies and services. We’ve been delivering effective training across all Microsoft products for over 30 years, and are proud to be Australia’s First and largest Microsoft Gold Learning Solutions Partner. All DDLS Microsoft courses follow Microsoft Official Curriculum (MOC) and are led by Microsoft Certified Trainers. Join more than 5,000 students who attend our quality Microsoft courses every year.


Stay ahead of the technology curve

Don’t let your tech outpace the skills of your people

Quality instructors and content

Expert instructors with real world experience and the latest vendor- approved in-depth course content.

Partner-Preferred Supplier

Chosen and awarded by the world’s leading vendors as preferred training partner.

Ahead of the technology curve

No matter your chosen technologies or platforms, we can help you stay one step ahead.

Who is the course for?

This course is designed for existing users of Microsoft Excel who would like to expand their knowledge and learn more of the techniques associated with creating better and more productive workbooks.

We can also deliver and customise this training course for your organisation. For more information, please contact us on 1800 853 276.


Course subjects

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

TOPIC B: Use Specialised 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

Lesson 2: Analysing 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 CONCATENATE Function

  • The TRANSPOSE Function

TOPIC B: Use Logical Functions

  • Logical Functions

  • Logical Operators

  • The AND Function

  • The OR Function

  • The IF Function

TOPIC C: Use Lookup Functions

  • Lookup Functions

  • The LOOKUP Function

  • The VLOOKUP Function

  • The HLOOKUP Function

TOPIC D: Use Date Functions

  • The TODAY Function

  • The NOW Function

  • Serialising Dates and Times with Functions

TOPIC E: Use Financial Functions

  • The IPMT Function

  • The PPMT Function

  • The NPV Function

  • The FV Function

Lesson 3: Organising Worksheet Data with Tables

TOPIC A: Create and Modify Tables

  • Tables

  • Table Components

  • The Create Table Dialog Box

  • The Table Tools – Design Contextual Tab

  • Styles and Quick Style Sets

  • Customising Row Display

  • Table Modification Options

TOPIC B: Sort and Filter Data

  • The Difference Between Sorting and Filtering

  • Sorting Data

  • Advanced Filtering

  • Filter Operators

  • Removing Duplicate Values

TOPIC C: Use Subtotal and Database Functions to Calculate Data

  • SUBTOTAL Functions

  • The Subtotal Dialog Box

  • Summary Functions in Tables

  • Database Functions

Lesson 4: Visualising Data with Charts

TOPIC A: Create Charts

  • Charts

  • Chart Types

  • Chart Insertion Methods

  • Resizing and Moving the Chart

  • Adding Additional Data

  • Switching Between Rows and Columns

TOPIC B: Modify and Format Charts

  • The Difference Between Modifying and Formatting

  • Chart Elements

  • Minimise Extraneous Chart Elements

  • The Chart Tools Contextual Tabs

  • Formatting the Chart with a Style

  • Adding a Legend to the Chart

TOPIC C: Create a Trendline

  • Trendlines

  • Types of Trendlines

  • Adding a Trendline

  • The Format Trendline Task Pane

TOPIC D: Create Advanced Charts

  • Dual Axis Charts

  • Creating Custom Chart Templates

  • Viewing Chart Animations

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

TOPIC A: Create a PivotTable

  • PivotTables

  • Start with Questions, End with Structure

  • The Create PivotTable Dialog Box

  • The PivotTable Fields Pane

  • Summarise Data in a PivotTable

  • The “Show Values As” Functionality of a PivotTable

  • External Data

  • PowerPivot

  • PowerPivot Functions

TOPIC B: Filter Data by Using Slicers

  • Slicers

  • The Insert Slicers Dialog Box

TOPIC C: Analyse Data with PivotCharts

  • PivotCharts

  • Creating PivotCharts

  • Applying a Style to a PivotChart

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

TOPIC B: Layer and Group Graphic Objects

  • Layering Objects

  • Grouping Objects

  • Positioning Objects

TOPIC C: Incorporate SmartArt

  • About SmartArt

  • The Choose a SmartArt Graphic Dialog Box

  • About the Text Pane

Lesson 7: Enhancing Workbooks

TOPIC A: Customise Workbooks

  • Comments

  • Hyperlinks

  • Watermarks

  • Background Pictures

TOPIC B: Manage Themes

  • About Themes

  • Customising Themes

TOPIC C: Create and Use Templates

  • Templates

  • Template Types

  • Creating a Template

  • Modifying a Template

TOPIC D: Protect Files

  • Recovering Lost Data

  • The Changes Group

  • Worksheet and Workbook Protection

  • The Protect Worksheet Option

  • The Protect Workbook Option

TOPIC E: Preparing a Workbook for Multiple Audiences

  • Displaying Data in Multiple International Formats

  • Utilise International Symbols

  • Modifying Worksheets Using the Accessibility Checker

  • Managing Fonts


Prerequisites

This course assumes the user has completed or has an understanding of the materials covered in the first part of the Microsoft Excel 2016 course, including:

  • Using absolute, relative, and mixed references

  • Using formulas and functions in a worksheet

  • Managing and organising worksheets

  • Editing and formatting Excel data

  • Printing and saving Excel files

  • Customising the Excel interface


Terms & Conditions

The supply of this course by DDLS is governed by the booking terms and conditions. Please read the terms and conditions carefully before enrolling in this course, as enrolment in the course is conditional on acceptance of these terms and conditions.



Request Course Information

By submitting an enquiry, you agree to our privacy policy and receiving email and other forms of communication from us. You can opt-out at any time.