You master the essential commands and wish to evolve towards more advanced techniques.
This training programme will make it possible for you to discover or improve your knowledge on formula, tools for calculations, pivot tables, sharing workbooks…
Duration
10.5 hours
Regular fee
$199
Objectives of the training
At the end of the training, the participant will be able to use the advanced features of Excel 2019.Targeted audience
Users who have mastered the essential commands and wish to move towards more advanced techniques.Prerequisite
The essential commands must be mastered in order to access Excel’s advanced techniques without difficulty.Trainers
Upcoming information
Course architecture
The essential modules
Excel 2019 – Level 2: Intermediate skills
This module offers interactive training consisting of 60 training videos, 60 exercises and 5 additional resources.
Move on to more complex tables
• Displaying several lines of data in one cell
• Absolute reference in a formula
• Copying values, copying with a link, transposing
• You need to know: Calculation functions
• Control date, date formats
• Simple conditions
• Custom formats
• Applying a conditional format
• Managing conditional formats
• Naming a range of cells
• Validation rules
• Multiple cell ranges and calculations in functions
Let charts explain your data
• You need to know: Charts
• Creating and positioning a chart
• Managing a chart
• Selecting items in a chart
• Adding and removing items
• Formatting chart items
• Modifying a chart's text items
• Legend and plot area
• Printing and page layout of a chart
Create striking charts
• Modifying data labels
• Data series and chart axes
• Managing series
• Chart type options
• Managing chart templates
• Creating a 2D map type chart
• Creating a sparkline
• Managing sparklines
Illustrate your data
• Creating a drawing object
• Selecting and deleting objects
• Copying and moving objects
• Resizing a drawing object
• Modifying a text box
• Modifying a drawing
• Formatting drawing objects
• Inserting a picture
• Managing pictures
• Rotating and aligning objects
• Stacking and grouping objects
Database functionality with an Excel table
• You need to know: Flash Fill
• Subtotal rows
• Creating and managing a data table
• Presenting and sorting data in a data table
• Adding calculations in a data table
• Automatic filtering
• Custom filters
• Blank cells and duplicates
• You need to know: The criteria range
• Criteria ranges
• Copying rows filtered using a criteria range
• Statistics with a criteria range
See your data from a different angle: pivot tables and charts
• You need to know: Pivot tables and charts
• Creating a pivot table
• Modifying and adding calculations to a pivot table
• Changing a pivot table's source
• Selecting, copying, moving or deleting in a pivot table
• Defining the layout and the presentation of a pivot table
• Formatting a pivot table
• Filtering a pivot table
• Filtering using slicers
• Creating a pivot table based on several tables
• Adding calculated fields or elements
• Grouping data
• Pivot charts
Excel 2019 – Level 3: Advanced skills
This module offers interactive training consisting of 42 training videos, 42 exercises and 5 additional resources.
Grasped the basics? Take the next step
• Converting data
• Creating a data series
• Custom views
• Attaching a comment to a cell
• Detecting errors
• Evaluating formulas
• The Watch Window
Find the right function for your calculation
• Lookup functions
• Text functions
• Time calculations
• Date calculations
• Conditions with AND, OR, NOT
• Nested conditions
• Conditional functions
• Array formulas
• Calculating with copied values
• Consolidation
• Financial functions
• Double entry data table
Discover unexpected features
• Goal seek
• The Solver
• Managing scenarios
• Hyperlinks
• You need to know: Styles and templates
• Creating and customising a theme
• Creating and working with templates
• You need to know: Importing and exporting data with Excel
• Modifying and deleting templates
• Diffusing Excel data: PDF, XPS, e-mail and Web page
• Converting Excel files
• Importing data
• Updating and managing imported data
• Workbook properties
Share and collaborate effectively
• Protecting workbooks
• Protecting specific cells
• Sharing a workbook
• Tracking changes in a workbook
• Merging workbooks
• Finalising a workbook
• Digital signatures
• Creating a form
• Protecting and using a form
Find out more…
• Macros
• Versions and recovery of files
• You need to know: Customising the ribbon
• You need to know: Best practices with a spreadsheet application
• You need to know: Collaborative work
Office 2019: use cases
Excel, Word or PowerPoint with an Office 365 subscription
Video: This module offers to view a video lasting 22 minutes.
• The Office suite with or without an Office 365 subscription: what are the differences?
• Creating and saving Office files in OneDrive, SharePoint Online or Teams
• Editing an Office file from OneDrive, SharePoint or Teams
• Sharing Office files with other contributors or people from outside your organisation
• Co-editing a file
Excel 2019: use cases
Video: This module offers to view a video lasting 55 minutes.
Implementing quality control
• Introduction
• Setting up the statistical formulas
• Preparing the chart's setup
• Setting up the control chart
• Improving the control chart
• Conclusion
Analysing a library's attendance levels
• Introduction
• Creating the Attendance evolution chart
• Creating the pivot tables
• Conclusion
Monitoring the budget
• Introduction
• Creating the pivot tables
• Creating the pivot charts
• Creating the scoreboard
• Conclusion
Minimising costs
• Introduction
• Setting up the formulas
• Solving the problem with the Solver
• Conclusion
Complementary material
Excel 2019 and Office 365 versions – Reference book
Online book: This module includes digital course material.
Excel 2019 – Level 1: Essential skills
This module offers interactive training consisting of 45 training videos, 45 exercises and 6 additional resources.
Training Notes
Complete online self-paced training. Accessible anytime from anywhere. One year access to the training and the digital book.
Private or personalized training
Do you have several employees interested in the same training course? Whether in person at your offices or remotely in virtual mode, we offer private training courses tailored to your team's needs. Group rates are available. Contact us for more details or request a quote online.
Request a quoteDuration
10.5 hours
Regular fee
$199
Private or personalized training
Do you have several employees interested in the same training course? Whether in person at your offices or remotely in virtual mode, we offer private training courses tailored to your team's needs. Group rates are available. Contact us for more details or request a quote online.
Request a quote