Microsoft Excel 2010/2013 (Advanced)
Course Details
icon-dollar Fees: $345 + GST* icon-book Course ID: EXL007
icon-clock-o Duration: 1 Day icon-file-text Certificate: Only participation
icon-archive Delivery Type: Classroom icon-globe Language: English
icon-flag-checkered Locations: Perth

icon-pencil-square Notes:

  • Discount on multiple person bookings available. Give us a call for an obligation free quote.
  • If you would like the training to be arranged at your premises, we can do that. Please contact us via email or phone for quotation and further information.

About this course

This course will teach participants some of the more advanced and complex features of Microsoft Excel 2010/2013 such as working with pivot tables, HLOOKUP, VLOOKUP, charting pivoted data etc. The Course Highlight section below has in detail what features you will learn in this course.

We highly recommend that the participants have done our Microsoft Excel 2010/2013 Foundation and Microsoft Excel 2010/2013 Intermediate course or have knowledge of the topics before enrolling in this course.

Course Highlights

Section 1: Sorting and Filtering Data

In this section you will learn:

  • Sorting Data by Multiple Columns at the Same Time
  • Applying a Pre-Installed Custom Sort
  • Creating a Customized List and Performing a Custom Sort
  • Removing a Customised List
  • Using AutoFilter
  • Using AutoFilter to Perform Multiple Queries
  • Top 10 AutoFilter
  • Removing All AutoFilters from a Worksheet
  • Advanced Filter Criteria
  • Sub-Totalling
  • Removing Subtotals
  • Expanding and Collapsing Outline Detail Levels

Section 2: Charts

In this section you will learn about:

  • Creating a Combined Line and Column Chart
  • Adding a Secondary Axis to a Chart
  • Changing the Chart Type for a Particular Data Series
  • Adding a Data Series to a Chart
  • Removing a Data Series from a Chart
  • Re-Positioning Chart Title
  • Re-Positioning the Chart Legend
  • Moving and Formatting Chart Data Labels
  • Modifying Chart Axis Scales
  • Formatting an Axis to Display Using Commas
  • Inserting Images into Chart Columns
  • Inserting Images to Chart Bars
  • Formatting the Chart Plot Area Using a Picture
  • Formatting the Chart Area Using a Picture

Section 3: Pivot Tables

In this section you will learn how to:

  • Creating and Using a Pivot Table
  • Filtering and Sorting Data within a Pivot Table
  • Automatically Grouping Data in a Pivot Table and Renaming Groups
  • Manually Grouping Data in a Pivot Table and Renaming Groups

Section 4: Input Tables, Importing Text Files

In this section you will learn how to:

  • One-Input Data Tables
  • Two-Input Data Tables
  • What Is a Delimited Text File?
  • Importing a Delimited Text File

Section 5: Hyperlinks

In this section you will learn how to:

  • Use the PMT and FV functions
  • Understand and use logical functions

Section 6: Linking & Embedding

In this section you will learn how to:

  • What Is Embedding and Linking?
  • Linking Data within a Worksheet
  • Linking Cells between Worksheets within a Workbook
  • Linking Data between Workbooks
  • Linking Data from Excel to a Word Document
  • Linking an Excel Chart to a Word Document
  • Updating, Locking and Breaking Links

Section 7: Tracking and Reviewing Changes

In this section you will learn how to:

  • Enabling or Disabling the ‘Track Changes’ Feature
  • Sharing, Comparing and Merging Worksheets

Section 8: Scenarios

In this section you will learn how to:

  • Scenario Manager
  • Scenario Summary Reports

Section 9: Validating

In this section you will learn how to:

  • Data Validation – Whole Number
  • Data Validation – Decimal Number
  • Data Validation – List
  • Data Validation – Date
  • Data Validation – Time
  • Data Validation – Text Length
  • Customising a Validation Input Message and Error Alert
  • Removing Data Validation

Section 10: Auditing

In this section you will learn how to:

  • Tracing Precedent Cells
  • Tracing Dependent Cells
  • Cells with Missing Dependents
  • Showing All Formulas in a Worksheet
  • Inserting and Viewing Comments
  • Editing and Deleting Comments
  • Showing and Hiding Comments

Section 11: Macros

In this section you will learn how to:

  • Macro to Change the Page Set-Up
  • Macro to Apply a Custom Number Format
  • Macro to Format a Cell Range
  • Macro to Insert Fields into the Header or Footer
  • Assigning a Macro to a Button on the Quick Access Toolbar
  • Deleting Macros

Section 12: Passwords & Security Issues

In this section you will learn how to:

  • Adding ‘Open’ Password Protection to a Workbook
  • Adding ‘Modify’ Password Protection to a Workbook
  • Removing an ‘Open’ Password from a Workbook
  • Removing a ‘Modify’ Password from a Workbook
  • Password Protecting Cells and Worksheets
  • Hiding Formulas
  • Un-Hiding Formulas