Microsoft Excel  - Level 4Download Course Outline

This is the outline of our Microsoft Excel Level 4 course - Duration: 1 day

This course is for advanced Excel users who want to update their skills in advanced data analysis. By its focus on working with nested functions, advanced Pivot Tables, complex use of Data Validation and Conditional Formatting, this course will give the skills to tackle the most complex 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. This course also prepares you for moving on to the Microsoft Excel VBA Course.

To get the most from this course 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.

We specialise on on-site training and can come to you to run our course either on your PC's or provide our own high specification equipment. If you want specific training for your staff, we're happy to customise this course to your requirements, adding or removing topics as necessary.

Microsoft Excel  - Level 4 course content

Download a PDF course outline using the link on the right.

Review Naming Cells and Ranges

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

Advanced Use of Names

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

Advanced Function Workshop

  • Nest functions by accurately puting one function inside another so that you can “customise” functions
  • Workshop session using a selection of functions eg:-
  • And, Or, Match, Index, Date Functions, Text Functions
  • Using IfError to remove Excel errors.

Working with Arrays

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

Concatenation

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

Formula Auditing

  • Trace Precedents and Dependents.
  • Use the Watch Window.

Working with Advanced Pivot Tables

  • Use the “Show Data As” Option
  • Consolidate Data using a PivotTable
  • Create Calculated Items
  • Create Calculated Fields
  • Create a PivotChart report

Introduction to PowerPivot

  • What is PowerPivot
  • Importing data
  • Creating PivotTables using PowerPivot
  • Creating measures

Advanced Use of Data Validation

  • Prevent invalid data being entered into a cell.
  • Create cascading list validations using the Indirect Function.
  • Extend list validations by using the Offset function.

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

What-If Analysis

  • Use Goal Seek to find the right input into a cell when you know the result you want in a formula.
  • Use Data Tables to see the results of many different inputs into cells at the same time.
  • Use Scenarios to save inputs and then switch between them.
  • Use Solver to find the optimal value of a target cell by changing values in cells used to calculate the target cel.

We also offer this course as Live Online Learning - follow this link to learn more.