Excel training workshop – EXCEL 365 New Features for controller and analyst

$ 395


6 heures (9h à 16h)


This training covers the use of dynamic arrays with the functions of the Microsoft 365 version of Excel. We show you how to benefit from the power of dynamic arrays to create more powerful and dynamic reports. Analysis of complex data is now accessible to everyone. You will be able to create reports that seem very complex to configure, but that you can design using simple functions. The introduction of dynamic arrays is the biggest change to Excel in years. Technical precision You must have the Microsoft 365 version of Excel.
Voir plus
Summary of training content SECTION 1- Introduction
  • Required version of Excel
  • What is a dynamic array
  • New functions used with dynamic arrays
  • New way to propagate formulas (formerly copy / paste)
  • How to block the propagation of formulas
  • Location for writing a dynamic array function
  • Meaning of the # sign
  • New type of #EPARS error!
  • Disappearance of Ctrl + Shift + Enter (CSE)
  • Limitation of use inside the ARRAY object
& nbsp; SECTION 2-New functions for working with dynamic arrays
  • SORT

o Sort with an argument

o Sort with multiple arguments (multiple columns)

o Combine SORT with other functions


o Sort by another column not present in the result


o Simple filter

o Filter with multiple conditions

o Filter by ignoring empty cells

o Combine FILTER with various functions


o Generate a sequential list per line

o Generate a sequential list by column

o Combine SEQUENCE with other functions


o Return a list of distinct elements

o Return a list of items appearing once

o Return a list of distinct items excluding empty cells

o Return a list of distinct elements with several criteria

o Count the number of distinct elements

o Combine UNIQUE with other functions

  • ALEA

o Generate a random list on several columns

o Generate a random list with integer or decimal values ​​

o Combine ARRAY.ALEA with other functions

& nbsp; SECTION 3 - Impact of dynamic arrays on existing functions
  • SI.ENS
  • IF
  • The address of the cells, absolute, relative or mixed ($ A $ 1, $ A1, A $ 1, A1)
& nbsp; SECTION 4- New functions compatible with dynamic arrays
  • RESEARCH (VLOOKUP is retiring!)

o Bidirectional search

o Usage *? For partial searches

o The different search modes


o Usage *? For partial searches

o Search modes

& nbsp; SECTION 5- Examples of uses
  • Drop-down list

o Simple

o Dependent

  • Conditional formatting
  • Creating a calendar
  • Various interactive and dynamic reports
& nbsp; SECTION 6 - Conclusion


09/08/2024WebinaireMarcel Dubé
05/11/2024WebinaireMarcel Dubé
03/03/2025WebinaireMarcel Dubé
05/08/2025WebinaireMarcel Dubé