Microsoft Excel 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

• Use macros to automate repetitive tasks
• Setup workbooks to collaborate with other users
• Create and build PivotTables to analyse data
• Write array and advanced lookup functions
• Use ‘what-if’ analysis tools
• Use data validation
• Import external data into Excel workbooks

Module 1: Creating macros

• Introducing macros
• Creating macros
• Relative and absolute macros
• Running and editing a macro
• Assigning shortcuts
• Macro security

Module 2: Collaborating with others

• Protecting data, workbook structure and files
• Checking for collaboration issues
• Marking workbooks as final

Module 3: PivotTables

• What is a PivotTable?
• Creating and modifying PivotTables
• Calculations in PivotTables
• Grouping fields
• Slicers and Timelines
• Creating PivotCharts
• PivotTables and related sources

Module 4: Array formulas

• What is an array?
• Array formulas
• Array functions
• Single and multiple cell arrays

Module 5: Further lookup and reference functions

• VLOOKUP and HLOOKUP
• GETPIVOTDATA
• MATCH and INDEX
• XLOOKUP (Excel 365 only)

Module 6: Analysing data

• Input data tables
• Worksheet scenarios
• Goal Seek
• Consolidating data
• Linking to external workbooks

Module 7: Data validation

• Setting up data validation
• Message prompts and alerts
• Conditional data validation
• Data validation errors

Module 8: Importing data

• Importing delimited files
• Importing data with a web query
• Refreshing data