Basic Excel Training

  • Checking Excel Version, number of rows and columns
  • Overview of Workbooks and Worksheets, Navigating Worksheets, Renaming sheet Tabs 
  • Knowing the Name box, Ribbon and status bar, customizing the quick access tool bar and ribbon
  • Freeze panes, wrap text, CTRL ENTER technique
  • Basic cell references, operations & using the Function Wizard (Function Arguments screen)
  • Selecting Ranges (Basic shortcuts of Selection)
  • Basic Functions: SUM, COUNT, COUNTA, AVERAGE, MAX, MIN, VLOOKUP, COUNTIF, SUMIF, TODAY
  • Basic Text Functions: PROPER, UPPER, LOWER, LEFT, RIGHT, MID, LEN, TRIM, CONCATENATE
  • Using the ‘equal to’ operator to check for a condition, Basic IF function
  • Simple VLOOKUP of exact match nature to fetch only one column of data, Properties of VLOOKUP
  • Creating simple drop-down lists using Data Validation
  • Basic Conditional Formatting to apply color, Top-Bottom Rules
  • Introduction to Sorting and Filtering
  • Creating a Simple Pivot Table and a Chart

Intermediate Excel Training

  • Name box and its uses, properties associated with Named Ranges, using Name Manager
  • Creating Custom Lists. For e.g.: type Ambarish and his list pops up
  • Referencing: Absolute and Partial (using the dollar symbol with F4 effectively)
  • Using the OPTIONS Tab while Finding and Replacing Data
  • Statistical functions: SUMIFS, COUNTIFS, AVERAGEIFS, LARGE, SMALL and others
  • Other important functions: MROUND, FLOOR, CEILNG,
  • Database functions: DCOUNT, DCOUNTA, DSUM, DMAX, DMIN, DAVERAGE
  • Logical functions: IF, IF with OR, IF with AND, IF with NOT, IF with OR with AND combination
  • NESTED IF scenarios, IFS, SWITCH
  • VLOOKUP with Named Range v/s Regular VLOOKUP with manual selection
  • VLOOKUP with Exact match (False or 0) v/s VLOOKUP with Range (True or 1)
  • DATE functions (Using DATEDIF and other functions)
  • Applying drop down lists using Validation, exploring other options in the Validation window
  • Multi-Level, Custom Sorting & Text Filtering along with an overview of Advanced Filter
  • Consolidation
  • Conditional Formatting: Data Bars, Icon Sets, Color Scales
  • Pivot Tables, Grouping and ungrouping, Multiple sheets from a Pivot in one go, Dynamic Pivot
  • Working with Charts: Column, Bar, Pie
  • Recording a Macro and Properties associated with a Macro

Advanced Excel Training

  • Quick review on Various Referencing Techniques, Named Ranges and Custom Lists
  • GO-TO window (Various options within the GO-TO SPECIAL window)
  • CTRL ENTER method and CTRL SHIFT ENTER (Array Method)
  • Nested IF with OR AND combination, NESTED IF with DATE VALUE, Multiple scenarios
  • VLOOKUP with MATCH with Partial Referencing and Drop-Down Validation
  • New functions like =XLOOKUP, =SORT(), =FILTER() available with M365 subscription
  • When to use VLOOKUP With TRUE over Nested IF
  • INDEX MATCH combination
  • CHOOSE function
  • INDEX with MATCH with Partial Referencing and Drop-Down Validation
  • Data Validation: Dependent and independent drop-down lists, custom functions, input & error
  • Multi-level sorting, Custom sorting, Advance Filtering with AND OR BETWEEN NOT scenarios
  • Specific columns in Advanced filter, Output on a new sheet
  • Subtotal (Single level and multi-level), Removing Subtotals
  • New rules within Conditional Formatting, Customizing functions within New Rules of formatting
  • Pivot Table: Advanced Features of Pivot. Using Slicers and Timelines in Pivot,
  • Creating multiple Pivot Tables and linking them with a slicer
  • Examples of PowerPivot
  • Examples of Power-Query
  • Working with Charts: Combination Charts, secondary axis, Different types of charts
  • Recording a Macro and assigning short cuts and/or button to it, editing the recorded Macro