MS Excel Advanced

Duration: One Day

Interested in this course?

Complete the form and let us know your preferred training date. Our team will contact you to discuss your requirements and arrange a suitable course schedule.

Whether you're booking for yourself or a team, we'll work with you to find a convenient training date.

Prefer to get in touch directly? You can also email us at forrest@forresttraining.com.au, and we'll be happy to assist you.

    Target Audience & Purpose

    Excel Advanced is for people who can already create moderately complex spreadsheets, and want to add more sophistication and automation.

    Objectives

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

    • Use outlines, range names, databases & the data form
    • Use AutoFilter & advanced filters
    • Work with Data Series
    • Use advanced functions
    • Modify charts
    • Use Scenarios & Goal Seek
    • Use templates, data tables & solve problems
    • Use automatic formatting & styles
    • Work with views, consolidate worksheets
    • Create & revise PivotTables
    • Use, record & edit macros

    Course Inclusions

    • Comprehensive learning materials incl. exercise files
    • Post course telephone support
    • ‘Certificate of Attendance’ on completion of course
    • Morning & afternoon tea

    Course Prerequisites

    Participants should already:

    • Be using Excel on a regular basis and or
    • Knowledge of Excel Introduction topics

    MS Excel Advanced Course Content

    Download Exercise Files

    Working with Outlines
    Apply an Outline, Collapse/Expand an Outline, Modify Outline Settings, Clear an Outline, Use Auto Outline, Create Subtotals in a List, Remove Subtotals from a List

    Using Multiple Workbooks
    Open and Arrange Multiple Workbooks, Save a Workspace, Create and Maintain Links

    Using Range Names
    Jump to a Named Range, Assign Names, Use Range Names in Formulas, Create Range Names from Headings, Apply Range Names, Delete Range Names, Use Range Names in 3-D Formulas, Create 3-D Range Names, Use 3-D Range Names in Formulas

    Working with Tables
    Use Tables, Create a Table from Existing Data, Change the Table Name, Change the Table Style, Change Table Style Options, Create a Total Row, Add Table Rows and Columns, Insert/Delete Table Rows/Columns, Create a Calculated Column, Select Parts of a Table, Move a Table, Sort Data by Multiple Levels, Use Text Filters, Use Number Filters, Use Data Validation, Validate Data using a List, Create a Custom Error Message, Remove Data Validation

    Working with Advanced Filters
    Create a Criteria Range, Use a Criteria Range, Show All Records, Use Comparison Criteria, Use an Advanced And Condition, Use an Advanced Or Condition, Copy Filtered Records, Use Database Functions, Find Unique Records, Remove Duplicates from a Table

    Using Advanced Functions
    Use the VLOOKUP and HLOOKUP Function, Use the IF Function and Nested IF Function, Use the ISERROR Function, Use an AND Condition with IF, Use an OR Condition with IF, Use the ROUND Function, Limit the Precision of Numbers

    Advanced Functions
    Add, Remove and Format Gridlines, Format an Axis, Change the Axis Scale, Format the Data Series, Add Data – Different Worksheets, Use a Secondary Axis, Change Data Series Chart Types, Add a Trendline, Create User-defined Charts, Create, Customise, Working with Sparklines

    Advanced Charting
    Add, Remove and Format Gridlines, Format an Axis, Change the Axis Scale, Format the Data Series, Add Data – Different Worksheets, Use a Secondary Axis, Change Data Series Chart Types, Add a Trendline, Create User-defined Charts, Create, Customise, Remove Sparklines

    Using Templates
    Save a Workbook as a Template, Use a Template, Edit a Template, Delete a Template, Create Default Templates

    Using Auditing Tools
    Display the Formula Auditing Toolbar, Display/Remove Dependent and Precedent Arrows, Remove Tracer Arrows, Use Auditing Tools Buttons

    Using Scenarios & Goal Seeking
    Use the Scenario Manager, Create, Display and Edit a Scenario, Create a Scenario Summary Report, Use Goal Seek

    Solving Problems
    Use Solver, Save a Solution as a Scenario, Change a Constraint, Create a Solver Report, View Solutions using Scenarios

    Using Automatic Formatting & Styles
    Apply an AutoFormat, Change AutoFormat Options, Extend List Formats and Formulas, Create a Style by Example, Create a New Style, Edit an Existing Style, Merge Styles

    Using Conditional and Custom Formats
    Apply Conditional Formats, Change a Conditional Format, Add a Conditional Format, Create a Custom Conditional Format, Use Data Bars, Delete a Conditional Format, Create a Custom Number Format

    Working with Views
    Create, Display and Delete a Custom View
    Consolidating Worksheets
    Consolidating Worksheets by Category or by Position

    Sharing Workbooks
    Save a Shared Workbook, View Users Sharing a Workbook, View Shared Workbook Changes, Highlight Changes, Resolve Conflicting Changes, Add a History Worksheet, Review Tracked Changes, Merge Shared Workbook Files

    Creating/Revising PivotTables
    Create a PivotTable Report, Add PivotTable Report Fields, Select a Report Filter Field Item, Refresh a PivotTable Report, Change the Summary Function, Add New Fields to a PivotTable Report, Move PivotTable Report Fields, Use Expand and Collapse Buttons, Hide/Unhide PivotTable Report Items, Delete PivotTable Report Fields, Create Report Filter Pages, Inserting & Using Slicers, Format a PivotTable Report, Create a PivotChart Report

    Using, Recording, Editing Macros
    Change Macro Security Settings, Open a Workbook Containing Macros, Run a Macro, Use a Shortcut Key, Use the Visual Basic Editor Window, Record a Macro, Assign a Shortcut key, Use Relative References, Delete a Macro, Write a New Macro, Enter Macro Comments, Copy, Edit and Type Macro Commands, Run a Macro from the Code Window

    Creating a Macro Button
    Use, Create, Format, Move/Size & Delete a Macro Button