MS Excel Advanced Course
* Live Training is for groups of 6 or more - Prices Ex. Vat
Course Overview
Picking up from where the Intermediate Course left off, the objective of this course is to expose you to the many powerful advanced functions that Excel has to offer.
Topics covered include: Data Modelling, Advanced Formula, Pivot Table Dashboards, Data Validation and Automating processes… not forgetting all those useful tips and tricks to help maximize efficiencies and impress your colleagues!
Course Content
Looking at all the key aspects to ensure Data Integrity. Using some neat Excel tools to check, troubleshoot, verify and validate data within your spreadsheet.
Points covered include: Verifying numerical content, Formula auditing, Empty cells vs Zero values, Excels Golden Rule, Running Totals, Audit Trails and Data Ranges.
Highlighting the importance of one’s current List Separator, Decimal symbol, Thousand Separator and Date formats when working with CSV files.
Explaining the difference between the New and Legacy Import Wizards within Excel and then using the Legacy Wizard to import a CSV file; highlighting the importance of the resultant Data Connection and how to use it to one’s advantage.
Taking formulas to the next level…
- Text Formulas
Using Text Formulas to dynamically manipulate and extract data while introducing the concept of nested formulas. CONCAT, PROPER, UPPER, LEFT, RIGHT, MID & TRIM.
- Date Formulas
Understanding that Dates are – or should be – stored as a numerical value. This opens up a whole new world when working with Dates. Date Functions such as WEEKNUM, EOMONTH, TODAY and NETWORKDAYS help us to take this to the next level.
- Logical Formulas
Introduction to the AND & OR functions in conjunction with IF to cater for multiple conditions, as well as the use of IFERROR to hide known errors.
- Lookup Formulas
Explore the many different variations of the VLOOKUP function and their advantages and disadvantages. Plus, the introduction to INDEX-MATCH and the all new XLOOKUP as better alternatives to VLOOKUP.
- Maths Formulas
Taking your basic SUM and COUNT functions to the next level with SUMIF(S) and COUNTIF(S). Building 2-dimentional formula arrays with these functions to create Data Summary Tables. Comparing these to Pivot Tables and exploring the advantages and disadvantages of both.
Best methods to prevent incorrect data entry, including lookup lists and in cell dropdowns.
Includes a brief recap on Pivot Table basics and the Key Features and Layouts to be aware of.
Best Practices of creating multiple Pivot Tables and Charts off the same data and how to combine these into a multi-pivot Dashboard by linking Slicers and Timelines; plus a really neat trick on how best to add additional records to your source data.
Introduction to the timesaving world of Recording a Macro for repeatable tasks. A clear explanation of the different options available to save your Macro and some really clever Tips and Tricks to ensure that your Macro works on different datasets.
Over 50 Keyboard Shortcuts covered throughout the course to increase those efficiencies.
A printable 1-pager, of all these shortcuts and more, is provided with the follow-along Workbook.
What's Included?
- A detailed follow-along Excel Workbook with Summary Notes
- Self-marking quick questions to test your progress as you learn
- Practical Tasks that will enable you to put your newly acquired Excel Skills into practice
- Course completion Certificates
- Lifetime access to course material and updates
Next Step ...
For further specific information for our various training options use the links below; or alternatively contact us on hello@summitsolutionstraining.co.za or 086 167 3923.
Excel Advanced Course Reviews
Long, understandable, insightful, wow!
Tinus de Wet – Plasson SA
Although it takes a bit of time, it is very thorough and clear. I like the fact that I can return whenever I want to go and check up on anything.
Easy to keep up and worthwhile
I really enjoyed the course. Although I did the Intermediate course, I liked the fact that Mark would repeat some steps done in the Intermediate course as a reminder so I can keep up because we have covered so much it is easy to forget some things.
Logical, thorough and informative
Kathy Ballance – UCL Company (Pty) ltd
The course was so informative, and the videos so clear and precise, I am excited to continue with the automation course!
Thank you so much, my life is changed:-)
Neat tricks
Aileen Anderson – GVB Con
Useful tips and tricks for improving excel usage.
Life changing experience
Ricardo Januarie – Grootvadersbosch Conservancy
Wonderful to have gone through this journey. It was an eye-openener for me. Thank you very much for the professional manner in which this course was presented.
Excellent
masego Beng – King Price Insurance
Avery informative Excel course for one to recommend
fantastic
Malefetsane Tladi – King Price Life
It was Great. I wish we could be able to save the videos
It was Great!
Lehlogonolo Martha Kekana – King Price
I loved every lesson!
I wish we can be able to save the videos so that we can always refresh from them.
