Microsoft Excel - Level 4

With its focus on working with nested functions, advanced Pivot Tables, advanced Analysis Tables and Conditional Formatting, this course will give the skills to tackle the most complex data analysis.  

You will have attended the Burningsuit Microsoft Excel Level 3 Course. Or you have been using Excel for a reasonable length of time and you are competent in spreadsheet analysis work and have used formulae and functions.  You now need to know how to use complex functions and perform difficult data analysis.

By completing structured exercises, using data relevant to your work, this course will increase your competence in advanced data analysis helping you meet those targets more efficiently. 

Review Naming Cells and Ranges

• Correctly create named cells and use names in formulas accurately.

Advanced Use of Names

• Use Names to simplify formulas.
• Use Scope to duplicate names.
• Use names as “constants” and in complex formula work.

Advanced features of Excel Analysis Tables

• Using Excel Tables to analyse data
• Using implicit names automatically generated by Tables.

Nesting Functions

• Nest functions by accurately putting one function inside another so that you can “customise” functions

Concatenation

• In a single cell, accurately string together cell values, text and formulas into a single value.

Advanced Function Workshop

• Workshop session using a selection of challenging functions eg:-
• And, Or
• Match, Index
• Indirect
• Text Functions
• Date Functions
• IfError

Working with Advanced Pivot Tables

• Creating Dashboards using Slicers
• Consolidate Data using a PivotTable
• Create Calculated Items
• Create Calculated Fields
• Create a PivotChart report
• Creating “Flattened” Pivots

Advanced Use of Conditional Formatting

• Use conditional formatting to compare data ranges
• Use functions within conditional formatting
• Format entire rows of data rather than single cells

Working with Arrays

• Use Array Formulas to analyse data stored in a range of adjacent cells.

What-If Analysis

• Looking at the use of Goal Seeking
• Looking at the use of Data Tables

Overview of Power Query

• How you can clean and transform data prior to analysing it.