Length
1 day

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
  • Working with Multiple Workbooks
  • Auditing Worksheets
  • Analysing and Presenting Data
  • 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 email [email protected]

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

Virtual Classroom
August 23 2021 - August 23 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.