Byron Employment

Byron Employment. Over 160,281 Jobs Australia Wide.

Delivery Method

  • Classroom
  • Price: $740.00

Course Overview

This is a 2-day course split over 2 weeks designed for people who have a good working knowledge of Excel, and who need to use Excel’s advanced features such as Pivot Tables, Filters and Macros.

Delivery

Classroom

Duration

Locations

  • Public Schedule - Sydney
  • Group Bookings - all States and Territories across Australia

Learning Outcomes

By the end of the course participants should be able to:

  • Use outlines, range names, databases and the data form
  • Use autofilter and advanced filters
  • Work with data series
  • Use advanced functions
  • Modify charts
  • Use scenarios and goal seek
  • Use templates, data tables and solve problems
  • Use automatic formatting and styles
  • Work with views, consolidate worksheets
  • Create and revise PivotTables
  • Use, record and edit macros

Prerequisites

Participants should already:

  • Be using Excel on a regular basis
  • Have knowledge of Excel introduction topics

Course Outline 

Working with Outlines

  • Applying an OutlineCollapsing
  • Expanding an Outline
  • Modifying Outline Settings
  • Clearing an Outline
  • Using Auto Outline

Using Range Names

  • Jumping to a Named Range
  • Assigning Names
  • Using Range Names in Formulas
  • Creating Range Names from Headings
  • Using Range Names in 3-D Formulas
  • Using 3-D Range Names and Labels in Formulas
  • Using Labels to Define a Range
  • Using Multiple Stacked Headings

Working with Databases

  • Creating and Modifying a Database
  • Sorting Records by Multiple Fields
  • Using Data Validation
  • Creating Subtotals in a List
  • Database Functions
  • Autofilter and Advanced Filter

Working with the Data Form

  • Editing Data Form Records
  • Adding Data Form Records
  • Defining Criteria
  • Clearing Data Form Criteria
  • Deleting Data Form Records

Using AutoFilter and Advanced Filters

  • Enabling AutoFilter
  • Using AutoFilter to Filter a List
  • Creating a Custom AutoFilter
  • Disabling AutoFilter
  • Creating a Criteria Range
  • Using Comparison Criteria
  • Using an Advanced AND Condition
  • Using an Advanced OR Condition
  • Using Database Functions

Working with Data Series

  • Using the Series Command
  • Creating a Linear
  • Date, Growth Trend Series
  • Using a Stop Value

Using Advanced Functions

  • Using the VLOOKUP and HLOOKUP Function
  • Using the IF Function and Nested IF Function
  • Using the ISERROR Function
  • Using an AND Condition with IF
  • Using an OR Condition with IF

Advanced Charting

  • Adding, Removing and Formatting Gridlines
  • Formatting an Axis
  • Changing the Axis Scaling
  • Formatting the Data Series
  • Adding Data - Different Worksheets
  • Using a Secondary Axis
  • Changing Data Series Chart Types
  • Adding a Trendline
  • Creating User-defined Charts

Using Templates

  • Saving a Workbook as a Template
  • Using a Template
  • Editing a Template
  • Deleting a Template
  • Creating Default Templates

Using Auditing Tools

  • Displaying the Formula Auditing Toolbar
  • Displaying/Removing Dependent and Precedent Arrows
  • Removing All Tracer Arrows
  • Using the Auditing Tools Buttons

Using Scenarios and Goal Seeking

  • Using the Scenario Manager
  • Creating, Displaying and Editing a Scenario
  • Creating a Scenario Summary Report

Solving Problems

  • Using Solver
  • Saving a Solution as a Scenario
  • Changing a Constraint
  • Creating a Solver Report
  • Viewing Solutions using Scenarios

Using Automatic Formatting and Styles

  • Applying an AutoFormat
  • Changing AutoFormat Options
  • Extending List Formats and Formulas
  • Creating a Style by Example
  • Creating a New Style
  • Editing an Existing Style
  • Merging Styles

Working with Views

  • Creating, Displaying and Deleting a Custom View

Creating/Revising PivotTables

  • Creating a PivotTable Report
  • Adding Fields, Selecting a Page Field Item
  • Refreshing a PivotTable Report
  • Changing the Summary Function
  • Adding New Fields
  • Moving Fields
  • Hiding/Unhiding PivotTable Report Items
  • Deleting PivotTable Report Fields
  • Creating a Page Field Report
  • Formatting a PivotTable Report
  • Creating a PivotChart Report

Consolidating Worksheets

  • Consolidating Worksheets by Category or by Position

Sharing Workbooks

  • Saving a Shared Workbook
  • Viewing Users Sharing a Workbook
  • Viewing Shared Workbook Changes
  • Highlighting Changes
  • Resolving Conflicting Changes
  • Adding a History Worksheet
  • Reviewing Tracked Changes
  • Merging Shared Workbook Files

Using, Recording, Editing Macros

  • Opening a Workbook Containing Macros
  • Running a Macro
  • Using the Visual Basic Toolbar
  • Using the Visual Basic Editor Window
  • Recording a Macro
  • Using Relative References
  • Assigning a Macro to a Menu
  • Removing a Custom Menu Item
  • Deleting a Macro
  • Writing a New Macro
  • Entering Macro Comments
  • Copying, Editing and Typing Macro Commands
  • Running a Macro from the Code Window

Using Custom Toolbars and Menus

  • Creating a Custom Toolbar
  • Adding/Removing Toolbar Buttons
  • Assigning a Macro to a Button
  • Attaching a Custom Toolbar
  • Deleting a Custom Toolbar
  • Creating a Custom Menu

Creating a Macro Button

  • Using a Macro Button
  • Creating a Macro Button
  • Formatting a Macro Button
  • Moving/Sizing a Macro Button
  • Deleting a Macro Button
CLICK HERE TO BOOK THIS COURSE NOW!

WANT MORE INFORMATION?
CALL NOW ON 1 300 667 814
CLICK ON THIS LINK TO ENQUIRE!

CLICK HERE TO
BOOK THIS COURSE NOW!
WANT MORE INFO?
CALL 1 300 667 814 OR
CLICK HERE TO ENQUIRE

Search Courses

Quick Job Search