Course Length: 79 Lessons | 7.5 hours of Videos.
Total course duration: 9 – 11 hours
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!
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.
- 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