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.
Full Day Course Price: €375 per person
Click here to view the upcoming Virtual Live Training 1.5 hour session schedule.
To book onto a Virtual Live Training 1.5 hour session, or series of sessions, please click here.
Dates
- 20 June 2023, 1 Day
- 29 August 2023, 1 Day
- 28 September 2023, 1 Day
- 14 November 2023, 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
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