Master advanced Excel features: data analysis, pivot tables, functions, and optimization tools
Les avantages de la gestion à distance ne sont plus à démontrer. Diminution des temps de déplacement, travail plus fluide, attrait pour les employés, diminution des besoins en espace locatifs, collaboration accrue et augmentation de la productivité n’en sont que quelques-uns.
Or, il existe aujourd’hui une multitude de plateformes permettant de mettre en place un tel mode de travail. Cependant, ces outils ne font pas le travail à eux seuls. S’ils sont mal utilisés, ils risquent de provoquer des pertes de productivité supérieures aux gains qu’ils auraient dû engendrer.
À la fin de cette formation, les participants connaitront les grandes règles pour bien structurer les plateformes de travail collaboratif. Ils seront aussi familiarisés avec les gestes clés qui leur permettront de faire un bon suivi du travail et mobiliser les collaborateurs.
Is it for you ?
All users who have mastered the essential basic commands and wish to discover or deepen their knowledge of advanced functions.
Prerequisites
You need to master the basic commands to access Excel's advanced functions without difficulty.
What You'll Walk Away With
- ✓ Analyze complex datasets using advanced tables, filters, and pivot tables
- ✓ Apply advanced functions for conditional logic and complex calculations
- ✓ Create dynamic and advanced charts to present data effectively
- ✓ Optimize analysis using tools like Solver, scenarios, and goal seek
- ✓ Secure, share, and automate workbooks with protection, forms, and macros
Training content
Essential modules
1 Excel 2021 - Level 2: Intermediate knowledge
This module offers interactive training with 61 training videos, 61 practice exercises and 5 additional information points.
2 Move on to more complex tables
- Display multiple rows in a cell
- Absolute reference in a formula
- Copy values, copy with link or transpose
- Worth knowing: Entering calculation functions
- System date and date format
- Simple condition
- Custom format
- Conditional formatting
- Managing conditional formatting
- Cell range name
- Validation criteria
- Cell range in a function
3 Present your figures on graphs
- Worth knowing: Graphs
- Creating and moving a graph
- Managing a graph
- Selecting chart elements
- Adding and deleting elements
- Formatting chart elements
- Modifying graph text elements
- Legend and plotting area
- Graph printing and layout
4 Enhance the presentation of your graphs
- Modifying data labels
- Data series and chart axes
- Managing series
- Chart type options
- Managing chart templates
- Creating a 2D chart
- Creating sparkline charts
- Managing sparkline charts
5 Enhance your tables
- Creating graphic objects
- Select and delete objects
- Copying and moving objects
- Sizing a graphic object
- Modifying a text box
- Modifying a drawing
- Formatting drawing objects
- Inserting an image
- Image management
- Rotating and aligning objects
- Overlaying and grouping objects
6 Exploit your data list tables
- Worth knowing : Instant fill
- Calculating subtotals
- Creating and managing a data table
- Presenting and sorting data in a data table
- Adding calculations to a data table
- Automatic filters
- Custom filters
- Empty values and duplicates
- Worth knowing: The criteria zone
- Using a criteria area
- Filtering and copying lines by criteria area
- Statistics with criteria fields
- Data sorting and filtering functions
7 Create and use pivot tables and charts
- Worth knowing : Pivot tables and pivot charts
- Creating a pivot table
- Modifying and adding calculations to a pivot table
- Modifying the source of a pivot table
- Selecting, copying, moving and deleting pivot tables
- Structure and print a pivot table
- Formatting a pivot table
- Filtering a pivot table
- Filtering using segments
- Creating a pivot table based on
8 Excel 2021 - Level 3: Advanced Knowledge
This module offers interactive training with 39 training videos, 39 practice exercises and 5 additional information points.
9 Gain in efficiency
- Data conversion
- Creating data series
- Customized views
- Cell annotation
- Error checking
- Formula evaluation
- Spy window
10 Use advanced calculation functions
- Look-up table and search functions
- Text functions
- Time calculations
- Date calculations
- AND, OR, NOT conditions
- Nested conditions
- Conditional functions
- Matrix formulas
- Copy calculations
- Consolidation
- Financial functions
- Double entry table
11 Discover unsuspected functions
- Target value
- The solver
- Scenario management
- Hypertext links
- Worth knowing : Styles and templates
- Creating and customizing a theme
- Creating and using a template
- What you need to know: Importing, exporting and Excel interoperability
- Modifying and deleting a template
- Excel data distribution: PDF, XPS, e-mail and Web page
- Converting Excel files
- Importing data
- Updating and managing imported data
- Workbook properties
12 Share in total security
- Binder protection
- Cell protection
- Share a workbook
- Finalize a workbook
- Creating a form
- Protecting and using a form
13 Find out more
- Macro commands
- File versioning and recovery
- What you need to know : Ribbon customization
- Good to know : Spreadsheet best practices
- Did you know? Collaborating on a workbook
14 Microsoft Office: use cases
Excel, Word or PowerPoint with a Microsoft 365 subscription
Video
This module includes a 23-minute video.
- Office with or without a Microsoft 365 subscription: what are the differences?
- Creating and saving Office documents on OneDrive, SharePoint Online or Teams
- Edit Office documents from OneDrive, SharePoint or Teams
- Share Office documents with colleagues or people outside your organization
- Co-edit files
15 Excel 2021: use cases
Video: This module includes a 55-minute video.
16 Setting up a quality control system
- Case study presentation
- Setting up statistical formulas
- Preparing to set up the graph
- Setting up the control chart
- Improving the control chart
- Conclusion
17 Analyzing library use
- Case study
- Creating the Evolution of patronage graph
- Creating pivot tables
- Conclusion
18 Budget monitoring
- Case study
- Creating pivot tables
- Creating pivot charts
- Creating the dashboard
- Conclusion
19 Solving a cost minimization problem
- Case study
- Setting up formulas
- Solving the problem with the Solver
- Conclusion
20 Additional modules
Excel 2021
Digital book: This module provides access to a digital course material.
21 Excel 2021 - Level 1: Essential knowledge
This module offers interactive training with 45 training videos, 45 practice exercises and 5 additional information points.
📌 Practical information
100% online training. Accessible anytime from anywhere for one year. If you have any questions about registration, the language of instruction, or the cancellation terms, please consult our FAQ