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

Microsoft Excel Intermediate

Description

The objective of our Microsoft Excel Intermediate training course is to develop a good working knowledge of Excel to include features such as functions and formulae, sorting and filtering data, working with charts and pivot tables.

Price: €350 per person

Dates

  • Book a place on this course and pay with your credit card.16 September 2019, 1 Day
  • Book a place on this course and pay with your credit card.17 October 2019 , 1 Day
  • Book a place on this course and pay with your credit card.13 November 2019 , 1 Day
  • Book a place on this course and pay with your credit card.11 December 2019 , 1 Day


TARGET AUDIENCE

The Microsoft Excel Intermediate course is aimed at users who have basic experience with Excel and want to build on their current knowledge to set up their own spreadsheets and manipulate existing ones.

PREREQUISITES

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

GOALS AND OBJECTIVES

Having completed this course, participants will be able to:

  • Create complex formulae incorporating relative and absolute cell addressing
  • Use a wide range of Excel functions such as Sumif, Countif, IF, Or, 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 Outline
Click here to download course oultline

Review Concepts
Absolute, Relative References
Standard calculations

New Features
New copy / paste functionality
New Functions
New Charts
Advanced Pivot Table Functionality

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
MAXIF / MAXIFS
MINIF / MINIFS
COUNTIF / COUNTIFS
ISERROR, IFERROR, IFNA
Nesting IF WITH AND, OR
SWITCH / 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
Setting Chart as Default
Area, legends, etc.
Using Sparklines (Line, Column, Win / Loss)
Creating a secondary axis
New Charts in Excel 2016

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
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.