• This course has strict prerequisites - delegates should have already attended the relevant Levels 1, 2 and 3 courses or have equivalent knowledge. This course is NOT a fast track course for learning the exam material but a course for turning knowledge in to exam readiness.

Sharing and Maintaining Workbooks

Apply workbook settings, properties, and data options

• Setting advanced properties, saving a workbook as a template, and importing and exporting XML data

Apply protection and sharing properties to workbooks and worksheets

• Protecting the current sheet, protecting the workbook structure, restricting permissions, and requiring a password to open a workbook

Maintain shared workbooks

• Merging workbooks and setting Track Changes options

Applying Formulas and Functions

Audit formulas

• Tracing formula precedents, dependents, and errors, locating invalid data or formulas, and correcting errors in formulas

Manipulate formula options

• Setting iterative calculation options and enabling or disabling automatic workbook calculation

Perform data summary tasks

• Using an array formula and using a SUMIFS function

Apply functions in formulas

• Finding and correcting errors in functions, applying arrays to functions, and using Statistical, Date and Time, Financial, Text, and Cube functions

Presenting Data Visually

Apply advanced chart features

• Using Trend lines, Dual axes, chart templates, and Sparklines

Apply data analysis

• Using automated analysis tools and performing What-If analysis

Apply and manipulate PivotTables

• Manipulating PivotTable data and using the slicer to filter and segment your PivotTable data in multiple layers

Apply and manipulate PivotCharts

• Creating, manipulating, and analysing PivotChart data

Demonstrate how to use the slicer

• Choosing data sets from external data connections

Working with Macros and Forms

Create and manipulate macros

• Running a macro, running a macro when a workbook is opened, running a macro when a button is clicked, recording an action macro, assigning a macro to a command button, creating a custom macro button on the Quick Access Toolbar, and applying modifications to a macro

Insert and manipulate form controls

• Inserting form controls and setting form properties