Sell tickets and manage registrations
View my tickets and registrations
Sell more tickets through digital marketing
Add an event calendar to your website
Find events by location and categories
Microsoft Excel Training Course Toronto (Intermediate / Advanced) | Virtual Classroom
Microsoft® Excel® Intermediate / Advanced Course (2 Day)
As our clients and colleagues continue to practice social distancing in response to COVID-19, we are offering some of our public courses in live, instructor-led virtual classroom format. If you'd like to arrange custom virtual training, contact us at
Upcoming Dates: April 29/30, 2020 (Virtual Classroom) |
Duration: 2 days (9 am - 4 pm)
Microsoft Excel Versions: 2010 | 2013 | 2016 | 2019 | 365 (Windows)
Course Fee: $495 CDN per person + HST (virtual classroom), $545 CDN per person + HST (bring your own device) or $595 CDN per person + HST (Avantix Learning provides device)*
Virtual Classroom: Virtual classroom courses are delivered from 9:00 am to 4:00 pm (Eastern Standard Time) with a 15 minute break in the morning and in the afternoon and a 1 hour break for lunch.
Classrom Course Location: Downtown Toronto, Ontario, Canada | Our classroom courses are offered at the Toronto Eaton Centre or at the Toronto Star Building (some Avantix Learning courses may be held at an alternate downtown Toronto location). Location will be confirmed when you register.
Delivery Method: Instructor-led classroom training or virtual classrom training
Learning Path: Level 2
Prerequisite:
Custom training: Available on request.
This hands-on Microsoft Excel training course is designed for the user who wants to move to the next level and focus on core competencies in Excel. Students will review common functions and 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 summarize list data using pivot tables and will create simple macros to automate repetitive tasks. Throughout this course, the instructor will include numerous tips, tricks and shortcuts. Key takeaways include a full course manual, Quick Reference Guide with keyboard shortcuts as well as sample and exercise files.
Course topics
Working Efficiently 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
Using Shortcuts 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
Using the 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 List 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
Who will benefit
This course has been designed for Microsoft Excel users who have completed the
Can I bring my own device?
Absolutely! If you choose to bring your own device, you will receive a reduced rate. Be sure to bring a power cord and a mouse is helpful.
What's included
Included in this course:
- Comprehensive course manual
- Keyboard shortcuts quick reference
- Sample and exercise files
- Refreshments (for classes conducted in Avantix Learning classrooms)
- Certificate of completion
- Follow-up email support
Related Microsoft Office training
Other Microsoft Office courses include:
Check out these helpful articles
About Avantix Learning courses
Avantix Learning offers live instructor-led classroom (ILC) courses allowing personal interaction between the students and the instructor. We specialize in small classes, typically ranging from 6 to 8 students to ensure an optimal student to instructor ratio.
Public courses are offered in downtown Toronto at 1 Yonge Street (Toronto Star Building), Suite 1801, Toronto, Ontario, Canada. Some courses may also be offered at an alternate downtown Toronto location.
Our top-notch instructors have years of experience in the business world and students are encouraged to ask questions during our courses. Avantix Learning senior instructors have 15 or more years of experience. A complete profile of the instructor leading a specific course is available on request.
Questions?
Have a question? Email us at
You can also find information about all of our courses on our web site at
Refunds/Cancellations
We understand that your plans may change. Check out our
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.
