Enquiry : 9920294574

Course Curriculum

Module 1 : Overview of the Basics

General Features

  • Working with the Ribbon

  • Comments, Transpose, Hyperlinks, Print Titles

  • Protecting and un-protecting Worksheet, Workbook and Lock Cell

  • Hide Formulas

Module 2 : Working with Functions

  • IF, AND, OR, NOT, IFERROR

  • SMALL, IF(SMALL), LARGE, LARGE (Best of 3), SUM-SMALL

  • SUM, SUMIF, SUMIFS, ROUND, ROUNDUP, ROUNDDOWN, RANDBETWEEN, ABS

  • COUNT, COUNTA, COUNTIF, COUNTBLANK, RANK

  • MATCH, INDEX, OFFSET, CHOOSE

  • VLOOKUP, HLOOKUP, Advanced VLOOKUP

  • Nested : IF, INDEX MATCH, OFFSET MATCH, VLOOKUP

MIS Reports and Charts

  • Creating MIS Reports with multiple Worksheets and Workbooks

Module 3 : Data Validation

  • Specifying a valid range of values for a Cell

  • Specifying Validation Criteria based on Decimal, List, Date, Time, Text Length

  • Specifying Custom Validations based on formula for a Cell

Module 4: Working with Templates

  • Designing the structure of Template (A typical Template for Invoices)

Module 5: What-If Analysis

  • Using Goal Seek

  • Using Data Tables; Tables with two variables

  • Creating and Editing Scenario

Module 6 : Data Forms

  • Using Forms to simplify data manipulation

  • Specifying Criteria for finding Records

Module 7: Workbook Sharing and Auditing

  • Track Changes, Sharing Workbook

  • Tracing Precedents and Dependents; Tracing Errors

Module 8 : Sorting and Filtering Data

  • Sorting and Filtering lists

  • Sorting data according to Cell Color, Font Color, Icon-wise

  • Using Multiple-level Sorting, Horizontal Sorting

  • Using Custom Sorting

  • Using Advanced Filter options

  • Wild card entry in Filter

Module 9 : More Functions

  • Database Functions

  • Date and Time Functions

  • Financial Functions – PPMT, IPMT, PMT

  • Text Functions

Module 10 : Working with Google Sheets

  • Manage Google Sheet – Open, Share, Import, Download, Rename, Print

  • Work with Google Sheet Offline

  • Formatting –  Toolbar, Conditional, Alternating Colors, To-do list, Clear

  • Data – Filter, Data Validation, Protect Sheet, Remove Duplicates

  • Functions – Translate, Xlookup, Offset, Array, Query, Import Range and more

  • Insert – Chart, Image, Drawings, Hyperlink, Comment, Scrolling Table

  • Pivot Table, Slicer

Module 11 : Conditional Formatting, Working with Styles

  • Using Highlight Cells Rules

  • Hide cell contents (with a formula)

  • Highlight expiry dates

  • Highlight items in list

  • Hide errors

  • Highlight cells according to type of value (using pre-defined rules)

  • Separate Even / Odd values

  • More Examples

  • Cell Styles, Merge Styles

  • Custom Formatting

Module 12 : Working with Reports (Using Pivot Tables)

  • Creating Subtotals

  • Consolidating Data from multiple Sheets and Pivot Tables

  • Creating and Formatting Pivot Tables and Charts; Using Slicer and Timeline

  • Calculated Fields and Calculated Items

  • Analysing Multiple Tables

Module 13 : Excel VBA Macros

  • Macros using Properties and Methods

  • Macros for Lookup, Search, selective Copy

Duration : 2 Months

Fees : 5400/-