Course Description

This Module covers logical and date functions, database management, advanced filters, data validation, and lookup techniques. Gain expertise in macros, dashboards, VBA snippets, and sensitivity analysis for dynamic reporting. Perfect for professionals looking to streamline workflows and master data handling!

Module Overview

Category Functions/Features
Advance Logical Countif, Sumif, Averageif, Sumifs, Countifs, Averageifs, Its application in dashboards
Advance Date Workday, Networkday, Workday.Intl, Networkday.Intl, EOMonth & Datedif
Database Dsum, Dmax & Daverage
Table Table Format, Automatic updation of formulas, Slicer
Cell Naming Name range, Dynamic Naming, Application
Data Validation Data Validation, Lists, Rules, Input and Error Messages and Basic Form Controls, Designing Forms, Dynamic Drop-down
Lookup & Reference Functions 3-D Vlookup, Fuzzy lookup, Index and Match, combining match with Vlookup, Choose, Indirect, Offset, Application
Advance Filter Single and multiple-level subtotals, subtotal formula, group ungroup, unique values, custom filters & Advance Slicer.
Remove Duplicates Various techniques to remove duplicates, extract unique values and to compare lists and compile data
Data Cleaning Goto Special, cleaning techniques for imported data to avoid error, Using Filters to Clean Data
Circular References Understanding model circularity & why some professionals use it and others don’t, Locating and managing circular reference and eliminating non-intentional circular references.
Security & Sharing Unlocking cells & protecting worksheets, Sharing workbooks, Tracking changes
Formula Auditing Techniques Check Formulas, Spot errors, Linking and cleaning Techniques, Tracing Precedents and Dependents
Advance Charts Thermometer, Speedometer, Sparklines, Target Charts, Gantt Charts, Form Controls Based Dynamic Charts
Macros Run, record, use, save and work with them
VBA Code Snippets To be able to use free VBA code snippets available online and use them to perform certain tasks like convert a number in word form
Customizing Ribbon Have your own ribbon like File, View with your designed Functions to perform repetitive tasks, automation and extremely useful time saving technique.
Scenario Manager Scenario analysis, Goal Seek, Data Table, use of macros to make it user friendly
Sensitivity Analysis Running Sensitivity Analysis with Drop-down menus or Data Validation Tools
Dashboard Preparation Dashboard for MIS, Analysis, different dashboards for different business segments, Auto report Generation – 100s with a click, Calculated Fields, Using Slicers, Connect a slicer to pivot tables from different sources and easy formatting techniques (formulas like offset, advance formatting, macros, hyperlink, etc. in creating Dashboards)

Category	Functions/Features
Advance Logical	Countif, Sumif, Averageif, Sumifs, Countifs, Averageifs, Its application in dashboards
Advance Date	Workday, Networkday, Workday.Intl, Networkday.Intl, EOMonth & Datedif
Database	Dsum, Dmax & Daverage
Table	Table Format, Automatic updation of formulas, Slicer
Cell Naming	Name range, Dynamic Naming, Application
Data Validation	Data Validation, Lists, Rules, Input and Error Messages and Basic Form Controls, Designing Forms, Dynamic Drop-down
Lookup & Reference Functions	3-D Vlookup, Fuzzy lookup, Index and Match, combining match with Vlookup, Choose, Indirect, Offset, Application
Advance Filter	Single and multiple-level subtotals, subtotal formula, group ungroup, unique values, custom filters & Advance Slicer.
Remove Duplicates	Various techniques to remove duplicates, extract unique values and to compare lists and compile data
Data Cleaning	Goto Special, cleaning techniques for imported data to avoid error, Using Filters to Clean Data
Circular References	Understanding model circularity & why some professionals use it and others don’t, Locating and managing circular reference and eliminating non-intentional circular references.
Security & Sharing	Unlocking cells & protecting worksheets, Sharing workbooks, Tracking changes
Formula Auditing Techniques	Check Formulas, Spot errors, Linking and cleaning Techniques, Tracing Precedents and Dependents
Advance Charts	Thermometer, Speedometer, Sparklines, Target Charts, Gantt Charts, Form Controls Based Dynamic Charts
Macros	Run, record, use, save and work with them
VBA Code Snippets	To be able to use free VBA code snippets available online and use them to perform certain tasks like convert a number in word form
Customizing Ribbon	Have your own ribbon like File, View with your designed Functions to perform repetitive tasks, automation and extremely useful time saving technique.
Scenario Manager	Scenario analysis, Goal Seek, Data Table, use of macros to make it user friendly
Sensitivity Analysis	Running Sensitivity Analysis with Drop-down menus or Data Validation Tools
Dashboard Preparation	Dashboard for MIS, Analysis, different dashboards for different business segments, Auto report Generation – 100s with a click, Calculated Fields, Using Slicers, Connect a slicer to pivot tables from different sources and easy formatting techniques (formulas like offset, advance formatting, macros, hyperlink, etc. in creating Dashboards)