Excel Advanced

Module 5: Advanced Functions

Topics:

• Creating and Applying Names In A Worksheet
• VLOOKUP Function
• XLOOKUP Function
• ROUND function
• Using the Formula Auditing Tools
• Using the IF Function and Nesting Functions
• Using the IFERROR Function
• Summary Based Functions: SUMIF(S), COUNTIF(S), AVERAGEIF(S)
• Text Functions: LEFT, RIGHT & MID
• CONCAT function and Text-To-Columns Feature

Module 6: Using the "What If" Analysis Toolpack & Implementing Macros

Topics:

• Using the Data Consolidation Tool 
• Using the Goal Seek Tool
• Using the Solver Tool
• Using the Scenario Manager
• Recording and Running Macros
• Understanding the Macro/VBA Developer Interface
• Editing Macros with the Visual Basic Editor
• Adding Buttons to Run Macros From the Ribbon

What We'll Explore:

1. Use Database Functions: XLOOKUP(), SUMIF(), and AVERAGEIF()
2. Fix errors using auditing, or handle inevitable errors with backup values
3. Apply data cleanup and isolation/consolidation techniques.
4. Find optimal values for a single cell or a set of inter-related cells. 
5. Create and run basic macros to automate repetitive tasks.
6. Add macros to the Excel ribbon