Excel 2019 : Advanced techniques - Online training

1 The essential modules

2 Excel 2019 – Level 2: Intermediate skills

This module offers interactive training consisting of 60 training videos, 60 exercises and 5 additional resources.

3 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

4 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

5 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

6 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

7 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

8 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

9 Excel 2019 – Level 3: Advanced skills

This module offers interactive training consisting of 42 training videos, 42 exercises and 5 additional resources.

10 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

11 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

12 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

13 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

14 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

15 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

16 Excel 2019: use cases

Video: This module offers to view a video lasting 55 minutes.

17 Implementing quality control

• Introduction
• Setting up the statistical formulas
• Preparing the chart's setup
• Setting up the control chart
• Improving the control chart
• Conclusion

18 Analysing a library's attendance levels

• Introduction
• Creating the Attendance evolution chart
• Creating the pivot tables
• Conclusion

19 Monitoring the budget

• Introduction
• Creating the pivot tables
• Creating the pivot charts
• Creating the scoreboard
• Conclusion

20 Minimising costs

• Introduction
• Setting up the formulas
• Solving the problem with the Solver
• Conclusion

21 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.