Microsoft Excel: Essential Features and Functions in Excel 365

Microsoft Excel Training Series | Level 3

Course Details

Duration: 1 day (9 am – 4 pm)

Microsoft® Excel® Versions: 365 (Windows)

Delivery Methods (Instructor-led): Live classroom (LC) | Virtual classroom (VC)

Course Dates: June 29, 2021 (Virtual classroom) | View schedule Course schedule

Course Fee: $275 CDN per person + HST (Virtual classroom), $295 CDN per person + HST (Bring your own device for live classroom courses) or $345 CDN per person + HST (Avantix Learning provides device for live classroom courses)*

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.

Note: Public scheduled courses are delivered as live instructor-led live classroom training or virtual classroom training (as indicated). Bring your own device rates apply to live classroom courses.

Register for this training course.

Course Overview

This course is designed for Excel users who have upgraded to Excel 365 or who have been using 365 but have not used the key new features and functions available in this version. Students will learn to use new functions such as CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, MINIFS, XLOOKUP and XMATCH. Workbooks will be created with dynamic arrays and then transformed using SORT, SORTBY, UNIQUE and FILTER functions. Dynamic drop-down menus will also be created using dynamic arrays. Students will learn new ways to collaborate, create map charts and use new smart data types. During this hands-on course, the instructor will include numerous tips, tricks and shortcuts. Each student will also receive a full course manual.

Prerequisite: Microsoft Excel: Intermediate / Advanced or equivalent knowledge and skills.

Note: This course does not include Power Query (Get and Transform) or Power Pivot which are available as separate courses. Some features and functions are also available in Excel 2019.

Location and timing: Public scheduled courses are held online in virtual classroom format or in downtown Toronto and run from 9:00 am to 4:00 pm (Eastern Time).

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

Combining Text Strings

  • Combining text strings using the CONCAT function
  • Using the TEXTJOIN function to combine text strings

Using Functions that Return Conditional Results

  • Calculate minimum or maximum values using multiple criteria with MINIFS or MAXIFS
  • Using the IFS function vs the IF function
  • Using the SWITCH function
  • Looking up values using XLOOKUP vs VLOOKUP
  • Looking up matching values using XMATCH vs INDEX and MATCH

Creating Variables

  • Creating variables in formulas using the LET function

Using Dynamic Arrays and Functions

  • What are dynamic arrays?
  • Understanding spilling and spill ranges
  • Using the FILTER and UNIQUE functions with dynamic arrays
  • Using the SORT and SORTBY functions with dynamic arrays
  • Creating dynamic drop-down menus using dynamic arrays
  • Sharing files containing dynamic arrays with users with older Excel versions

Using Smart Data Types

  • Inserting live stock data
  • Inserting linked geographic data

Creating Innovative Charts for Visualization

  • Inserting funnel, waterfall and histogram charts
  • Creating map charts
  • Inserting 3D map charts

Collaborating with Other Users

  • Collaborating with other users in real time
  • Viewing and restoring changes in real time

Setting Pivot Table Default Layouts

  • Setting default layouts for pivot tables

Inserting Data from Pictures or PDFs

  • Taking pictures on a smart phone and converting to editable Excel data
  • Converting a PDF into an editable Excel document

Administative Tools

  • Viewing workbook statistics
  • Getting insights for analyzing data using Ideas
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.

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

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

You may like

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

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

You can convert cm to inches (or inches to cm) using formulas with operators or functions in Excel. This is useful since the regional settings on a computer affect the default measurement system used in Excel and other programs.

How to Hide Comments or Notes in Excel Workbooks

How to Hide Comments or Notes in Excel Workbooks

You can hide comments and notes in Excel workbooks. In addition to adding comments in Excel, there is a little known function called the N function that you can use to enter notes directly within a formula. The N function has been around for a long time so you can use it in older versions of Excel.

How to Delete Blank Rows in Excel (5 Ways)

How to Delete Blank Rows in Excel (5 Ways)

You can delete blank rows in Microsoft Excel worksheets manually or you can use several tricks to remove multiple blank rows quickly. Check out 5 different ways to easily delete blank rows in your data.

How to Move a Pivot Table in Excel

How to Move a Pivot Table in Excel

Moving a pivot table is not as simple as moving other objects in an Excel worksheet or workbook. You will typically need to use the Move PivotTable command in the Ribbon to move a pivot table to a different area on a worksheet or to a different sheet in the same workbook.

You may also like

How to Use a Laser Pointer in PowerPoint (with Shortcuts)

How to Use a Laser Pointer in PowerPoint (with Shortcuts)

During a PowerPoint slide show, you can change your mouse into a laser pointer to focus attention on a specific area on your slide. You can show or hide the built-in laser pointer using keyboard shortcuts or by using the context menu.

How to Add Audio in PowerPoint Presentations

How to Add Audio in PowerPoint Presentations

You can add audio in PowerPoint from a file on your PC or from a shared drive. Audio is played during a slide show and you can insert music, sound clips, or voice recordings. Since audio files are copied into PowerPoint presentations by default, they can increase file size and may need to be compressed. To play audio, your computer must be equipped with a sound card and speakers.

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: Essential Features and Functions in Excel 365 (in Toronto or online in virtual classroom format)
Location
Avantix Learning, 1 Yonge Street, Suite 1800 (Toronto Star Building),Toronto, Ontario,Canada
Next course starts
June 29, 2021
Next course ends
June 29, 2021
Description
This course is designed for Excel users who have upgraded to Excel 365 or who have been using 365 but have not used the key new features and functions available in this version. Students will learn to use new functions such as CONCAT, TEXTJOIN, IFS, SWITCH, MAXIFS, MINIFS, XLOOKUP and XMATCH. Workbooks will be created with dynamic arrays and then transformed using SORT, SORTBY, UNIQUE and FILTER functions. Dynamic drop-down menus will also be created using dynamic arrays. Students will learn new ways to collaborate, create map charts and use new smart data types. During this hands-on course, the instructor will include numerous tips, tricks and shortcuts. Each student will also receive a full course manual.

Pin It on Pinterest