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

Microsoft Excel Advanced

Description

Our Microsoft Excel Advanced training course is designed to enable participants to create complex models involving multiple linked spreadsheets and files, analysing data using what ifs and scenarios, validating and auditing data and automating common processes.

Price: €375 per person

Dates

  • Book a place on this course and pay with your credit card.19 November 2019 , 1 Day
  • Book a place on this course and pay with your credit card.05 December 2019 , 1 Day
  • Book a place on this course and pay with your credit card.23 January 2020 , 1 Day
  • Book a place on this course and pay with your credit card.19 February 2020 , 1 Day
  • Book a place on this course and pay with your credit card.19 March 2020 , 1 Day
  • Book a place on this course and pay with your credit card.30 April 2020 , 1 Day
  • Book a place on this course and pay with your credit card.21 May 2020 , 1 Day
  • Book a place on this course and pay with your credit card.25 June 2020 , 1 Day
  • Book a place on this course and pay with your credit card.22 July 2020 , 1 Day
  • Book a place on this course and pay with your credit card.26 August 2020 , 1 Day
  • Book a place on this course and pay with your credit card.23 September 2020 , 1 Day
  • Book a place on this course and pay with your credit card.22 October 2020 , 1 Day
  • Book a place on this course and pay with your credit card.19 November 2020 , 1 Day
  • Book a place on this course and pay with your credit card.17 December 2020 , 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 Microsoft Excel. They should be able to create, edit and manipulate spreadsheets containing formulae and functions. They should also be able to create basic charts and pivot tables. Ideally they should have completed the Excel Introduction and / or Excel Intermediate course.

Goals and Objectives

Having completed this course, 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 2019
Improved copy/paste
New functions
Additional Pivot table functionality

Review of Intermediate level
If Statement / Nested If / IFS
Absolute cell referencing
Named ranges
SUMIF / SUMIFS / MAXIFS / MINIFS

Lookup and Information Functions
Vertical Lookup (VLOOKUP)
Horizontal Lookup (HLOOKUP)
INDEX, MATCH & OFFSET
ISTEXT, ISVALUE, ISNULL, ISERROR
ISNA, ISDATE, IFERROR

Summarising Data with Pivot Tables
Inserting calculated fields
Changing value field settings
Using report filter
Changing the scope of the data source
Pivot table options
Using slicers for effective filtering
Using timelines
Pivot charts

What If Analysis Tools
Scenarios
Custom views
Goal seek
Solver
Scenario manager
Data tables

Advanced Filtering and Sorting
Text filters
Date filters
Numeric filters
Advanced subtotals

Formulae Auditing Formula View
Tracing precedents
Tracing dependents
Using watch window
Go To Special

Protecting and Sharing
Sharing a file
Tracking changes
Applying data validation rules
Protecting cells, sheets, files
Password protecting a file

Working with Tables
Advantages of excel tables
Techniques
Structured references

Advanced Charts
Saving custom charts as templates
Applying trend lines
Formatting and editing series, plot area, data points
New Charts in Excel 2019

Introduction to Macros
Displaying the Developer Tab
Overview and Purpose of Macros
Where to save macros
Absolute and relative recording
Running macros
Assigning to the Quick access toolbar
Assigning to shapes or pictures
Keyboard shortcuts for macros

Course Summary and Review
Recap of topics covered
Question time

Contact Us

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