Microsoft Excel 2016 – Part 3

Length

1 day

Price

$440.00 inc GST

Overview

This course is intended to help all users get up to speed on the different aspects of Microsoft Excel, including some of its more advanced features.

We will cover how to automate worksheet functionality, audit worksheets, analyse data, work with multiple workbooks, export Excel data, as well as import and export XML data.

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

Automating Worksheet Functionality
Auditing Worksheets
Analysing and Presenting Data
Working with Multiple Workbooks
Exporting Excel Data
Importing and Exporting XML Data

Skills Gained

Key Topics

Target Audience

Prerequisites

Skills Gained

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

Automate worksheet functionality
Audit worksheets
Use a variety of different analysis tools
Work effectively with multiple workbooks
Export Excel data
Import and export XML data to and from a workbook
Create Excel forms

Key Topics

Lesson 1: Automating Worksheet Functionality

TOPIC A: Update Workbook Properties

Workbook Properties

TOPIC B: Create and Edit Macro

Macros
The Record Macro Dialog Box
Naming Macros
Visual Basic for Applications
Copying Macros Between Workbooks
Macro Security Settings

TOPIC C: Apply Conditional Formatting

Conditional Formatting
Conditional Formats
The Conditional Formatting Rules Manager Dialog Box
The New Formatting Rule Dialog Box
Clear Rules

TOPIC D: Add Data Validation Criteria

Data Validation
The Data Validation Dialog Box
Summary
Review Questions

Lesson 2: Auditing Worksheets

TOPIC A: Trace Cells

The Trace Cells Feature
Tracer Arrows

TOPIC B: Troubleshoot Invalid Data and Formula Errors

Invalid Data
The Error Checking Command
Error Types

TOPIC C: Watch and Evaluate Formulas

The Watch Window
Formula Evaluation

TOPIC D: Create a Data List Outline

Outlines
The Outline Group

Lesson 3: Analysing and Presenting Data

TOPIC A: Create Sparklines

Sparklines
Types of Sparklines
The Sparkline Tools – Design Tab

TOPIC B: Create Scenarios

Scenarios
The What-If Analysis Tools
The Scenario Manager Dialog Box

TOPIC C: Perform a What-If Analysis

Add-In Types
Goal Seek Feature
The Solver Tool

TOPIC D: Perform a Statistical Analysis with the Analysis ToolPak

Analysis ToolPak
The Data Analysis Dialog Box

TOPIC E: Create Interactive Data with Power View

The Power View Add-In
Enabling Power View
Creating a Power View

Lesson 4: Working with Multiple Workbooks

TOPIC A: Consolidate Data

Data Consolidation
The Consolidate Dialog Box
Consolidation Functions

TOPIC B: Link Cells in Different Workbooks

External References

TOPIC C: Merge Workbooks

The Compare and Merge Workbooks Feature

Lesson 5: Exporting Excel Data

TOPIC A: Export Excel Data

The Export Process

TOPIC B: Import a Delimited Text File

The Import Process
The Get External Data Group
Delimited Text Files
Methods of Importing Text Files

TOPIC C: Integrate Excel Data with the Web

The File Publishing Process
Publish as Web Page Dialog Box

TOPIC D: Create a Web Query

Web Queries
The New Web Query Dialog Box

Lesson 6: Importing and Exporting XML Data

TOPIC A: Import and Export XML Data

XML
XML Components
XML Schemas
XML Maps
The XML Source Task Pane
Import and Export XML Data

Target Audience

This course is designed for users of Microsoft Excel who need to use some of the more advanced features of Microsoft Excel 2016.

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

Prerequisites

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

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

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

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

Virtual-Classroom

20 Nov - 20 Nov

26 Feb - 26 Feb

26 Mar - 26 Mar

Book Course Book Course Book Course

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

Book Your Course

Testimonials

YOUR IT TRAINING PARTNER

FOLLOW US