Introduction to MS Excel and Understanding Basic Working with it

• Introduction to MS Excel, Quick Review on MS Excel Options, Ribbon, Worksheets and Toolbar
• Difference Between Excel 2003, 2007, 2010 and 2013
• Saving Excel File as CSV, Macro Enable Sheet, PDF and Older Versions
• List of Excel Shortcuts
• Copy, Cut, Paste, Hide, Unhide, Delete and Link the Data in Rows, Columns and Sheets
• Using Paste Special Options
• Formatting Cells, Rows, Columns and Sheets
• Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
• Page Layout, Themes, Background and Printer Properties
• Inserting Pictures, Hyperlinks, Header/Footers, Shapes and Other Objects in Worksheets

Working with Formulas/Functions in Excel

• VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc
• IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
• DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc
• DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR,
• YEARFRAC, TIME, WEEKDAY, WORKDAY etc
• CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT,
• ISNUMBER, ISREF, ISTEXT, TYPE etc
• RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc
• AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK,
• COUNTIF,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc
• LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH,
• SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc

Excel Conditional Formatting

• Using Conditional Formatting
• Using Conditional Formatting with Multiple Cell Rules
• Using Color Scales and Icon Sets in Conditional Formatting
• Creating New Rules and Managing Existing Rules

Data Sorting and Filtering

• Sorting Data by Values, Colors, etc
• Using Filters to Sort Data
• Advance Filtering Options

Pivot Tables

• Creating Pivot Tables
• Using Pivot Table Options
• Changing and Updating Data Range
• Formatting Pivot Table and Making Dynamic Pivot Tables

Pivot Charts

• Creating Pivot Charts
• Types of Pivot Charts and Their Usage
• Formatting Pivot Charts and Making Dynamic Pivot Charts

VBA Macro

• Introduction to VBA Macro
• Recording Macro & Understanding Code Behind
• Editing, Writing VBA Code and Saving as Macro or Add-In
• Adding Add-Ins in Excel

Advance VBA Macro Training Syllabus

• Introduction of VBA Macros
• Recordings Macro
• Working with VBA Editor
• VBA Programming Concept
• VBA Syntax and Semantics
• Variable Type and Declaration
• Decisions Making with Operators
• Repeating Actions with a Loops
• User Defined Functions
• Developing Add In
• User Forms and GUI
• Sort and Filter, PivotTables and Pivot charts with VBA Macros
• ODBC Connectivity to SQL Database and Query Handling
• File Handling