The WSQ Microsoft Excel Advanced course is structured around a part-to-whole sequencing method, unfolding through six progressively linked learning units. It kicks off, Advanced Formulas and Functions, where participants dive into complex business statistical formulas and functions. This unit covers summarising and analysing both categorical and numerical data sets, including the use of nested conditions, and lookup, text, date, and time functions. 

Next, learners will leverage Excel's built-in data analysis tools, Managing and Analysing Data Ranges, to discern trends and patterns. This focus on data analysis paves the way to next learning unit, Organising and Summarising Data, where common statistica l software applications and techniques for data organisation and summary, including scenario reports and consolidation of data, are taught.  

For the next learning unit, Working with PivotTables, it equips learners with the skills to create and customise PivotTables with slicers. Moving to the stage, Working with Web and External Data, learners apply their knowledge to query data from multiple s ources, extracting relevant information for stakeholders using techniques from statistical software. On the last learning unit, Working with Macros, where participants learn to automate repetitive tasks and data management through macro recording. 

This structured approach ensures that by the end of the course, participants are adept at utilising advanced statistical formulas and functions, summarising and analysing data, creating PivotTables, working with external data sources, and automating tasks with macros, equipping them with the comprehensive skills needed for effective data management and analysis.

2 Days

16 hours, 15 hours training and 1 hour assessment

Advanced Formulas and Functions

• Formulas with Multiple Operators 

• Inserting and Editing a Function

• AutoCalculate and Manual Calculation

• Cell and Range Names

• Displaying and Tracing Formulas

• Understanding Formula Errors

• Using Financial Functions (PMT)

• Using Logical Functions (IF, AND, OR, Nested IFs)

• Using Lookup Functions (VLOOKUP, HLOOKUP, MATCH & etc)

• Using Text Functions (CONCATENATE, UPPER, LEFT & etc)

• Using Date & Time Functions

Managing and Analysing Data Ranges

• Working with Scenarios & Creating a Scenario Summary Report

• Working with Data Tables

• Using Goal Seek & Solver

• Using Text to Columns

• Grouping and Outlining Data

• Using Subtotal

• Consolidating Data by Position or Category

• Consolidating Data Using Formulas

• Sharing a Workbook

• Track changes

• Compare and Merge Workbooks

Organising and Summarising Data

• Creating a Custom AutoFill List

• Applying Conditional Formatting

• Create & Manage Conditional Formatting Rules

• Sorting by a Custom List

• Filtering Data

• Creating a Custom AutoFilter

• Using Advanced Filter

• Using Data Validation

Working with PivotTables

• Create and Specifying PivotTable Data

• Changing a PivotTable’s Calculation

• Using Multiple PivotTable Fields

• Filtering & Sorting a PivotTable

• Working with PivotTable Layout

• Grouping PivotTable Items

• Update & Format a PivotTable

• Create a PivotChart

• Using Slicers & Sharing Slicers Between PivotTables

Working with Web and External Data

• Inserting a Hyperlink

• Importing Data from a Text File and an Access Database

• Importing Data from the Web Working with Existing Data Connections

Working with Macros

•Recording a Macro

• Playing and Deleting a Macro

• Adding a Macro to the Quick Access Toolbar