Advance Excel Training Syllabus

--------------------------------------------------------------------------------------

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

Using Excel Shortcuts with Full 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

Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc

Logical Function: IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc

Database Functions: DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc

Date and Time Functions: DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc

Information Functions: CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, TYPE 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,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc

Text Functions: LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc

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

Loop UP

V – Lookup

H- Lookup

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

Duration : 40 Hours

Admission fee : LKR. 500.00 (Five Hundred) each student

Course Fee : LKR. 9000.00 (Nine Thousand) each student

** Individual Classes time and the days can be arranged to your convenient.