Microsoft Excel Advanced Training Course (1 day)

Scheduled courses in Peterborough or £595, plus VAT, on-site.

This course is suitable only for those with considerable prior experience and covers: advanced lookup, maths and array formulas and functions; working with macros; data analysis tools; techniques for summarizing data; and performing what-if data analysis.

Lookup and Reference Functions

Nesting VLOOKUP functions , VLOOKUP multiple criteria, Using MATCH & INDEX , Advanced MATCH & INDEX , Optimizing Lookup Operations Part , The OFFSET Function , The INDIRECT function

Advanced Number-Crunching Functions

Controlling rounding sensitivity, Using SUMIF and INDIRECT , Using wildcards with SUMIFS, AVERAGEIFS, MINIFS and MAXIFS , Using the SUBTOTAL function , The RANK function variations , The LARGE and SMALL functions

Array Formulas

Array formula basics , Using conditionals in array formulas , Using concatenation in array formulas , Array formulas and data tables , The TRANSPOSE function

Auditing Worksheet Functions

Understanding Excel error values, #DIV/0!, #NAME?, #REF, #NULL!, #N/A, Using conditionals to anticipate error values, Using IFERROR(), Using the formula error checker, Auditing a worksheet, Tracing cell precedents and dependents, Evaluating formulas, Watching cell values

Working With Macros

Creating A Macro, Running A Macro, Editing A Macro, Saving A Workbook With Macros, Opening A Workbook With Macros, Adding A Macro To The Quick Access Toolbar

Pivot Tables, Slicers and Timelines

Setting Pivottable Options, Filtering Pivottable Data With Slicers, Filtering Pivottable Inline, Creating Custom Filters, Filtering Pivottable Data Using Timeline, Creating A Pivotchart, Creating dashboards

Summarizing Data

Adding Subtotals To A List, Nesting Subtotals, Applying Advanced Filters, Adding Group And Outline Criteria To Ranges, Using Data Validation, Converting Text To Columns, Previewing Data Using Quick Analysis

What-If Data Analysis

Using Goal Seek, Using Solver, Creating & Displaying Scenarios, Using Data Tables