​Solutions with Complex Formulas and Functions (50576) 
Cours disponible en français ou en anglais - Training available in French or in English

Durée: 1 jour 

Objectifs du cours : 
This one-day instructor-led course provides students with the knowledge and skills to develop a greater understanding of the productivity tools and the function library in Microsoft Excel, in order to more efficiently manage, analyze and compute data stored within worksheets. The course focuses on user interfaces, program structure, language syntax, and implementation details.
 
Compétences acquises : 
Les stagiaires seront à même d'effectuer les tâches suivantes :
  • Use the SUMIFS function
  • Use the COUNTIFS function
  • Use the AND / OR functions nested in an IF function
  • Create complex formulae by nesting multiple IF functions
  • Use a selection of ERROR functions
  • Understand the VLOOKUP function
  • Create formulas using VLOOKUP
  • Use the IF and Match function with VLOOKUP function
  • Use the TRIM function to remove spaces
  • Convert text to a value using the VALUE function
  • Use the SUBSTITUTE function to manage replacement of data
  • Use the LEN function to count the number of characters in a cell or part of a cell
  • Use the TEXT function to retain numeric formatting when joining to text
  • Use the RIGHT, LEFT or MIDDLE functions to extract details from a cell
  • Use the FIND function to return the position of a character in a cell
  • Use Date functions and Time calculation to extract and format data
  • Describe an array
  • Describe the advantages and disadvantages of using arrays
  • Create single and multiple array formulas
  • Edit an array
  • Trace errors
  • Use Trace precedence and dependence arrows
  • Use Evaluate Formula
  • Use Watch Window
  • Resolving circular references
  • Apply conditional format using a formula
  • Manage conditional formatting rules
  • Use Custom Validation
  • Create a validation to prevent duplicate entries in a list
  • Create a validation to Limit the Total
  • Create a validation to prevent leading or trailing spaces

 
Connaissances requises : 
Before attending this course, students must have:
  • Intermediate Excel experience