Mastering Microsoft excel is imperative for the performance of individuals involved in working with statistical and financial data. Excel enables users to perform daily duties in a simplified manner and with more efficiency and effectiveness. Thus, participants of this course will master Microsoft Excel program and apply it in daily operational activities.

Course objective

The objective of the course is to master the use and application of Microsoft excel in operational activities to enhance performance and efficiency.

Learning outcome

At completion of this course you (or your staff) will have mastered the most popular Excel tools and come out with confidence to complete any Excel tasks with efficiency and grace.

Target Staff
This course is for individuals whose job responsibilities include creating, inputting, budgeting, preparing financial reports, preparing timeline, loan management, forecasting and printing basic spreadsheets.

Course contents

Module 1: Working with excel conditional functions

• Working with Excel Name Ranges
• Advantages and Disadvantages of Excel Name Ranges
• Editing an Excel Name Range
• Using Excel’s IF() Function
• Excel’s IF() Function with a Name Range
• Nesting Functions with Excel
• Nesting Excels AND() Function within the IF() Function
• Using Excel’s COUNTIF() Function
• Using Excel’s SUMIF() Function
• Using Excel’s IFERROR() Function

Module 2: Working with excel’s look up functions

• Microsoft Excel VLOOKUP() Function
• Microsoft Excel HLOOKUP() Function
• Microsoft Excel INDEX() Function
• Microsoft Excel MATCH() Function
• Microsoft Excel INDEX() and MATCH() Function Combined
• Creating a Dynamic HLOOKUP() with the MATCH() Function

Module 3: Working with excels text based functions

• Using Excel’s LEFT(), RIGHT() and MID() Functions
• Using Excel’s LEN() Function
• Using Excel’s SEARCH() Function
• Using Excel’s CONCATENATE() Function

Module 4: Auditing an excel worksheet

• Tracing Precedents in Excel Formulas
• Tracing Dependents in Excel Formulas
• Working with the Watch Window
• Showing Formulas

Module 5: Protecting excel worksheets and workbook

• Protecting Specific Cells in a Worksheet
• Protecting the Structure of a Workbook
• Adding a Workbook Password

Module 6: Mastering excel’s “what if” tools

• Working with Excel’s Goal Seek Tool
• Working with Excel’s Solver Tool
• Building Effective Data Tables in Excel
• Creating Scenarios in Excel

Module 7: Automating Repetitive Tasks in Excel with Macros

• Understanding Excel Macros
• Activating the Developer Tab in Excel
• Creating a Macro with the Macro Recorder
• Editing a Macro with VBA
• Creating Buttons to Run Macros