Program: Advanced Excel
Duration : 15 hours

MS Excel

  • Grouping of Worksheets
    • Making formatting easy
    • Collective printing
  • Data Validation
    • Text, numeric…built-in
    • Using formulae, comparing with other fields
    • Creating List based on other list values
  • Conditional Formatting
    • Built-in
    • Data bars, icons
    • Formulae option
    • With dropdown lists
  • Paste Special
    • Mathematical Operators
    • Values, comments, blanks
    • Linking, transpose
    • Hyper Linking – various options
  • Naming and name in formula
    • Range names, named constants
    • Using name in formula
    • Editing names
    • Advantages of using names
    • Substituting names in existing formulae
  • VLookup and Hlookup
    • vLookup/hLookup
    • Lookup with wild characters
    • Look for repeated values
    • Lookup many values
    • Return many fields together
    • Lookup reverse searching
  • Match and Index
    • Index
    • Match
    • Index-Match a Substitute for lookup functions
    • Index-Match repeat values
  • Conditional IF Statement-Simple, Nested, Multiple
    • Simple IFo
    • Nested If
    • AND/OR/NOT functions
  • IF and IFS functions
    • Countif, Sumif, Averageif
    • Countifs, Sumifs, Averageifs
  • Database functions
    • Database functions
    • Ease of usage
    • Substituting for vLookup
  • Subtotals
    • Advantages
    • Single level subtotal
    • Multi level subtotal
  • Array Functions
    • Entry of array
    • Pros-Cons
    • Application of array functions
  • Auto Filters and Advanced Filters
    • Advantages of auto filtering
    • Drawback of auto filter
    • Adv.filter
    • Using AND/OR criteria
    • Copy to other sheet
    • Remove duplicates
  • Table in Excel
    • Advantage of Tables
    • Table formula
    • Table autofill
    • Use table to create dynamic range
  • Date and Time functions and calculations
    • Date and time datatypes, upper&Lower limits
    • Date functions
    • Creating data validation for date field
    • Time functions
    • Data validation for time
  • Goal Seek
    • Usage of Goal seek for analysis
    • Setting up the hypothesis
    • using for financial and non financial situation
  • Using MSQuery to import datao
    • Importing data form different worksheets/workbooks
    • MS Query
    • Various options to customise query
    • Parameter query
  • Consolidation
    • Consolidating data from other sheets/books
    • Different consolidation functions
    • Consolidate by value
    • Consolidate by position
  • Using solver to perform what-if analysis
    • Setting up hypothesis
    • Complex analysis
    • Solver options
    • Solver optimisation
    • Summary report by solver
  • Charting and Creating Dynamic charts
    • Simple charts
    • Changing data source, series values, chart type
    • Formatting axes, labels, legends, titles
    • Automating Chart title
    • Switch axis, plot on secondary axis
    • Customising the items with images
    • Dynamic charts
    • Special charts-Speedometer, Thermometer, Waterfall, Pareto, Gantt, Guage etc…
  • Pivot Tables-introduction, concepts
    • Advantage of Pivot table
    • Data range
    • Creating simple pivot table
    • Changing display options, totals
    • Changing the summary functions
    • Use Pivot table for Analysis
    • Create calculated field, Item
    • Grouping
    • Slicer
    • Timeline
    • Use pivot table to create frequency
    • Working on dynamic range
    • Internal vs external data source
    • Moving, refreshing, deleting
  • Pivot charts
    • Creating Dynamic pivot charts
    • Using slicers to automate
    • Editing charts
    • Changing source datao
    • Moving, editing, deleting
    • Making it a static chart
  • Creating Dynamic ranges
    • Usage and advantage of Dynamic ranges
    • Using Offset function
    • Using Excel Table
  • Optional Topics
    • Indirect function
    • Choose function
    • Getpivotdata function
    • Subtotal function
    • Aggregate function
    • Financial functions
    • Dashboards – Introduction
    • Shortcut keys – quick glance
    • Protection and track changes
    • Special commands not in ribbon