Analyzing Data with Excel Training

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
• Use ‘what-if’ analysis tools
• 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
• PivotTables and related sources
• Analyze Data/Ideas (Excel 365 only)

Module 4: Analysing data

• Input data tables
• Worksheet scenarios
• Goal Seek
• Consolidating data
• Linked data types (Excel 365)

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