CERTIFICATE COURSE IN ADVANCED MICROSOFT EXCEL (C2AE)

(Duration 1-2  Months…………………………….Depends upon candidate grasping power)

  • Spreadsheet basics
  • Creating, editing, saving and printing spreadsheets
  • Working with functions & formulae
  • Graphically representing data : Charts & Graphs
  • Analyzing data : Data Menu, Subtotal, Filtering Data
  • Working with functions & formulae Formatting worksheets , Securing & Protecting spreadsheets
  • Formulas & Macros Formulas in Advanced Excel Course
  • Use the Function Wizard, Common functions
  • (AVERAGE, MIN, MAX, COUNT, COUNTA, ROUND, INT)
  • Nested functions , Name cells /ranges /constants
  • Relative, Absolute, Mixed cell references : >,<,= operators
  • Logical functions using IF, AND, OR, NOT
  • The LOOKUP function , Date and time functions , Annotating formulas
  • DATA Analysis in Advanced Excel Course
  • Sub Total Reports, Auto Filter
  • Password Protecting Worksheets
  • Linking Multiple Sheets
  • Sheet Referencing
  • Linking Between Word/Excel/Ppt
  • Functions: LOOKUP, VLOOKUP, HLOOKUP, COUNTIF, SUMIF in Advanced Excel Course
  • What-if-analysis
  • GOAL SEEK
  • NESTED IF
  • Reporting
  • Character Functions
  • Date Functions
  • Age Calculations
  • Consolidation of Data
  • Data Validation
  • PIVOT TABLES is part of Advanced Excel Course
  • Enter the Pivot Table Data
  • Create the Pivot Table
  • Adding Data to the Pivot Table
  • Filtering the Pivot Table Data
  • Change the Pivot Table Data
  • Analyze Data Columns in Pivot Tables
  • Adjust Data to Analyze
  • MACRO’S in Advanced Excel Course
  • Macros
  • Definition and use, Record a macro
  • Assign a macro, Run a macro
  • Store a macro, Introduction to VBA Program
  • Develop the Worksheet in Excel
  • Plan a worksheet , Row and Column labels
  • Split worksheet /box /bar, Copy data and formulas
  • Display /move toolbars , Enhance worksheet Appearance
  • Special Operations:
  • Use multiple windows : Copy/ paste between Worksheets
  • Link worksheets , Consolidate worksheets
  • Import and link from other Applications
  • Use AutoFormat : Create, use and modify styles and templates
  • Print features : Create /edit an outline
  • Graphic Operations
  • Create charts , Enhance charts, Drawing toolbar features

OR

CERTIFICATE COURSE IN ADVANCED MICROSOFT EXCEL (C2AE)

(Duration 1-2 Months…………………………….Depends upon candidate grasping power)

DAY ONE
Introduction and Basic Overview of Microsoft Excel understanding the use and purpose of using Excel for Reporting
Basic functionality of formulas and type of formulas, AutoSum functionality How to format the reports
professionally in organized way – Like border applying, alignment, font colour/size. Auto Formats of the Reports ,
Working with Workbook and Worksheets Linking of Worksheets & Workbooks Formatting Cells, Formatting Cell
Contents, Customized formatting of Cells Other Features (Paste Special and finding and replacing data etc.),
Shortcut Keys.

DAY TWO
Types of Cell References (Absolute, Relative & Mixed) Prepare Budget Allocation Report, Joining Text using functions, Text
Formulas (UPPER, LOWER, PROPER, MOD, MODE, RIGHT, LEFT, FIND, LEN) Calculations on Excel – VAT %age, Discount
%age, Interest %age Inserting Comments, hiding and show comments, selecting and deleting or finding comments. More
useful formulas (SEARCH, CHOOSE, REPLACE, SUBSTITUTE), Logical formula (IF function and its use)

DAY THREE
Conditional Formatting and its Use in different scenarios, Logical Complex Formulas (IF, OR, AND & IFERROR) Use of
Nested IF formula with examples Other Text Formulas (TEXT, CLEAN, TRIM, T, COUNT, COUNTA, COUNTBLANK,) Quick
Access Tool Bar – Data Form, Camera and Speak Cell. Defining Cell Range, Name Manager and Pasting Defined Name
Purpose and tricks of using AutoFilter.

DAY FOUR
Formatting data as Table or insert fresh table for smarter data management. How to apply multiple filters in a single
worksheet, Freezing and Unfreezing Panes. Use of Advanced Filter where AutoFilter seems to be limited and not worth
enough. Displaying unique records by using Advanced Filter. Removing duplicates and creating and unique list Data
Validation and its use in different scenarios. Summarizing the data using (COUNTIF, COUNTIFS, SUMIF, SUMIFS) Open
websites, existing files etc. by Inserting Hyperlink. Understanding the Page layout of the workbook (Orientation, Print
preview, Page Break etc.)

DAY FIVE
Statistical Functions: FREQUENCY, MODE, REPT, MIN, MAX, RANK, AVERAGEIF, AVERAGEIFS, Working with different kinds
of charts – understanding charts, and learn easier way of preparing complex, advanced and interactive charts to be used
for presentation purpose, Inserting Sparkline charts within cells. Text to Column to fetch data and more Calculating
Weighted Average.

DAY SIX
Working with dates and date functions, Calculate age as per the date of birth or any pass date, Lookup & Reference
Formulas (CELL, LOOKUP, VLOOKUP, HLOOKUP, MATCH, OFFSET and INDEX) , Customized Protection of worksheet,
Encrypting Document, Sharing Workbook and Track/Highlight Changes Formula Auditing (Trace Precedents/Dependents,
Error Checking, Show/Evaluate Formula), Watch Window to be applied on Complex Spreadsheets

DAY SEVEN
What if Analysis (Goal Seek, Scenario Manager and Data Table) Understanding & Preparing Dashboards. Use of INDIRECT
function in Data Validation Applying Subtotal to a Data Source. Export and Import Data from (Different Sources like
Access, Web, Text, CSV etc.)

DAY EIGHT
Calculation within PivotTable Month Wise / Quarter Wise Report Using Pivot Table Creating Range/Slab Wise Report
Using Pivot Table Group and Ungroup Data Working with Slicers. Financial Formulas (STEDV, PMT, PV, DIC, DB, IRR, NPV)
Activating the Developer Tab in Excel Ribbon Understanding the use of Form Controls. Understanding Macros, Recording a
new Macro, See the recorded Syntax and Run the Recorded Macro

DAY NINE
Introduction to Visual Basic Form Controls vs. ActiveX Controls Getting into the Code VBA Programming Variables. Data
types, Constants and Variables Private Subroutine – Worksheet Selection Change, Worksheet Activate. Object Oriented
Programming concepts and Illustration Create Object, Assigning Object to a Variable, Object Properties, and Object
Methods Worksheet Activate Object

DAY TEN
Operators: Arithmetic Operators, Conditional Operators, Comparison Operators and Concentration Operators. IF
statement, Else and ELSE IF Statement, With and End With Statement, Looping Statements: For-Next, For-Each, Do-While,
Do until, Do Loop Decision-Making

Menu