Analyzing Data with Excel Training

In this training, you'll deepen your knowledge by exploring specialised features such as task automation, advanced data analysis with PivotTables, and techniques for importing and cleaning data.

The course is hands-on, with guided exercises integrated throughout to reinforce learning through practice.

This course is designed for existing Excel users. You should already be comfortable with:

• Creating, editing, and formatting spreadsheets

• Navigating worksheets and workbooks

• Using built-in functions with Insert Function

• Working with absolute references

• Creating named ranges

• Creating tables in Excel

• Sorting and filtering data

By the end of this training, you will have gained knowledge and skills in the following areas:

• Import data from external sources

• Use data validation and collaboration tools

• Build PivotTables to analyze complex datasets

• Create dynamic array formulas

• Use formula auditing tools

• Automate tasks using macros

Module 1: Importing Data

• Importing delimited text files

• Using Power Query (Excel 2016+ and 365)

• Exporting to text files

Module 2: Collaborating with Others

• Using data validation

• Protecting cells and workbook structure

• Restricting access to documents

Module 3: PivotTables

• What is a PivotTable?

• Creating and modifying PivotTables

• Performing calculations

• Grouping fields, using slicers and timelines

• Creating PivotCharts

• Using GETPIVOTDATA

• Analyze Data/Ideas (Excel 365 only)

Module 4: Dynamic Array Formulas

• Introduction to arrays

• Legacy vs. dynamic array formulas

• Key functions and use cases

Module 5: Formula Auditing Tools

• Tracing precedents and dependents

• Identifying errors

• Viewing and evaluating formulas

• Using the Watch Window

Module 6: Creating Macros

• Macro basics and use cases

• Recording and running macros

• Relative vs. absolute macros

• Editing and assigning shortcuts

• Macro security settings