Analyzing Data with Excel Training in Australia

This course is aimed at existing Excel users who need to further their knowledge. Delegates are assumed to have experience of the following:

• 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

• Import data
• Use collaboration and data validation tools
• Create and build PivotTables to analyse data
• Create dynamic array formulas
• Use the formula auditing tools
• Use macros to automate repetitive tasks

Module 1: Importing Data

• Importing a delimited text file
• Using Power Query (2016+ and 365)
• Exporting to a delimited text file

Module 2: Collaborating with others

• Use Data Validation
• Protect cells
• Protect a workbook’s structure
• Restrict document access

Module 3: PivotTables

• What is a PivotTable?
• Creating and modifying PivotTables
• Calculations in PivotTables
• Grouping fields
• Slicers and Timelines
• Creating PivotCharts
• The GETPIVOTDATA function
• Analyze Data/Ideas (Excel 365 only)

Module 4: Dynamic Array Formulas

• Introducing arrays
• Legacy array formulas and functions
• Dynamic array formulas and functions

Module 5: The Auditing tools

• Tracing precedents and dependents
• Tracing errors
• Viewing formulas
• The Watch Window
• Evaluating formulas

Module 6: Creating macros

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