Microsoft Excel – (1 Day)

(Full Day Session)

1. Some Important Functions – At a Glance

  • Useful Text Functions – TEXT, CONCATENATE, etc.
  • Important Date Functions – EOMONTH, NETWORKDAYS, etc.
  • Demonstrating Flash Fill techniques and its usage in Excel Tables
  • Exploring Volatile Functions – TODAY, NOW, RAND, RANDBETWEEN, etc.

2.Performing complex calculations efficiently

  • Using Lookup functions – easy demonstration of VLOOKUP and HLOOKUP
  • Reverse Lookup techniques – using INDEX and MATCH
  • Effective Error handling – using ISERROR
  • Tracing formula dependents and precedents

3.Data Visualization Concepts

  • Formatting worksheets
  • Understanding Data Visualization concepts and principles
  • Creating effective charts — using thought starter
  • Exploring new components of Charts ¡n MS Excel
  • Creating Dual Series Chart – Pareto Analysis

4.Slicing and Dicing of Data – some powerful techniques

  • Creating Pivot Tables using complex datasets
  • Generating Pivot Charts Instantly
  • Implementing Slicers to build Interactive dashboards
  • Beautifying reports / tables using Sparklines

5.Advanced Data Validation and Analysis

  • Exploring Data Validation techniques and controlling inputs
  • Creating dynamic lists using data validation and Name Ranges

6.Automation using Macros

  • Introduction and classification of Macros
  • Recording simple Macros
  • Overview VBA IDE – components and features
  • Coding simple Macros using VBA