Microsoft Excel 2016 – Part 2

Length

1 day

Price

$440.00 inc GST

Overview

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.

Key Topics

Detailed Info

Creating Advanced Formulas
Analysing Data with Logical and Lookup Functions
Organising Worksheet Data with Tables
Visualising Data with Charts
Analysing Data with PivotTables, Slicers, and PivotCharts
Inserting Graphics
Enhancing Workbooks

Skills Gained

Key Topics

Target Audience

Prerequisites

Skills Gained

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

Key Topics

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

Target Audience

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.

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

Print course details

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

You don't appear to have Javascript enabled, our contact forms & booking process unfortunately requires it.

If you'd like more information, or to book a course please email us

Email Course Outline

Request a Callback

Enter your details below and we'll email you a pdf of the course outline.

Enter your details below and one of our team will give you a call to answer any questions you may have.

Length

Price

Overview

Key Topics

Skills Gained

Key Topics

Target Audience

Prerequisites

Request Course Information

Testimonials

YOUR IT TRAINING PARTNER

FOLLOW US