Microsoft Excel

COURSE GOAL:
This course is aimed at exposing participants to the use of Advanced Excel formulas and features
in intensive data analyses.

OBJECTIVES OF THE COURSE:
1. To teach participants the advanced formulas as well as how to use which formula for
which occasion.
2. To equip participants with the knowledge on how to debug and audit the advanced
formulas.
3. To explore the magic of analyzing data using Advanced Excel

COURSE CONTENT:
The course is organized in modules and each module covers a reasonable content to enable
participants connect and progress successively in the next modules.
Module 1: Basic Formulas and functions of advanced Excel

• Basic Formulas ( Addition, Subtraction, Multiply, Division, average, average if, Max, Min,
• Percentage)
• Total Concept of Date & Time Formula
• Broad concept about cell reference
• Transpose Data in three way
• Sum For mula (Sum, Sum if, Sum Ifs)
• Data Filter
• Using Subtotal
• The Paste Special Function
• 3 D Sum
• Consolidating Data

Module 2: Data Validation

• Extended uses of Data Validation
• Working with validation formulae
• Other methods of tracking down invalid entries

Module 3: Preparing your data for analysis

• Mastering lookup functions (INDEX, MATCH)
• Creating helper columns using DATE and TEXT functions
• Applying NESTED-IF, AND, OR to organize data
• VLOOKUP-up Formula in different way with creating Invoice.

Module 4: Methods of Summarizing Data

• Using COUNTIFS
• Advanced uses of PIVOT-TABLE feature like Value Field Settings, Grouping Data and
• Slicers among others
• identify Major Customers, Top Products, Top/Bottom Sales reps…LARGE, SMALL,
• MAX, MIN
• Advanced Range Names and Formula in Names
• Calculations and reporting in Power Pivot – an introduction to Data Analysis Expressions
• (DAX)

Module 5: Report Visualization Techniques in Excel

• Dynamic charts (using CHOOSE & OFFSET functions) & Sparkline’s for trends
• Effectively using Conditional Formatting (formula-driven) for reporting
• Exchanging information with VB code
• Calculations in Power Pivot – an introduction to Data Analysis Expressions (DAX)
• Report presentation with using DASHBOARD.

Module 6: Decision Making with Excel

• Applications of Financial functions (Amortization table, FV, NPV, IRR, etc.);
• Sensitivity (“What-if”) analysis on models using Data Tables, Goal Seek, Scenarios;
• Using Excel to help you make decisions
• Create and manage alternative scenarios
• Make more profit or incur less expense by using Excel Solver to identify the best solution
• Use of External Data tools

Module 7: Macro and Finalizing worksheet

• Understanding basic Macro
• Using Advanced Macro
• Protecting worksheet and workbook  Sharing Worksheets to multiple user
• Data Encrypting and Finalizing Workbook

COURSE OUTCOMES:
At the end of the course, participants should be able to:

1. Apply advanced formulas to lay data in readiness for analysis
2. Use advanced techniques for report visualizations
3. Leverage on various methodologies of summarizing data