Microsoft Excel 2019 / 365 Advanced Training

• Create, edit and format spreadsheets
• Navigate within worksheets and books
• Use Insert Function to create built-in functions
• Work with absolute references
• Create named ranges
• Create Tables within Excel
• Sort and filter data

Dual monitor setup is required for when attending this course via the QA Attend from Anywhere delivery method.

• Create macros to automate common tasks
• Create and manipulate PivotTables and PivotCharts
• Write advanced Lookup and Reference Functions
• Use advanced techniques to analyse data
• Ensure acceptable data entry with Data Validation

Module 1: Creating Macros

• Creating a Macro
• Comparing Relative and Absolute Macros
• Running a Macro
• Editing a Macro
• Assigning Shortcuts to Macros
• Macro Security
• Enabling Macros in the Backstage View
• Copying a Macro between Workbooks
• Document Properties and the Document Inspector

Module 2: Collaborating With Others

• Protection
• Track Changes
• Workbook Merging

Module 3 - Creating PivotTables, PivotCharts and Slicers

• Creating a PivotTable
• Amending the Fields in a PivotTable
• Updating a PivotTable
• Adding Calculations to a PivotTable
• Add Grouping to a PivotTable
• Creating a PivotChart
• Using Slicers
• PivotTables Timelines
• Creating PivotTables from Tables and Related Tables

Module 4: Array functions

• An Array Formula
• Array Functions
• FREQUENCY
• TRANSPOSE
• Single Cell or Multiple Cell Arrays
• Single Cell Array Functions
• Multiple Cell Array Functions

Module 5: Advanced Lookup and Reference Functions

• GETPIVOTDATA
• MATCH
• VLOOKUP
• ROW/COLUMN
• INDEX
• OFFSET
• INDIRECT
• LOOKUP (Array format)
• LOOKUP (Vector format)

Module 6: Analysing Data

• Creating a Single Input Data Table
• Creating a Two-Variable Data Table
• Creating Scenarios
• What-If Analysis using Goal Seek
• Consolidating Data
• Linking to External Workbooks

Module 7: Data Validation

• Number Validation
• Data List Validation
• Message Prompts and Alerts
• Conditional Data Validation
• Data Validation Errors

Module 8: Import and Export Data

• Exporting Excel Data
• Importing Delimited Text
• Importing a Web Query