Excel Microsoft 365 Training Workshop – Tips, Tricks and New Functions
(0 Ratings)
No reviews
This training course introduces the new Microsoft 365 Excel functions that transform the way lists, lookups, filters, and automated reports are created. You will learn how to use modern functions such as FILTER, UNIQUE, SORT, and XLOOKUP to build faster, more flexible, and easier-to-maintain tools.
Using these new features, you will be able to create powerful reports and efficient worksheets with fewer formulas and without manually copying calculations. The techniques presented will help you simplify data analysis and automate many everyday Excel tasks. These new capabilities represent one of the biggest changes to Excel in many years.
The trainers
Your team of specialized trainers consist of Stéphanie Perreault, CPA, Marcel Dubé, Silène Saadeh, Stéphane L'Archevêque (ADM.A) and François Jasmin, CPA. They have extensive experience in finance and accounting or IT departments. This training was developed for the members of the National Payroll Institute.
Prerequisites
Excel (Office 365 version) is required to take this training, as the functions presented are not available in earlier versions.
This training is not designed for Apple computer users.
Prior completion of the Excel Level 2 seminar for payroll professionals offered by the NPI is recommended.
Participants must have access to 2 screens, or 2 laptops or a laptop and a tablet.
- Connect one laptop on ZOOM and upload the exercises on the second
- Connect the tablet on ZOOM and upload the exercises on the laptop.
Access to a microphone preferable
How to access your session:
One to three business days before the live event, you will be sent information on how to access your online seminar. If you do not receive the login information within this timeframe, please check your email SPAM folder. If it's not there, please contact membership@payroll.ca as soon as possible.
Introduction
- Required version of Excel
- What is a dynamic array?
- New functions used with dynamic array
- New way to spill formulas (formerly, copy / paste)
- How to block the spill of formulas
- Location to write a dynamic array function Meaning of the # sign
- New types of errors #SPILL!, #CALC! Limitation of use within the object "Table"
New functions working with dynamic array
UNIQUE and TRANSPOSE
- Return a list of distinct items
- Return a list of items appearing once
- Combine UNIQUE with other functions like TRANSPOSE
SORT
- Sort with one argument
- Sort with multiple arguments (multiple columns)
- Combine SORT with other functions
SORTBY
- Sort by another column not included in the result
FILTER
- Simple filter
- Filter with multiple condition
New lookup function
XLOOKUP
- Simple lookup with 3 arguments
- Integration of error handling: "#N/A"
- Bidirectional lookup
- The different search modes
- Lookup with multiple conditions
Impact of dynamic array on existing functions
- IFS – allows you to test multiple conditions in
- SWITCH – allows you to compare a value against multiple possible SUMIFS – allows you to sum values based on multiple criteria.
- Drop-down list
Conclusion
After registering online for this event, you will receive an email confirmation approximately 2 business days after the registration has been processed.
One to three business days prior to the live event, you will be sent information on how to access your online seminar.
Cancellations or Transfers: requests must be received in writing at least four (4) business days prior to the online seminar date. Please note a cancellation fee or transfer fee in the amount of $50 plus applicable taxes will be applied. Cancellation or transfer requests received after the login information has been provided are not eligible for a transfer or refund.