Microsoft Excel: Intermediate / Advanced

Microsoft Excel Training Series | Level 2

Course Details

Duration: 2 days

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

Delivery Methods: Instructor-led classroom (ILC) | Virtual classroom (VC) on request

Course Dates: December 17/18, 2018 | February 4/5, 2019 | View schedule Course schedule

Course Fee: $595 CDN per person + HST or BYOD (bring your own device): $545 CDN person + HST*

Note: Public scheduled dates for this course are delivered as live instructor-led classroom 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.

Location and timing: Public scheduled courses are held in downtown Toronto and run from 9:00 am to 4:00 pm.

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.

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

  • Customizing the Quick Access Toolbar
  • Setting important Excel options
  • Using keyboard shortcuts for navigating and selecting cells
  • Applying themes to automate formatting

Mastering Key Excel Concepts

  • Using absolute and relative referencing in formulas
  • Creating a formula with a mixed reference
  • Naming cells and cell ranges and using the names in formulas
  • Managing 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 with different types of data
  • 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

Displaying Conditional Results using IF and VLOOKUP Functions

  • Using the powerful IF function to output conditional results
  • Nesting the IF function within a longer expression
  • Creating formulas using SUMIF and COUNTIF
  • Looking up data from a list using the VLOOKUP function
  • Dealing with VLOOKUP errors

Applying Conditional Formatting

  • Highlighting key information using conditional formatting rules
  • Removing conditional formatting rules
  • Editing rules for icon sets
  • Writing formulas in conditional formatting rules

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

  • Linking worksheets and workbooks using links in formulas
  • Managing and updating links
  • Importing linked and unlinked Excel data into other programs

Manipulating and Extracting List or Database Data

  • Using key strategies to set up  lists or databases correctly in Excel
  • Identifying the parts of a list or database
  • 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 and using the Outline feature to display subtotals and grand totals
  • 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

Reducing Errors with Validations

  • Applying validations to ensure users enter the correct type of data in cells
  • Creating custom dialogs if users attempt to enter incorrect data
  • 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

Register Now for a public course at our downtown Toronto location at 1 Yonge Street, Suite 1801 (Toronto Star Building), Toronto, Ontario, Canada.

Do you have any other questions? Contact us!

If you are looking for more information regarding versions, check out our Microsoft Excel version overviews.

To request this page in an alternate format, contact our staff.

*Prices subject to change.

Copyright 2018 Avantix® Learning Inc.

Instructor in Microsoft Excel Intermediate / Advanced training course in Toronto.

You may like

How to Freeze Row and Column Headings in Microsoft Excel

As many Excel worksheets can become quite large, it can be useful to freeze row and column headings so that they are locked in place when you scroll through your worksheet. In Excel, you can freeze both row headings and column headings or just one.

10 Excel Tips for Working with the Subtotal Feature

You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature. Check out these 10 great tips which include showing and hiding subtotals, formatting subtotals, copying only subtotals and grand totals and more …

5 Awesome Excel Chart Shortcuts

If you create a lot of charts in Excel, you’re probably looking for shortcuts to speed up creation and formatting. Here are 5 useful shortcuts you can use with your Excel charts.

10 More Excel Pivot Table Shortcuts

Since pivot tables are one of the most powerful tools in Microsoft Excel for summarizing data, we’ve compiled a list of 10 more keyboard shortcuts for pivot table users.

You may also like

How to Add Notes in PowerPoint Files

You can add notes or speaker notes in your PowerPoint files using the Notes pane in Normal view. Notes may be viewed by a presenter during a presentation and may be printed as well. When you create your PowerPoint slides, you can add, edit and format notes.

How to Add Slide Numbers in PowerPoint (The Complete Guide)

You can add slide numbers in PowerPoint to some or all of your slides using slide layouts. If your slide layouts contain slide number placeholders, you can then apply those layouts to your slides so that your slide numbers will display in Normal view …

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 (Toronto course)
Location
Avantix Learning, 1 Yonge Street, Suite 1801 (Toronto Star Building),Toronto, Ontario,Canada
Next course starts
December 17, 2018
Next course ends
December 18, 2018

Pin It on Pinterest