COURSE OVERVIEW

 

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
Array Functions Array formulas, Array constant, Create one dimensional and two dimensional constants, Dynamic Transpose
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)

 

KNOW MORE ABOUT Data Management

Course Related Free PDF

  • Book Summary Vol – 1
  • Book Summary Vol – 2
  • Book Recommendations
  • Monthly Report – April 2024
  • MS Office Shortcuts – Windows
  • MS Excel Shortcuts – MAC
Whatsapp Youtube