Advanced Excel Formulas and Functions course.

COURSE OUTLINE

 Module one: LOGICAL FUNCTIONS

•   Understanding Logical Functions
•   Using IF With Text
•   Using IF With Numbers
•   Nesting IF Functions
•   Using IFERROR
•   Using TRUE and FALSE
•   Using AND
•   Using OR
•   Using NOT
•   SUMIF & SUMIFS
•   COUNTIF & COUNTIFS

Module Two: LOOKUP FUNCTIONS & Project Plan (Gantt chart)

•   Understanding Data Lookup Functions
•   Using CHOOSE
•   Using VLOOKUP
•   Using VLOOKUP for Exact Matches
•   Using HLOOKUP
•   Using INDEX
•   Using Match
•   Understanding Reference Functions
•   Using ROW and ROWS
•   Using COLUMN and COLUMNS
•   Using ADDRESS
•   Using INDIRECT
•   Using OFFSET

 Module Three: WHAT-IF ANALYSIS

•   Scenario Manager
•   Goal Seek
•   Data Table

Module Four: FORMULA AUDITING

•   Show formulas
•   Errors checking
•   Evaluate formula
•   Trace precedents
•   Trace dependents
•   Remove arrows

Module Five: Solver

•     Understanding How Solver Works
•     Installing the Solver Add-In
•     Setting Solver Parameters
•     Adding Solver Constraints
•     Performing the Solver Operation
•     Running Solver Reports

Module Six: VALIDATING DATA

•   Validating Data
•   Understanding Data Validation
•   Creating a Number Range Validation
•   Testing a Validation
•   Creating an Input Message
•   Creating an Error Message
•   Creating a Drop Down List
•   Using Formulas as Validation Criteria
•   Circling Invalid Data
•   Removing Invalid Circles
•   Copying Validation Settings

Module Seven: DATA CONSOLIDATION

•   Understanding Data Consolidation
•   Consolidating Data with Identical Layouts
•   Creating a Linked Consolidation
•   Consolidating Data with Different Layouts
•   Consolidating Data Using the SUM Function

Module Eight: PIVOT TABLES& DASHBOARD MANAGEMENT

•   understanding Pivot Tables
•   Recommended Pivot Tables
•   Creating Your Own PivotTable
•   Defining the PivotTable Structure
•   Filtering a PivotTable
•   Clearing a Report Filter
•   Switching PivotTable Fields
•   Formatting a PivotTable
•   Understanding Slicers
•   Creating Slicers
•   Inserting a Timeline Filter
•   Dashboard Management

Module Nine: TIME SERIES SALES FORECASTING USING ADVANCED EXCEL

•   Understanding Time series concept
•   Historical sales data management
•   Historical data analysis
•   Time series plot
•   Understand trend components
•   The irregular component in time series
•   Design with graph quarterly time series forecasts

Module Ten: INTRODUCTION TO MODELS IN EXCEL

•   Definition
•   Keys Models and Use
•   Practical Exercises

Module Eleven: Business Analytics using Excel

•   Solver Addin
•   Histogram
•   Goal Seek
•   Data Table
•   Scenario Manager
•   Descriptive Statistics

Module Twelve: Statistical Analysis using Excel

•   Testing hypothesis
•   ANOVA
•   Covariance
•   Correlation
•   Regression

Module Thirteen: Excel VBA

•   Msg Box
•   VBA variables
•   Events
•   Array
•   VBA functions
•   Application Object

Module Fourteen: Power BI

•   Power Pivot
•   Power View
•   Power Query
•   Power Map