Olas Software Training and Development | Microsoft Excel Intermediate
00 353 1 2790020sales@olas.ie Olas: Professional, Direct, Clever & Helpful

Microsoft Excel Intermediate

 

Description

The objective of this Excel training course is to create complex models involving multiple linked sheets and files; to analyse data using what-ifs and scenarios; validate and audit data; generate reports using Pivot Tables.

Price €280 per person

Dates

  • Book a place on this course and pay with your credit card.3 March 2017 , 1 day
  • Book a place on this course and pay with your credit card.22 March 2017 , 1 day
  • Book a place on this course and pay with your credit card.10 April 2017 , 1 day
  • Book a place on this course and pay with your credit card.18 May 2017 , 1 day
  • Book a place on this course and pay with your credit card.21 June 2017 , 1 day
  • Book a place on this course and pay with your credit card.12 July 2017 , 1 day
  • Book a place on this course and pay with your credit card.22 August 2017 , 1 day
  • Book a place on this course and pay with your credit card.13 September 2017 , 1 day
  • Book a place on this course and pay with your credit card.11 October 2017 , 1 day
  • Book a place on this course and pay with your credit card.21 November 2017 , 1 day
  • Book a place on this course and pay with your credit card.13 December 2017 , 1 day

TARGET AUDIENCE

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

PREREQUISITES

Participants should be existing users of MS Excel who can create, edit, format and print a spreadsheet. They should be able to create basic formulae and ideally will have completed the MS Excel Introduction programme.

UPON COMPLETION, PARTICIPANTS WILL BE ABLE TO

  • Create complex formulae incorporating relative & absolute cell addressing
  • Use a wide range of Excel functions such as Sumif, Countif, IF, IF And, Lookups, ISError and many more
  • Build and apply Conditional Formatting
  • Link Sheets and Files
  • Manipulate Data – Sorting, Filtering, Validation
  • Create Pivot Tables
  • Create a wide range of Charts
Course Content

Course Outline

Click here to download course oultline

Review Concepts
Absolute, Relative References
Standard calculations

New Features
One click Forecasting
Smart Lookup
Tell me what you want to do…
Quick Analysis Tools

Naming Cell Ranges
Concept And Purpose
Naming Individual Cells
Naming Range Of Cells
Deleting And Amending Named Ranges
Using Name Ranges in Formulae/Functions
Named Ranges As Navigation Aid

Linking Sheets and Files
Changing the default number of workbook sheets
3D Calculations
Linking sheets in the same file
Linking different Excel files
Using Edit, Links
Viewing Different Files on One Screen
Window Split / Freeze Panes
Viewing Different Parts of the Sheet On One Screen
Custom Views

Logical Functions
IF Statements
Nested If / IFS
SUMIF / SUMIFS
AVERAGEIF / AVERAGEIFS
MAXIFS / MINIFS
COUNTIF / COUNTIFS
ISERROR, IFERROR, IFNA
Nesting IF WITH AND, OR
SWITCH / CONCAT/ TEXTJOIN

Applying and Managing Conditional Formatting
Data Bars
Colour Scales
Icon Sets
Top/Bottom
Creating Formula based Conditional formatting

Lookup Functions
Vertical Lookup (VLOOKUP)
Horizontal Lookup (HLOOKUP)

Database List Management
Sorting Data (By Values, By Cell Colour, By Font Colour, By Cell Icons)
Multi Column Sort
Filter (By Values, By Cell Colour, By Font Colour, By Cell Icons)
Multi Column Filter
Advanced Filter
Adding Subtotals
Freeze Panes
Group and Outline
Data Form
Format as Table Feature
Data Validation

Formulae Auditing Formula View
Tracing Precedents
Tracing Dependents
Using Watch Window
Go To Special…

Charts/Graphs Advanced Techniques
Using Recommended charts
Creating Chart Using Shortcut Keys
Saving Custom Chart as Templates
Setting Chart as Default
Applying Trend lines
Formatting and Editing Series, Plot Area, Data Points Chart Area, legends, etc…
Using Sparklines (Line, Column, Win/Loss)
Creating a secondary axis
New Chart types

Pivot Tables
Creating a Pivot Table
Updating the Table
Changing the Table Structure
Formatting the Pivot Table
AutoFormats
Creating Charts from the Data
Calculated Items
Using Get Pivot data

Protection
Protection – Cells / Sheets / Files
Using IRM

Course Summary and Review

Contact Us

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