Microsoft Excel Advanced

Course Details

ExcelAs an advanced Excel user, you may need to perform many varied tasks.  In this course you will manage external data using tables, analyse data using advanced functions and formula auditing tools and PivotTables. 

 

Learn how to cleanse data imported from external sources and perform what-if analysis using Goal Seek, Solver and advanced Data Tables.  Automate repetitive tasks using Macros and VBA.  For a more comprehensive use of VBA, refer to our Excel Visual Basic Course outline.

 Duration:

1 Day

Audience:

The course has been designed for experienced users of Excel who wish to used the advanced features of Excel.

Prerequisites:

Participants must be confident users of Excel who are able to create and manage multiple sheet workbooks.

Excel 2016

Advanced Functions

  • SUMIF/SUMIFS, COUNTIF/COUNTIFS
  • VLOOKUP, MATCH and INDEX
  • SUMPRODUCT
  • Array formulas

Auditing Tools

  • Overview of Auditing Tools
  • Tracer, Dependent and Precedent Arrows
  • Removing Tracer Arrows
  • Tracing Cells Causing Errors
  • Understanding Error Messages
    • #DIV/0; #NA; #Name; #Ref!, #Value
  • Overcoming Error Messages

Data Cleaning Tools

  • Importing data from external sources
  • Remove duplicates
  • Remove formatting
  • Remove leading or trailing spaces with TRIM()
  • Convert numbers stored as text
  • Convert numbers to text TEXT()

What If Analysis

  • Using Scenario Manager
  • Creating a Scenario
  • Displaying and Editing a Scenario
  • Creating a Scenario Summary Report
  • Using Goal Seek
  • Data Tables
    • One Variable
    • Two Variables
  • Using Solver
  • Creating a Solver Report

Table References

  • Understanding Structured References
  • Using Table References with Data Validation
  • Creating dynamic charts with tables

Sorting and Filtering

  • Sorting and Filtering records
  • Creating complex filters
  • Sorting on Multiple Columns
  • Sorting and Filtering by Colour
  • Performing Custom Sorts
  • Sorting Left to Right

Advanced Filters

  • Using a Criteria Range
  • Clearing the Filter
  • Using Comparison Criteria
  • Using AND OR conditions
  • Extracting Filtered records
  • Using Database functions
    • Dsum, Daverage, Dget

Pivot Table Reports

  • Creating a PivotTable
  • Adding PivotTable Report Fields
  • Editing and Refreshing a PivotTable Report
  • Changing The Summary Function
  • Moving Pivottable Report Fields
  • Hiding And Unhiding Items
  • Adding Multiple Data Fields
  • Custom Calculations In Pivot Tables
  • Creating Calculated Fields and Calculated Item
  • Grouping/Ungrouping Items
  • Sorting A Pivot Table
  • Filtering using Slicers
  • Using PivotTable Styles, Themes and Colours
  • PivotTable Layout Tools
  • Creating PivotCharts

Macros

  • Defining Macros
  • Recording and running a Macro
  • Personal Workbook
  • Visual Basic Editor Understanding Your Code
  • Running Code In VBA Environment
  • Recording a Relative Record
  • Debugging a Macro
  • Correcting Errors
  • Assigning A Macro To A Menu
  • Deleting A Macro From A Menu
  • Assigning A Macro To a Button

Excel 2013

Advanced Functions

  • SUMIF/SUMIFS, COUNTIF/COUNTIFS
  • VLOOKUP, MATCH and INDEX
  • SUMPRODUCT
  • Array formulas

Auditing Tools

  • Overview of Auditing Tools
  • Tracer, Dependent and Precedent Arrows
  • Removing Tracer Arrows
  • Tracing Cells Causing Errors
  • Understanding Error Messages
    - #DIV/0; #NA; #Name; #Ref!, #Value
  • Overcoming Error Messages

Data Cleaning Tools

  • Importing data from external sources
  • Remove duplicates
  • Remove formatting
  • Remove leading or trailing spaces with TRIM()
  • Convert numbers stored as text
  • Convert numbers to text TEXT()

What If Analysis

  • Using Scenario Manager
  • Creating a Scenario
  • Displaying and Editing a Scenario
  • Creating a Scenario Summary Report
  • Using Goal Seek
  • Data Tables
    - One Variable
    - Two Variables
  • Using Solver
  • Creating a Solver Report

Table References

  • Understanding Structured References
  • Using Table References with Data Validation
  • Creating dynamic charts with tables

Sorting and Filtering

  • Sorting and Filtering records
  • Creating complex filters
  • Sorting on Multiple Columns
  • Sorting and Filtering by Colour
  • Performing Custom Sorts
  • Sorting Left to Right

Advanced Filters

  • Using a Criteria Range
  • Clearing the Filter
  • Using Comparison Criteria
  • Using AND OR conditions
  • Extracting Filtered records
  • Using Database functions
    - DSUM, DAVERAGE, DGET

Pivot Table Reports

  • Creating a PivotTable
  • Adding PivotTable Report Fields
  • Editing and Refreshing a PivotTable Report
  • Changing The Summary Function
  • Moving Pivottable Report Fields
  • Hiding And Unhiding Items
  • Adding Multiple Data Fields
  • Custom Calculations In Pivot Tables
  • Creating Calculated Fields and Calculated Item
  • Grouping/Ungrouping Items
  • Sorting A Pivot Table
  • Filtering using Slicers
  • Using PivotTable Styles, Themes and Colours
  • PivotTable Layout Tools
  • Creating PivotCharts

Custom Views

  • Creating a Custom View
  • Display a View
  • Editing a View

Macros

  • Defining Macros
  • Recording and running a Macro
  • Personal Workbook
  • Visual Basic Editor Understanding Your Code
  • Running Code In VBA Environment
  • Recording a Relative Record
  • Debugging a Macro
  • Correcting Errors
  • Assigning A Macro To A Menu
  • Deleting A Macro From A Menu
  • Assigning A Macro To a Button

 

Excel 2010

Advanced Functions

  • SUMIF/SUMIFS, COUNTIF/COUNTIFS
  • VLOOKUP, MATCH and INDEX
  • SUMPRODUCT
  • Array formulas

Auditing Tools

  • Overview of Auditing Tools
  • Tracer, Dependent and Precedent Arrows
  • Removing Tracer Arrows
  • Tracing Cells Causing Errors
  • Understanding Error Messages
    • #DIV/0; #NA; #Name; #Ref!, #Value
  • Overcoming Error Messages

Data Cleaning Tools

  • Importing data from external sources
  • Remove duplicates
  • Remove formatting
  • Remove leading or trailing spaces with TRIM()
  • Convert numbers stored as text
  • Convert numbers to text TEXT()

What If Analysis

  • Using Scenario Manager
  • Creating a Scenario
  • Displaying and Editing a Scenario
  • Creating a Scenario Summary Report
  • Using Goal Seek
  • Data Tables
    • One Variable
    • Two Variables
  • Using Solver
  • Creating a Solver Report

Table References

  • Understanding Structured References
  • Using Table References with Data Validation
  • Creating dynamic charts with tables

Sorting and Filtering

  • Sorting and Filtering records
  • Creating complex filters
  • Sorting on Multiple Columns
  • Sorting and Filtering by Colour
  • Performing Custom Sorts
  • Sorting Left to Right

Advanced Filters

  • Using a Criteria Range
  • Clearing the Filter
  • Using Comparison Criteria
  • Using AND OR conditions
  • Extracting Filtered records
  • Using Database functions
    • Dsum, Daverage, Dget

Pivot Table Reports

  • Creating a PivotTable
  • Adding PivotTable Report Fields
  • Editing and Refreshing a PivotTable Report
  • Changing The Summary Function
  • Moving Pivottable Report Fields
  • Hiding And Unhiding Items
  • Adding Multiple Data Fields
  • Custom Calculations In Pivot Tables
  • Creating Calculated Fields and Calculated Item
  • Grouping/Ungrouping Items
  • Sorting A Pivot Table
  • Filtering using Slicers
  • Using PivotTable Styles, Themes and Colours
  • PivotTable Layout Tools
  • Creating PivotCharts

Custom Views

  • Creating a Custom View
  • Display a View
  • Editing a View

Macros

  • Defining Macros
  • Recording and running a Macro
  • Personal Workbook
  • Visual Basic Editor Understanding Your Code
  • Running Code In VBA Environment
  • Recording a Relative Record
  • Debugging a Macro
  • Correcting Errors
  • Assigning A Macro To A Menu
  • Deleting A Macro From A Menu
  • Assigning A Macro To a Button

Excel 2007

Advanced Functions

  • SUMIF/SUMIFS, COUNTIF/COUNTIFS
  • VLOOKUP
  • MATCH and INDEX
  • SUMPRODUCT
  • Array formulas

Auditing Tools

  • Overview of Auditing Tools
  • Tracer, Dependent and Precedent Arrows
  • Removing Tracer Arrows
  • Tracing Cells Causing Errors
  • Understanding Error Messages
    • #DIV/0; #NA; #Name; #Ref!, #Value
  • Overcoming Error Messages

Data Cleaning Tools

  • Importing data from external sources
  • Remove duplicates
  • Remove formatting
  • Remove leading or trailing spaces with TRIM()
  • Convert numbers stored as text
  • Convert numbers to text TEXT()

What If Analysis

  • Using Scenario Manager
  • Creating a Scenario
  • Displaying and Editing a Scenario
  • Creating a Scenario Summary Report
  • Using Goal Seek

Data Tables

  • One Variable and Two Variable Data Tables

Using Solver

  • Saving Solutions as Scenarios
  • Creating a Solver Report

Custom Views

  • Creating a Custom View
  • Display a View
  • Editing a View

Tables

  • Setting up an Excel Table
  • Adding Rows
  • Using Dynamic Headings
  • Table Tools and Formats
  • Using the Table Style Gallery
  • Deleting Table Records
  • Find Duplicates

Sorting and Filtering

  • Sorting and Filtering records
  • Creating complex filters
  • Sorting on Multiple Columns
  • Sorting and Filtering by Colour
  • Performing Custom Sorts
  • Sorting Left to Right

Advanced Filters

  • Using a Criteria Range
  • Clearing the Filter
  • Using Comparison Criteria
  • Using AND OR conditions
  • Extracting Filtered records
  • Using Database functions
    • Dsum, Daverage, Dget

Pivot Table Reports

  • Creating a PivotTable
  • Adding PivotTable Report Fields
  • Editing and Refreshing a PivotTable Report
  • Changing The Summary Function
  • Moving Pivottable Report Fields
  • Hiding And Unhiding Items
  • Adding Multiple Data Fields
  • Custom Calculations In Pivot Tables
  • Creating Calculated Fields and Calculated Item
  • Grouping/Ungrouping Items
  • Sorting A Pivot Table
  • Filtering using Slicers
  • Using PivotTable Styles, Themes and Colours
  • PivotTable Layout Tools
  • Creating PivotCharts

Macros

  • Defining Macros
  • Recording and running a Macro
  • Personal Workbook
  • Visual Basic Editor Understanding Your Code
  • Running Code In VBA Environment
  • Recording a Relative Record
  • Debugging a Macro
  • Correcting Errors
  • Assigning A Macro To A Menu
  • Deleting A Macro From A Menu
  • Assigning A Macro To a Button

Event Properties

Event Date 05-02-2018
Event End Date 05-02-2018
Individual Price $440.00
Course Code XLADV
Location
Corporate Training Solutions Australia
Level 19, Waterfront Place, 1 Eagle St Brisbane, QLD 4000 Australia
Corporate Training Solutions Australia

Group Rate

#Registrants Rate/Person($)
3 418.00
4 396.00
$440.00

Location Map

Share this event: