Certificate in Advanced Excel
Duration - 1 Months
Introduction to MS Excel and Understanding Basic Working with it
• Introduction to MS Excel, Quick review on MS Excel Options, Ribbon, Sheets and
• Difference between Excel 2003, 2007, 2010 and 2013
• Saving Excel File as PDF, CSV and Older versions
• Using Excel Shortcuts with Full List of Shortcuts
• Copy, Cut, Paste, Hide, Unhide, and Link the Data in Rows, Columns and Sheet
• Using Paste Special Options
• Formatting Cells, Rows, Columns and Sheets
• Protecting & Unprotecting Cells, Rows, Columns and Sheets with or without Password
• Page Layout and Printer Properties
• Inserting Pictures and other objects in WorksheetsWorking With Formulas/Functions
• Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, MATCH, TRANSPOSE etc
• Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
• Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc
• Math and Trigonometry Functions: RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc
• Statistical Functions: AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK, COUNTIF, MAX,MIN etc
• Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etcConditional Formatting
Data Sorting and Filtering
Pivot Tables & Charts
V look up & H lookup
VBA Macro with MIS
• 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
• Variables
• Working across applications
• File System Objects
• Macro Security
• Using forms
• Programming menus and toolbars