MS EXCEL 2016 – ADVANCED
TRAINER: Mark Tyrer – Summit Solutions
Date 29 August 2018
Time 08:30 – 16:30
Venue Chamber House, Royal Showgrounds
COST (includes vat and lunch)
Members R1271-05 p/p, R1240-79 p/p for 3/more, R1180-27 p/p for 5/more
Non-members R1371-93 p/p, R1341-67 p/p for 3/more, R1281-15 p/p for 5/more
The objective of this course is to expose the User to the many powerful advanced functions that Excel has to offer, as well as cover some key principles that ensure good practice while avoiding common pitfalls when working with data.
Some Key principles that are often overlooked including:
• The difference between Text, Numbers and Dates.
• Understanding Windows Regional settings and its effect on Excel
• Basic principles and common pitfalls in setting up a range of Data.
Formula – Tips and Tricks on working with/editing formula
• Text – CONCATENATE, PROPER, UPPER, LEFT, MID
• Logical – IF, IFERROR, AND, OR
• Maths – SUM, SUMIF, COUNTIF
• Lookup – VLOOKUP, INDEX – MATCH widely accepted as the best Lookup formula
Spreadsheet Validation – Adding formula to validate and cross check your spreadsheet.
Goal Seek – Finding input values for a desired formula result.
Cell references – Absolute and Relative cell referencing.
Defined Names – Naming cells and ranges to simplify cell references formula
Data Validation – including lookup lists and in cell dropdowns.
Protecting cells and worksheets – best methods to prevent incorrect data entry
Differences bt. Ranges and Tables – and the pros and cons of both
Pivot Tables and Pivot Charts – including slicers and creating dashboards
Macros – Including recording a Macro and manipulating downloaded code to suite specific requirements.
The course is Solution Based as opposed to skills based – by workshopping a number of real life scenarios that everyone should relate to, the relevant skills are acquired, which in turn can be applied to other workbooks.
DO YOU HAVE YOUR OWN LAPTOP TO BRING TO THE TRAINING WITH MS EXCEL VERSION 2013 OR HIGHER INSTALLED? YES NO
Your take-home course material includes:
Printed worksheet of all the Tasks used in the workshop. A tear-off sheet that recaps all the Key Concepts covered including Keyboard shortcuts and Formula. A USB flash stick preloaded with all Excel Worksheets used, plus a comprehensive Video File Library that explains in detail all the tasks workshopped during the course.
excel (verb) – be exceptionally good at or proficient in an activity or subject. May this be the course that enables you to Excel.
Attendees will receive a certificate of attendance.