Home    Contact Us

ADVANCED MS EXCEL TRAINING (Intermediate & Advanced Level)

Overview:

      Our Advanced Excel training will really show you how to make Excel work for you. The course is aimed at fairly experienced Excel users whom need to learn more complex functions, advanced IF conditions formulas, dashboards, pivot tables, pivot charts, goal seek, nesting, data manipulation and protect data using the security features. If you want a more basic version, try our Basic and internediate excel training programme You will gain an insight into data tables and using excel advanced look up features to automate worksheets and analysis tools to forecast figures based on a range of scenarios and use consolidation to bring together information. We also teach you how to develop these advanced solutions on the worksheet and to assemble the proper Excel tools in ways you have never thought of before. Find out how you can utilise advanced ‘IF’ statements.

Class
Syllabus
Training Details
Session - 1

EXCEL FUNCTIONS

Uses of Excel Formulas

SUM, SUBTOTAL, ADDRESS, AVERAGE, CHAR, COLUMN, COLUMNS, CONCATENATE, COUNT, COUNTA, COUNTBLANK, EXACT, HYPERLINK, RIGHT, LEFT, MID, LEN, LOWER, UPPER, TEXT, OFFSET, PROPER, MAX, SEARCH, TIME, DATE, DAY, MONTH, YEAR, FIND, REPLACE, ROUND, ROW, ROWS, SUBSTITUTE, TODAY, TRANSPOSE, TRIM, INDIRECT, NETWORKDAYS, DATEDIF, MOD, NOW etc

Session - 2
Session - 3

EXCEL ADVANCE FUNCTIONS

Various Methods and Uses of Advance Excel Formulas

VLOOKUP, HLOOKUP, SUMIF, SUMIFS, SUMPRODUCT, DSUM, COUNTIF, COUNTIFS, IF, IFERROR, ISERROR, ISNA, ISNUMBER, ISNONTEXT, ISBLANK, ISTEXT, GETPIVOTDATA, DCOUNT, DCOUNTA, OR, AND, SEARCH, INDEX, MATCH etc

    The sessions will include:
  • Introducing the above mentioned options
  • How to use/apply the excel formulae in various situations
  • How to combine two or more excel formulae to get desired results
  • How to combine the formulae with IF Condition

Session - 4

EXCEL OPTIONS

  • Various Methods and Options of Pivot Table
  • Various Methods of Filter and Advance Filter options
  • Creating and Updating Subtotals
  • Various Methods of Text to Column options
  • Uses of Data Grouping and Consolidation options
  • Uses of Goal Seek and Scenarios Manager
  • Various Method of Sorting Data
  • Creating, Formatting and Modifying Chart
  • Data Validation, Creating drop down lists using different data sources
  • Linking Workbooks and Uses of Edit Link options
  • Excel Options, Customizing the Quick Access Tool Bar
  • Formula Auditing features and Trace formula error
Session - 5

ACCESS DATABASE & RECORD  MACRO

  • Create a New Table and Database in MS Access
  • Load the Data into Access Database
  • Create a Query in Access Database
  • Create Single and Multiple Mapping Tables
  • Filter Single and Multiple criteria using Access Query
  • Create Query for Data Grouping
  • Create Query to Select required Columns
  • Retrive specific data using Logical Access Query
  • Create Access Query to Update, Change and Delete the Information in Access
  • Recording, Viewing and Editing Macro VBA code
Session - 6

ADVANCED EXCEL  FUNCTIONS

Various Methods and Uses of IF Conditions & Advanced Formulaes
  • When should use the "IF" Conditions?
  • Creation of Multiple IF Conditions in One Cell
  • Use the IF Conditions with the Other Advance Functions
  • How to use nested IF statements in Excel with AND, OR Functions
  • How to combine two or more excel formulae to get desired results
  • How to combine the formulae with IF Condition
  • How to use/apply the Advanced excel formulae in various situations

Our session will not just stop with teaching you the above options, we go beyond to make you an expert in using the various options in real time scenario.