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