Microsoft Excel: Intermediate / Advanced
Microsoft Excel Training Series | Level 2
Course Details
Duration: 2 days (9 am – 4 pm)
Microsoft® Excel® Versions: 2016 | 2019 | 2021 | 365 (Windows)
Delivery Methods (Instructor-led): In-person (Live classroom) | Online in virtual classroom
Course Dates: December 16/17, 2024 (Online in virtual classroom or In-person) | January 15/16, 2025 (Online in virtual classroom or In-person) | February 11/12, 2025 (Online in virtual classroom or In-person) | View schedule
Course Fee: $495 CAD per person + HST (Virtual classroom), $545 CAD per person + HST (Bring your own device for in-person courses) or $595 CAD 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.
In-person classroom courses: Our instructor-led, live classroom (in-person) courses are held in downtown Toronto at 18 King Street East, Suite 1400, Toronto, Ontario, Canada. 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 virtual classroom or in-person solution. Contact us at info@avantixlearning.ca for more information including savings for custom group training.
Some public courses and / or formats and may be subject to a minimum enrollment requirement.
Course Overview
This Intermediate / Advanced Excel course is designed for users who want to move to the next level and focus on core competencies in Excel. During this instructor-led class, students will learn to enter common functions with a focus on Excel best practices and then move on to popular functions and tools including IF, SUMIF, SUMIFS, COUNTIF, COUNTIFS, VLOOKUP, XLOOKUP, conditional formatting, linking, data management, pivot tables and basic macros. Links will be created and managed between worksheets, workbooks and other programs. Students will learn strategies to manage list or database data using filtering, sorting, subtotalling and validations. Using Excel data, students will generate and format charts and will also create pivot tables to summarize list or database data. At the end of this course, participants will create simple macros to automate repetitive tasks. During this hands-on course, students will practice their skills and have the opportunity to ask questions and interact with the instructor. Each student will also receive a full course manual with tips, tricks and shortcuts as well as sample and exercise files.
Prerequisite: Microsoft Excel: Introduction or equivalent knowledge and skills.
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 Functions to Display Conditional Results
- Using the powerful IF function to output conditional results
- Creating nested IF statements
- Using the IFS function (available in specific Excel versions)
- Creating formulas using SUMIF, SUMIFS, COUNTIF and COUNTIFS
Using the VLOOKUP or XLOOKUP Functions to Perform a Lookup
- Using the VLOOKUP function to look up data vertically in a list or database
- Performing an exact and non-exact lookup
- Benefits of using a dynamic source
- Dealing with VLOOKUP errors
- Using the XLOOKUP function as an alternative to VLOOKUP (available in specific Excel versions)
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 Worksheets
- Locking and unlocking cells
- Enabling worksheet protection
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 data sets or lists to tables in 3 different ways
- Naming tables
- Formatting tables
- Converting tables back to normal ranges
Calculating in Tables
- Displaying totals for filtered records
- Creating formulas in tables
- Creating formulas outside of tables
Creating Drop-Down Lists Using Data Validation
- Applying data 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
Summarizing Data using Pivot Tables and Pivot Charts
- 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 calculations in pivot tables to summarize data
- Creating a pivot chart
- Updating or refreshing a pivot table
- Changing the data source
- Using a dynamic data source
- Applying filtering using Excel's slicers
- Adding a button to the Quick Access Toolbar to quickly create a pivot table
Getting Started with Power Query to Clean Data
- Creating a query
- Removing blank rows
- Filling blank cells with data
- Transforming data in other ways
- Loading a query
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
*Prices subject to change.
Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person 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 instructor-led virtual classroom or onsite training on a date that's convenient for you.
Copyright 2024 Avantix® Learning
Custom training (Onsite or Online in Virtual Classroom)
Register now for a public course or contact us at info@avantixlearning.ca for more information about any of our courses or to discuss custom training options (virtual classroom or in-person in live classroom).
FIND THE BEST EXCEL COURSE FOR YOUR NEEDS >
Related courses
Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools
Microsoft Excel: Introduction to Power Query (Get and Transform Data)
Microsoft Excel: Introduction to Power Pivot and Data Models
Microsoft Excel: Introduction to Dynamic Arrays
Microsoft Excel: Data Visualization
Microsoft Excel: Visual Basic for Applications (VBA) Macros | Introduction
You may like
What is Power Query in Excel?
What is Power Query in Excel and Why is It So Useful? by Avantix Learning Team | Updated October 18, 2024 Applies to: Microsoft® Excel® 2016, 2019, 2021 and 365 (Windows) Power Query in Excel is a powerful data transformation tool that allows you to import data from...
How to Stop or Control Green Error Checking Markers in Excel
Turn Off or Customize Error Checking Options (and Markers) in Excel by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) In Microsoft Excel, errors are flagged by default with a small green...
Excel Shortcuts to Zoom In and Out in Your Worksheets (4 Shortcuts)
Save Time in Microsoft Excel using Shortcuts to Zoom In or Out by Avantix Learning Team | Updated September 14, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) There are several mouse and keyboard shortcuts you can use to zoom in and out in...
How to Use Flash Fill in Excel (4 Ways with Shortcuts)
4 Ways to Use Flash Fill in Excel to Extract, Combine, Clean or Format Data by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 or 365 (Windows) You can use Flash Fill in Excel to extract, combine, clean or format...
How to Convert Text to Numbers in Excel (5 Ways)
5 Ways to Convert Numbers Stored as Text to Numbers in Microsoft Excel Worksheets by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) There are several ways to convert text to numbers in...
How to Insert Multiple Rows in Excel (4 Fast Ways with Shortcuts)
Insert Multiple Rows Quickly in an Excel Worksheet by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) You can quickly insert multiple rows in Excel using a drop-down menu, keyboard shortcuts or...
3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells
Apply or Remove Strikethrough Using Shortcuts in Excel by Avantix Learning Team | Updated September 14, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) You can apply strikethrough to text or values in Excel to cross out or create a line...
How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values
Quickly Fill Blanks with Zeros or Other Values in Excel Worksheets (0, -, N/A, Null or Other Text) by Avantix Learning Team | Updated September 14, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) You can quickly replace blank cells in...
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 October 14, 2022 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) You can convert cm to inches (inches to centimeters or centimetres) using...
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...
You may also like
How to Freeze Rows in Excel (One or Multiple Rows)
Freeze One or More Rows in an Excel Worksheet by Avantix Learning Team | Updated October 25, 2023 Applies to: Microsoft® Excel® 2010, 2013, 2016, 2019, 2021 and 365 (Windows) You can freeze one or more rows in an Excel worksheet using the Freeze Panes command. If you...
How to Show or Hide Gridlines in Excel
Show or Hide Gridlines in Excel Worksheets (+ Shortcuts) by Avantix Learning Team | Updated October 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) You can remove or hide gridlines in Excel worksheets to simplify worksheet design. By...
How to Insert Multiple Columns in Excel (4 Fast Ways with Shortcuts)
Insert Multiple Columns Quickly in an Excel Worksheet by Avantix Learning Team | Updated September 15, 2023 Applies to: Microsoft® Excel® 2013, 2016, 2019, 2021 and 365 (Windows) You can quickly insert multiple columns in Excel using a drop-down menu, keyboard...
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 |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca