# 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

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