Module 1

Basic Formula

1. Formulae that Add/Subtract/Multiply/Divide
2. BODMAS/Formula Error Checking
3. The Sum Function

Absolute Referencing Problems with Absolute/Relative Cell Referencing, Creating Absolute/Mixed References LOOKUP Functions

1. The VLOOKUP/HLOOKUP Functions

PIVOT Tables

1. Creating, Formatting Simple PivotTables
2. Page Field in a PivotTable
3. Formatting a PivotTable
4. Creating/Modifying a PivotChart

LOGICAL FUNCTIONS

1. IFs and Nested IF Functions
2. Using AND/OR/NOT Functions

STATISTICAL FUNCITIONS Using The SUMIF/COUNTIF Functions

1. Using The AVERAGE/COUNT/LARGER/SMALLER Functions

Pivot Tables – Advance

1. Adding new calculated Fields / Items
2. Changing the Summary Function
3. Consolidate Pivot table

Module 2

LOOKUP Functions – Advance

1. MATCH with VLOOKUP Functions
2. INDEX & MATCH Functions
3. OFFSET/ INDIRECT functions

Logical Functions – Advance

1. If Loop and Nested IF Loop Functions
2. Using IF/ISERROR Functions

Chart Data Techniques

1. The Chart Wizard
2. Chart Types
3. Adding Title/Legends/Lables
4. Printing Charts
5. Adding Data to a Chart
6. Formatting/Renaming/Deleting Data Series
7. Changing the Order of Data Series

Date/Time Functions

1. Using the Today
2. Now & Date Functions
3. Using theDatedif/ Networkdays/ Eomonth Functions
4. Using theWeeknum Functions
5. Using theEdate/ Networkdays.Intl/ Weekdays.Intl Functions.

Text Functions Using

1. The Mid/ Search/ Left/ Right Functions
2. Using the Trim/ Clean/ Upper/ Lower Functions
3. Using theSubsitute/ Text Functions

Validations

1. Input Messages / Error Alerts/ Drop-Down Lists
2. Conditional Formatting

Module 3

Advanced Filters

1. Extracting Records with Advanced Filter
2. Using Formulas In Criteria

Advanced Sorting

1. Sorting by Top to Bottom / Left to Right
2. Creating / Deleting Custom List
3. Sort by using Custom List

Hyper / Data Linking

1. Hyper linking data, within sheet / workbook
2. Linking & Updating links between workbooks & application

Math & Trigonometry Functions

1. Using SUMPRODUCT Functions
2. Using FLOOR/ CEILING/ MROUND/ MOD/ QUOTIENT Functions

Statistical Analysis

1. Conduct sensitivity and ‘What if Analysis’, Scenario Analysis, Dashboards, Interactive Financial Models, Monte Carlo Simulation

Financial Functions

1. Loan Amortization Schedule and functions like PV, FV, PMT, Rate, Nper, IPMT, CUMPMT, etc

 

Module 4

Summarizing Data

1. Creating Subtotals/Nested Subtotals
2. SUBTOTALS Formula

Outlining

1. Creating/Working with an Automatic/Manual Outline
2. Grouping / Ungrouping

Consolidation

1. Consolidating Data with Identical/Different Layout

Using Auditing Tools

1. Displaying/Removing Dependent & Precedent Arrows
2. Evaluate Formula – Step IN/ Step Out

Custom Views

1. Creating Custom Views
2. Displaying Custom Views
3. Deleting Custom Views

Sharing and Protecting Workbooks

1. Sharing Workbooks & Tracking Changes
2. Protecting sheets / workbooks / Files

Importing & Exporting Data

1. Importing Data from Database/ Text Files/ Web
2. Exporting Data
3. Changing External Data Range

Module 5

Dashboard Preparation-

1. Dashboard for MIS, Analysis, different dashboards for different business types and business segments, will include a self-assignment (formulas like offset, advance formatting, macros, hyperlink, etc.)

Advance Pivot Formatting, grouping, computing %, Summarizing Values, Auto report Generation –100s with a click, Calculated Fields, Connecting Multiple Sources of data, Use of Slicer and Report Filter to create quick dashboard, Pivot charts, Power pivot and Power View –Introduction, and other Advance features, Consolidating data across multiple table.