MS Excel Advanced

Start Dates: TBC

Duration: 1 Day

https://www.ifsskillnet.ie Book Now

Programme overview

This one-day course is designed to give users an understanding of and practical experience of some of the more advanced features and functions within MS Excel including: Lookup tables, Data validation, Pivot Tables, dashboards and macros.

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

Please contact info@ifsskillnet.ie for more information on course dates and course fees.

Learning outcomes

On completion of this course, delegates will be able to use a range of advanced functions including:

  • 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

Who is the course 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.

Course 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

  • Setting-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 External Table Source

Trainer Profile

Please contact info@ifsskillnet.ie for further information on training providers.