Length
1 day

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
  • Organising Worksheet Data with Tables
  • Analysing Data with PivotTables, Slicers, and PivotCharts
  • Analysing Data with Logical and Lookup Functions
  • Visualising Data with Charts
  • 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 email [email protected]

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.

Book Your Course

Virtual Classroom
July 27 2021 - July 27 2021
July 26 2021 - July 26 2021
August 24 2021 - August 24 2021
August 25 2021 - August 25 2021

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.