00 353 1 2790020sales@olas.ie Olas: Professional, Direct, Clever & Helpful

    Microsoft Excel Advanced

     

    Description

    The objective of this Excel training course is to create complex models involving multiple linked Excel spreadsheets and files, analysing data using what ifs and scenarios, validating and auditing data and automating commonly used features with macros.

    Price €300 per person

    Dates

    • Book a place on this course and pay with your credit card.29 January 2018 , 1 Day
    • Book a place on this course and pay with your credit card.21 February 2018 , 1 Day
    • Book a place on this course and pay with your credit card.29 March 2018 , 1 Day
    • Book a place on this course and pay with your credit card.18 April 2018 , 1 Day
    • Book a place on this course and pay with your credit card.23 May 2018 , 1 Day
    • Book a place on this course and pay with your credit card.22 June 2018 , 1 Day
    • Book a place on this course and pay with your credit card.18 July 2018 , 1 Day
    • Book a place on this course and pay with your credit card.30 August 2018 , 1 Day
    • Book a place on this course and pay with your credit card.12 September 2018 , 1 Day
    • Book a place on this course and pay with your credit card.22 October 2018 , 1 Day
    • Book a place on this course and pay with your credit card.29 November 2018 , 1 Day
    • Book a place on this course and pay with your credit card.18 December 2018 , 1 Day

    TARGET AUDIENCE

    This Microsoft Excel advanced course is aimed at experienced Excel users who need to create and manipulate more complex models using the advanced features of Excel.

    PREREQUISITES

    Participants should be existing users of MS Excel.  They should be able to create, edit and manipulate spreadsheets containing formulae and functions.  They should be able to create a basic chart and pivot table.  Ideally they will have completed the Excel Introduction and / or Excel Intermediate course(s).

    UPON COMPLETION, PARTICIPANTS WILL BE ABLE TO

    • Create complex formulae
    • Use a wide range of Excel functions – Mathematical / Statistical / Financial / Text / Lookup / Date & Time
    • Link worksheets and workbooks via formulae
    • Use Data Validation and Audit a workbook
    • Analyse data using Scenarios / What Ifs / Goalseek
    • Share workbooks
    • Use advanced sorting and filtering features
    • Create advanced Pivot Tables
    • Create and use a keystroke macro
    Course Outline
    Click here to download course oultline

    What’s new in Excel 2016
    New Team Tab
    Tell me what you want to do…
    One Click Forecasting
    Quick Analysis

    Review of Intermediate level
    If Statements/Nested If

    Advanced Functions
    Or / Not
    Combining If, And, Or, Not
    SUMIF
    Vertical Lookup (VLOOKUP)
    Horizontal Lookup (HLOOKUP)

    Lookup and Information Functions
    Match function
    Index Function
    ISTEXT, ISVALUE, ISDATE,ISNULL,ISERR, ISNA
    IFERROR
    Offset
    Advanced List Management
    Advanced Filter
    Database Functions: DSUM, DMIN, DMAX, DAVERAGE, DCOUNT

    Summarising Data with Pivot Tables
    Inserting calculated fields
    Manipulating Fields
    Changing Value Filed Settings
    Using Report Filter
    Grouping Data containing Dates and Numbers
    Formatting Pivot Table
    Showing and Hiding the Grand Totals
    Refreshing Data In Pivot Table
    Changing The Scope Of The Data source
    Summarizing Values by Sum, Count, Average, Max, and Product
    Show Values As % of Grand Total, % of Column Total, % of Row Total
    Pivot Table Options

    Using Slicers for Effective Filtering
    Pivot Charts

    What if Analysis Tools
    Scenarios
    Custom Views
    Goal Seek
    Solver
    Data Tables

    Protecting and Sharing
    Sharing a file
    Tracking changes
    Accepting or rejecting changes
    Applying Data validation rules
    Inserting comments
    Protecting cells, sheets, files
    Password protecting a file
    Password protecting a cell range

    Introduction to Macros
    Displaying the Developer Tab
    Review And Purpose Of Macros
    Where To Save Macros
    Absolute and relative record
    Running macros: Assigning to Quick Access Toolbar, shapes, Pictures and keyboard shortcuts

    Templates
    Creating Custom Templates
    Opening And Editing Templates
    Setting Template Properties

    Themes
    Applying Current Themes
    Creating Custom Themes

    Course Summary and Review
    Recap of Topics Covered/ Questions
    Question Time

Contact Us

Drop us a line, include your contact details and we will get back to you as soon as possible.