MS Excel Advanced

Overview

Course Dates

Iteration 1

  • Module 1: Tuesday 13th September – 11:30 am – 1:00 pm – Dates/Protecting Content/Data Validation
  • Module 2: Tuesday 20th September – 11:30 am – 1:00 pm – More on Functions (Part One)
  • Module 3: Tuesday 27th September – 11:30 am – 1:00 pm – More on Functions (Part Two)
  • Module 4: Tuesday 4th October – 11:30 am – 1:00 pm – Pivot Tables & Pivot Charts
  • Module 5: Tuesday 11th October – 11:30 am – 1:00 pm – Macros

Overview

These online webinars have been designed to replace classroom-based training whilst people are required to work from home.

Participants have the option of either attending the live webinars at the scheduled date and time or alternatively can view a recording of the webinar at a time that suits them.

Recordings will be available for 6 months after completion and a link to the recording will be sent to all participants following the live webinar along with all relevant course files.  A trainer will be on hand via email to answer any queries or questions that might arise following the training.

This second group of webinars follows on from ‘Bundle 1’ and covers the more advanced content.  It is recommended to view the ‘Bundle 1’ webinars in advance.

Each module is CPD Accredited and 1.5 CPD hours can be awarded to each attendee of the live session.

Schedule Outline

EXCEL 6: DATES / PROTECTING CONTENT / DATA VALIDATION

  • Working with Dates
    • Understanding how Excel sees dates
    • Calculating the difference between 2 dates
    • The TODAY Function
    • Calculating Age
    • EOMONTH/ISONUMBER
    • Using the Text Function with dates
    • Text to Columns to convert date format
  • Protecting worksheets, workbooks & restricting input.
    • Data Validation – Text, Numbers, Drop-Down List.
    • Locking cells and enforcing protection
    • Password protecting workbooks

EXCEL 7: MORE ON FUNCTIONS (PART ONE)

  • Grouping and Outlines
  • Working with the Subtotals
  • SUMIFS/MINIFS/MAXIFS/AVERAGEIFS/COUNTIFS
  • Writing and Understanding the IF Function
    • Writing a basic IF Statement
    • Using IF with AND/OR/IS Functions
    • Created Nested IF Functions.
  • Using the IFS Function

EXCEL 8: MORE ON FUNCTIONS (PART TWO)

  • Creating and working with named ranges.
  • VLOOKUP Function
  • IF Error function revisited
  • Index and Match
  • XLOOKUP – Excel 2019/365 only
    • Basic XLOOKUP
    • XLOOKUP – [if_not_found] argument
    • Search Mode argument

EXCEL 9: PIVOT TABLES & PIVOT CHARTS

  • Creating and understanding a basic Pivot Table
  • Filtering Data
  • Refreshing a Pivot Table/Refresh automatically on opening
  • Using the Pivot Table Design Ribbon
  • Resizing columns and preserving column widths
  • Grouping Dates/Grouping Fields
  • Using Slicers with Pivot Tables
  • Creating/Modifying Calculated Fields
  • Creating and Modifying Pivot Charts

EXCEL 10: MACROS

  • Recording simple macros
  • The VB Editor
  • A brief explanation of VBA code
  • Assigning macros to buttons
  • Building a macro to open a file and copy data
  • Using variables
  • Assigning macros to the Ribbon

Who Should Attend?

  • This course is designed to follow-on from MS Excel Level Two (Intermediate).  Having already familiarised yourself with the most efficient way of performing everyday tasks and now that you have mastered the more commonly used Intermediate topics, it is now time to build on that knowledge.  A large proportion of the course is dedicated to managing and manipulating lists of information from working with tables & slicers up to creating and working with Pivot Tables.
  • Useful functions such as IF and VLOOKUP are covered in addition to automating tasks using Macros etc.  It is highly recommended that you first attend the Excel Level Two (Intermediate) course before attending this advanced course if you are not already fully familiar with the Intermediate topics.
  • All participants will be given access recordings of the webinars by way of an email along with copies of files used and User Reference Material. These will be available to participants for 6 months following the course.
  • A Cork Chamber Skillnet / E-Bridge Certificate of Attendance will be awarded to each participant on completion of all courses. A sample copy is attached for your approval.

Entry Requirements

Ideally, those attending should have already attended the Excel Intermediate course or have an equivalent skill level.  If you have a very basic knowledge of Excel, it is recommended that you attend the Excel Intermediate course in advance.

This course can also be run as an in-company programme delivered on-site to meet your specific requirements. For more information on in-company delivery, or if you have any questions regarding this course, please Click Here.

Duration
5 X 1.5 Hours
Start & Finish Time
11:30 - 01:00
Location
Online
Members Fee
150
Non-Members Fee
175
blue_bar