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.28 August 2017 , 1 Day
  • Book a place on this course and pay with your credit card.21 September 2017 , 1 Day
  • Book a place on this course and pay with your credit card.19 October 2017 , 1 Day
  • Book a place on this course and pay with your credit card.30 November 2017 , 1 Day
  • Book a place on this course and pay with your credit card.18 December 2017 , 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.