Microsoft Excel: Intermediate / Advanced

Microsoft Excel Training Series | Level 2

Course Details

Duration: 2 days (9 am – 4 pm)

Microsoft® Excel® Versions: 2013 | 2016 | 2019 | 365 (Windows)

Delivery Methods (Instructor-led): In-person (Live classroom) | Virtual classroom

Course Dates: August 17/18, 2021 (Virtual classroom) | View schedule Course schedule

Course Fee: $495 CDN per person (Virtual classroom), $545 CDN per person (Bring your own device for in-person courses) or $595 CDN per person + HST (Avantix Learning provides device for in-person courses)*

Timing: Public scheduled courses run from 9:00 am to 4:00 pm (Eastern Time).

Virtual classroom courses: Our instructor-led virtual classroom courses are delivered in a virtual classroom environment. Students will be sent a virtual classroom invitation prior to the course.

Live classroom courses: Our instructor-led live classroom (in-person) courses are held in downtown Toronto at the Toronto Star Building, 1 Yonge Street, Suite 1801. Some courses are also held at an alternate downtown Toronto location.

Custom training: This course may be delivered at your site or ours as an instructor-led or virtual classroom solution. Contact us at info@avantixlearning.ca for more information including savings for custom group training.

Register for this training course.

Course Overview

This Microsoft Excel course is designed for users who want to move to the next level and focus on core competencies in Excel. Students will learn shortcuts to enter common functions as well as Excel key concepts and then move on to powerful conditional functions and tools including IF, SUMIF, COUNTIF, VLOOKUP and conditional formatting. Links will be created and managed between worksheets, workbooks and other programs. Students will review strategies to manage list or database data using filtering, sorting, subtotalling and validations. Using Excel data, charts will be generated and formatted and then a chart template will be created to save time. Students will also create pivot tables to summarize list or database data and will create simple macros to automate repetitive tasks. During this hands-on course, students will practice their skills and the instructor will include numerous tips, tricks and shortcuts. Each student will also receive a full course manual.

Prerequisite: Microsoft Excel: Introduction or equivalent knowledge and skills.

Next course(s) in this series: Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools or Microsoft Excel: Introduction to Power Query to Get and Transform Data.

Related training: View all Microsoft Excel courses >

INCLUDED IN THIS COURSE

  • Comprehensive course manual
  • Keyboard shortcuts quick reference
  • Sample and exercise files (new samples added frequently)
  • Refreshments (for classes conducted in Avantix Learning classrooms)
  • Certificate of completion
  • Follow-up email support

Course Topics

Working Effectively in the Microsoft Excel Environment

  • Adding buttons to customize the Quick Access Toolbar (QAT)
  • Controlling key Excel options
  • Navigating and selecting cells using keyboard shortcuts
  • Automating formatting using themes

Mastering Key Excel Concepts

  • Creating formulas using absolute, relative and mixed referencing
  • Naming cells and cell ranges
  • Using names in formulas
  • Editing named cells and ranges using the Name Manager

Tips and Techniques for Entering Core Excel Functions

  • Using different strategies to quickly enter core Excel functions including SUM, AVERAGE, MIN and MAX
  • Counting cells containing numeric or text values
  • Inserting the current static date or dynamic date
  • Extracting date information from cells containing dates using core date functions
  • Using keyboard shortcuts to insert and display formulas
  • Combining text strings in a formula

Using IF and VLOOKUP Functions to Display Conditional Results

  • Using the powerful IF function to output conditional results
  • Creating nested IF statements
  • Creating formulas using SUMIF and COUNTIF
  • Using the VLOOKUP function to look up data vertically in a list or database
  • Dealing with VLOOKUP errors

Applying Conditional Formatting

  • Highlighting cells using built-in conditional formats
  • Clearing conditional formatting
  • Editing rules for icon sets
  • Writing formulas in conditional formatting rules
  • Highlighting entire records using conditional formatting

Protecting and Formatting Worksheets and Workbooks

  • Protecting worksheets and workbooks
  • Using cell styles and themes to speed up formatting in multiple worksheets

Linking Worksheets and Workbooks

  • Using link cells in formulas to link worksheets and workbooks
  • Managing and updating links between workbooks
  • Importing linked and unlinked Excel data into Microsoft Word

Summarizing Cells or Ranges from Multiple Sheets

  • Understanding 3D references
  • Inserting formulas that extract totals from multiple sheets using 3D references
  • Considerations when inserting, moving and deleting sheets

Manipulating and Extracting List or Database Data

  • Identifying the parts of an Excel list or database
  • Best practices for setting up lists or databases correctly in Excel
  • Inserting records on a worksheet or adding records using a data form
  • Sorting records using the Sort feature with multiple sort keys
  • Using a custom sort to sort records
  • Inserting subtotals using the Subtotal feature
  • Applying filters with multiple criteria
  • Using custom criteria when filtering text, number and date fields
  • Setting up advanced filters for more complex filtering such as overcoming the two criteria custom filter maximum

Converting Data Sets to Tables

  • Why you should be using Excel tables
  • Converting lists to tables in 3 different ways
  • Creating formulas in tables
  • Displaying totals for filtered records
  • Converting tables back to normal ranges

Creating Drop-Down Lists Using Validations

  • Applying validations to ensure users enter the correct data in cells
  • Creating drop-down lists to make data entry easier and more accurate

Visualizing Data using Charts and Sparklines

  • Inserting charts on a spreadsheet or on a new Excel sheet
  • Applying chart formats to improve the appearance of your charts
  • Changing the chart type
  • Applying different types of formatting using various tips and tricks
  • Saving time by creating a chart template
  • Displaying trends using miniature charts in cells called Sparklines (in Excel 2010 and later)

Summarizing Data using Pivot Tables

  • Creating pivot tables to summarize Excel list or database data
  • Rearranging fields in a pivot table
  • Applying formatting to pivot tables and fields
  • Filtering a pivot table
  • Creating a pivot chart
  • Updating or refreshing a pivot table
  • Applying filtering using Excel’s slicers (in Excel 2010 and later)
  • Adding a pivot table button to the Quick Access Toolbar

Automating Tasks using Simple Macros

  • Creating simple macros using the macro recorder to automate tasks
  • Viewing VBA (Visual Basic for Applications) code created by the macro recorder
  • Controlling macro security
  • Running a macro using different strategies
  • Writing keyboard shortcuts

NOTE: This is not a VBA programming course. If you are interested in learning more about writing macros with Visual Basic for Applications, check out our Excel VBA courses designed for non-programmers:

Microsoft Excel:  Visual Basic for Applications (VBA) Macros | Introduction

Microsoft Excel: Visual Basic for Applications (VBA) Macros | Intermediate

*Prices subject to change.

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada (some live classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom training on a date that's convenient for you.

Copyright 2021 Avantix® Learning

Microsoft Excel training course Toronto represented by man pointing to icons on screen.

You may like

How to Expand the Formula Bar in Excel (2 Ways)

How to Expand the Formula Bar in Excel (2 Ways)

Easily Expand or Collapse the Formula Bar in Excel by Avantix Learning Team | Updated May 27, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) In Microsoft Excel, the Formula Bar appears below the Ribbon by default. When you enter data or a...

How to Convert Cm to Inches in Excel (or Inches to Cm)

How to Convert Cm to Inches in Excel (or Inches to Cm)

Convert Inches to Centimeters or Vice Versa in Excel Worksheets by Avantix Learning Team | Updated July 29, 2021 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) You can convert cm to inches (inches to centimeters or centimetres) using formulas...

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

Hide Notes and Comments in Microsoft Excel by Avantix Learning Team | Updated October 17, 2020 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019 and 365 (Windows) You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is...

How to Move a Pivot Table in Excel

How to Move a Pivot Table in Excel

Move a Pivot Table in the Same Worksheet or to a Different Worksheet in Microsoft Excel by Avantix Learning Team | Updated March 15, 2021 Applies to: Microsoft® Excel® 2013, 2016, 2019 and 365 (Windows) Moving a pivot table is not as simple as moving other objects in...

How to Lock and Protect Excel Worksheets and Workbooks

How to Lock and Protect Excel Worksheets and Workbooks

Lock and Protect Your Excel Worksheets and Workbooks by Avantix Learning Team | Updated May 31, 2021 Applies to: Microsoft Excel 2010, 2013, 2016, 2019 and 365 (Windows) You can lock and protect an Excel workbook by adding a password using your security options. If...

You may also like

How to Stop Design Ideas in PowerPoint (Disable PowerPoint Designer)

How to Stop Design Ideas in PowerPoint (Disable PowerPoint Designer)

3 Ways to Turn Off Design Ideas in PowerPoint by Avantix Learning Team | Updated June 21, 2021 Applies to: Microsoft® PowerPoint® 365 (Windows) PowerPoint Designer displays a Design Ideas task pane by default in PowerPoint 365 if Intelligent Services has been enabled....

7 Microsoft Project Navigation and Selection Shortcuts in Task Lists

7 Microsoft Project Navigation and Selection Shortcuts in Task Lists

Use These Simple Tricks in Gantt Chart Task Lists by Avantix Learning Team | Updated June 11, 2021 Applies to: Microsoft® Project® 2010, 2013, 2016, 2019 and 365 (Standard and Professional) If you are working in Gantt Chart View, you can use these keyboard shortcuts...

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning | 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada M5E 1W7 | info@avantixlearning.ca

Upcoming Training
Course
Microsoft Excel: Intermediate / Advanced (Online in virtual classroom format or in Toronto)
Location
Avantix Learning, 1 Yonge Street, Suite 1800 (Toronto Star Building),Toronto, Ontario,Canada
Next course starts
August 17, 2021
Next course ends
August 18, 2021

Pin It on Pinterest