Byron Employment

Byron Employment. Over 71,667 Jobs Australia Wide.

Delivery Method

  • Distance Learning
  • Price: $345.00

Course Overview

This course covers aspects of Microsoft Excel beyond building basic workbooks and deals with workbook automation and productivity features. Included areas are those such as workbook and worksheet consolidation and linking, data analysis tools, and automating workbooks using macros.

Delivery Method

This course is delivered by Workbook.

Learn To

  • nest functions to create complex formulas
  • use advanced filters to analyse data in a list
  • use a variety of data validation techniques
  • use goal seeking to determine the values required to reach a desired result
  • create, use and modify data tables
  • create and work with scenarios and the Scenario Manager
  • create, modify and work with PivotTables
  • use linking to create more efficient workbooks
  • combine data in separate worksheets or workbooks using consolidation
  • publish workbooks and worksheets for the web
  • summarise data using subtotals and relative range naming
  • confidently open workbooks that contain macros
  • create recorded macros in Excel
  • use the macro recorder to create a variety of macros.

Objectives

Nesting Functions Workshop

  • Scoping a Formula
  • Developing a Nested Function
  • Creating a Nested Function
  • Editing a Formula With Nested Functions
  • Copying a Formula With Nested Functions
  • Concatenation

Advanced Filters

  • Advanced Filter Concepts
  • Using an Advanced Filter
  • Extracting Records with Advanced Filter
  • Using Formulas in Criteria
  • Using Database Functions

Validations

  • Validation Techniques
  • Data Validation by Number Range
  • Testing Data Validation
  • Input Messages
  • Creating Error Alerts
  • Creating Drop-Down Lists
  • Using Formulas as Validation Criteria
  • Creating Custom Validation Criteria
  • Number Formats with Built-in Logic
  • Conditional Formatting
  • Copying Data Validation Settings

Goal Seeking

  • Goal Seek Components
  • Using Goal Seeking

Data Tables

  • Using a Simple What-If Model
  • Creating a One-Variable Table
  • Using One-Variable Data Tables
  • Creating a Two-Variable Data Table

Scenarios

  • Creating a Default Scenario
  • Creating Scenarios
  • Using Names in Scenarios
  • Displaying Scenarios
  • Creating a Scenario Summary Report
  • Merging Scenarios

PivotTables

  • PivotTable Theory
  • Creating a Simple PivotTable
  • Adding a Row Field to a PivotTable
  • Using the Page Field in a PivotTable
  • Filtering Row and Column Values
  • Formatting a PivotTable
  • Counting With PivotTables
  • PivotTable Summary and Display Options
  • Show Data as Percentages in PivotTables
  • Calculated Fields in PivotTables
  • Calculated Items in PivotTables
  • Creating a PivotChart
  • Modifying a PivotChart Via the PivotTable

    Linking Workbooks

    • Linking Data in Excel
    • Linking Within a Workbook
    • Linking Between Workbooks
    • Updating Links Between Workbooks

    Consolidation

    • Consolidating Data with Identical Layouts
    • Consolidating Data with Different Layouts
    • Consolidating Data Using a PivotTable

    Excel on the Web

    • Previewing Workbooks as Web Pages
    • Publishing a Static Worksheet
    • Adding To an Existing Web Page
    • Publishing an Interactive Web Page

    Summarising Data

    • Creating Subtotals
    • Using a Subtotaled Worksheet
    • Creating Nested Subtotals
    • Copying Subtotals
    • Using Subtotals with AutoFilter
    • Installing the Conditional Sum Wizard
    • Using the Conditional Sum Wizard
    • Creating Relative Names for Subtotals
    • Using Relative Names For Subtotals

    Macro Virus Control

    • Opening Workbooks with Macros
    • Setting Macro Security Levels
    • Trusting Publishers
    • Creating a Digital Signature
    • Removing a Trusted Publisher

    Recorded Macros

    • Setting Macro Security Levels
    • Recording a Simple Macro
    • Running a Recorded Macro
    • Relative Cell References
    • Running a Macro with Relative References
    • Viewing a Macro Module
    • Modifying a Recorded Macro

    Recorder Workshop

    • Preparing Data
    • Recording Summation Macros
    • Recording Consolidations
    • Recording Divisional Macros
    • Testing Macros
    • Creating Objects to Run Macros
    • Assigning A Macro to an Object
CLICK HERE TO BOOK THIS COURSE NOW!

WANT MORE INFORMATION?
CALL NOW ON (02) 9779 9800
CLICK ON THIS LINK TO ENQUIRE!

CLICK HERE TO
BOOK THIS COURSE NOW!
WANT MORE INFO?
CALL (02) 9779 9800 OR
CLICK HERE TO ENQUIRE

Search Courses

Quick Job Search