To make a booking, call
01-6390050

MS Advanced Excel

Please call us for details - 01-6390050
Available as in-house training
1 day
Purpose: 

In summary the Trigraph MS Excel training course will improve your understanding of how the MS Excel features and Functions can be used to improve your productivity when using MS Excel specifically for the manipulation and extrapolation of data.

This one day course is designed to give users an understanding of some of the more advanced features and functions within MS Excel; while also exploring the advantages of using a powerful electronic spreadsheet.

The course is entirely 'hands on', so those attending will get practice in using advanced features of MS Excel to manage, automate and customise workbooks.

Suitable for: 

This course is suitable for those people who want to expand and improve their knowledge of MS Excel’s more advanced functions, features and Add-Ins.

The course is aimed at people who have been using Excel for some time and are confident of using the MS Excel on a day to day basis.

What’s good about the course? : 

Learn by doing – in additional to the formal presentations from Trigraph, attendees will benefit by using the MS Excel software itself on their own dedicated PC / Laptop. They will learn using either generic data files or their own in-house data files.

All of our trainers are mature business management professionals who are educated to 3rd / 4th level and they have Accredited Microsoft Excel Expert Certification. They are also experienced course presenters.

The training materials are fully owned by Trigraph which means we have full flexibility to tailor the content to suit particular client requirements.

On completion of this course, delegates will be able to:

  • Use Range Naming to understand and apply Lookup Functionality
  • Use complex Functions including Nesting
  • Apply Data Validation to existing and data to be Inputted
  • Conditional Formatting including RAG Status identification
  • Cell / Sheet / Workbook Protection
  • Pivot Table Reports & Charting
  • Dashboards for Management Reporting
  • Macros and VBA for improved efficiency
  • PowerPivot data Analysis

 

Objectives:

On completion of this course, delegates will be able to:

  • Use Range Naming to understand and apply Lookup Functionality
  • Use complex Functions including Nesting
  • Apply Data Validation to existing and data to be Inputted
  • Conditional Formatting including RAG Status identification
  • Cell / Sheet / Workbook Protection
  • Pivot Table Reports & Charting
  • Dashboards for Management Reporting
  • Macros and VBA for improved efficiency
  • PowerPivot data Analysis

 

Benefits:

There are benefits to using MS Excel more efficiently:

  • Become more productive when using MS Excel
  • Do more work in less time with the assistance of MS Excel
  • Extrapolate relevant information more efficiently including from Multiple Tables
  • Produce accurate Management Reports faster
  • Highlight data inconsistencies more clearly
  • Highlight data patterns more clearly
  • Learn more efficient way to use MS Excel in your work environment

 

It is during the manipulation and extrapolation of data that the functionality of MS Excel will become apparent.

This is a practical hands-on course – “learn by doing” with access to MS Excel software for the course duration.

Detailed Content:

Working with Named Ranges

  • Creating Range Names
  • Range Names in Calculations

Lookup and Reference Functions

  • Understanding how and when to use the VLOOKUP Function

Complex Functions and Nesting

  • If, SumIfs and IfError Functions
  • AND / OR Functions
  • Subtotal and Rank Functions

What if analysis

  • Goal Seek
  • Data Tables

Data Validation

  • Seting-up Data Validation Rules
  • Create Drop Down Lists
  • How to Circle Invalid Data
  • Formula Auditing

Custom Formatting

  • Using Custom Formats
  • Using Conditional Formatting

Cell Protection

  • Protecting Formulas / Worksheet / Workbook

Pivot Table & Charts

  • Creating Pivot Tables
  • Editing Pivot Tables
  • Using Functions and Formulae within Pivot Tables
  • Printing Pivot Tables
  • Pivot Charts

Charts

  • Creating Charts
  • Changing Chart Elements
  • Changing Chart Data
  • Printing Charts
  • Inserting Pictures
  • Manipulating Objects

Using Dashboards for Management Reports

  • Analyse
  • Design
  • Create
  • Test

Introduction to Macros and Basic VBA

  • Recording a Basic Macro
  • Running a Macro
  • Editing a Macro

PowerPivot Add-In

  • Establish Relationships between several Tables
  • Create Pivot tables using more than one Excel Spreadsheet Table Source
  • Create Pivot tables using more than one ExternalTable Source