EXCEL GYAN
Add Extra Skills to Your Professional Career
Advance Excel and Macros (VBA) training by Industry experts
Regular|Weekend|Online|Fast-track|Workshop
Program Details
BATCH DURATION(HRS) DURATION (WEEKS) TIMINGS FEE
Weekdays 60hrs (30 Classes) 6 Weeks 7 am-9 pm (Any 2hrs) 6,000/-
Weekends 48hrs (12 Classes) 6 Weekends 10 am to 2pm 6,000/-
2 pm to 6 pm
Fast Track 40hrs (10 Classes) 10 Days 7am-9pm (Any 4hrs) 10,000/-
Online 30hrs (15 Classes) 15 Days 6am-8am 7pm-9pm 12,000/-
Our Services
|Class room training|Online training|Corporate training|
|Placement Assistance|Project Assistance|
Courses Offered
|MS-Excel & Macros (VBA) |MS-Access with SQL & Macros
(VBA) |SAS|SAP|TABLEAU|
ADVANCE EXCEL CONTENT
DAY 1: Introduction to Excel
Short Keys
Formatting (Applying Borders, Colors and Font styles)
Conversion of Formats
Merging and Wrapping the text
Conditional Formatting
Format as Table
Inserting and deletion of Rows, Columns and Sheets
Row height and Column width
Formulae based Conditional Formatting
DAY 2: Tables, Illustrations and charts
Hide and Unhide of Rows, Columns and Sheet
Protecting sheet and Workbook
Move or Copy, Rename sheet and Tab Color
Filling series of Numbers and Dates
Sorting and Filtering
Pivot Tables
Usage of Formulas in Pivot Tables
Inserting pictures, Clip art, Text box, Shapes and Smart Art
Usage of Charts (Column, Pie, Bar, Line)
Usage of Dynamic ranges in Charts
DAY 3: Proofing, Comments and Changes
Inserting Hyperlinks
Linking sheets, Cells, Workbook, Range and Mail
Header-Footer, Word Art and Signature Line
Inserting Objects
Protect Sheet
Protect Workbook
Hiding Formulas
Sharing Workbook
Track Changes (Highlight, Accept and Reject Changes)
Inserting and Editing Comments
DAY 4: Page setup, Scale to fit and Arrange
Inserting Comments and Spell check
Freeze Panes (Rows and Columns)
Save workspace, Switch windows and Split window
Arranging the window (Vertically and Horizontally)
Page Layout, Gridlines and Formula bar
Paper margins, Gridlines and Paper size
Page breaks, Applying background and Print titles
Row repeat at top and Print preview
DAY 5: Connections and Data Tools
Data Importing (From Access, Web and Text)
Other Sources (SQL server and XML)
Advance Sorting and Filtering
Text to Columns
Removing Duplicates
Data Validation
List box, Formula based restrictions
Customization of error alert and Input box
Types of Alerts (Stop, Warning and information)
Highlighting the Invalid data
DAY 6: Data Tools and Outline
Data Consolidation
Scenario Manager
Goal Seek
Data Table
Group and Ungroup
Adding subtotals to the list
Defining name to the range
Name manger editing
Trace precedents/Trace Dependents
Evaluate Formulas
DAY 7: Text, Arithmetical Functions
UPPER, LOWER, PROPER, LEN, LEFT, RIGHT, MID,
FIND, TRIM, CLEAN, CHAR, CODE, CONCATENATE,
SUBSTITUTE, EXACT, REPT, REPLACE, SEARCH,
VALUE and TEXT.
ABS, ROUND, SUM, SUMIF, SUMIFS, SUMPRODUCT, SUBTOTAL and
RANDBETWEEN
Activities based on the above functions
DAY 8: Date & Time and Logical Functions
DATEDIF, DATE, TODAY, NOW, WEEKDAY, MONTH, YEAR, YEARFRAC,
NETWORKDAYS, DAYS360, MINUTE, HOUR, SECOND, WEEKNUM, EDATE and
EOMONTH.
IF, AND, OR, NOT, TRUE, FALSE, IFERROR and Nested Functions.
Activities based on the above functions
DAY 9: Statistical and Information Function
AVERAGE, AVERAGEIF, AVERAGEIFS, COUNT, COUNTA, COUNTBLANK,
COUNTIF, COUNTIFS, LARGE, SMALL, MAX, MIN, RANK, ROWS, ROW,
COLUMN and COLUMNS
ISBLANK, ISERROR, ISNUMBER, ISTEXT and ISNA
Activities based on the above functions
DAY 10: Lookup and References
VLOOKUP, HLOOKUP, INDEX, MATCH, OFFSET, CHOOSE and INDIRECT.
VLOOKUP with MATCH, IFERROR, WILDCARD and CHOOSE.
Combination of Formulas
Array Formulas
VLOOKUP and COLUMN
VLOOKUP with IF and VLOOKUP with AND
Activities based on the above functions
DAY 11: Activities
INDEX and MATCH
Dynamic Pivot ranges
Creation of Dynamic ranges
Comparison of charts using OFFSET function
Dynamic charts using OFFSET and Scroll bar
DAY 12: Activities
SUM and CHOOSE
Sorting the data using formula
Multiple VLOOKUP
Multiple INDEX
Leave Tracker using conditional formatting
Using formula in conditional formatting to highlight second repeated
value
DAY 13: Summarizing of Excel
Activities based on Real Time Scenarios
Summarizing Of Excel
MACROS CONTENT
DAY 1: Introduction to Macros
Introduction to Macros
Introduction to VBA
Importance of Developer Tab and VB window
Path to get into VB window
Introduction to different windows in VB
Enabling the Macros to run the program
Macro workbook saving
Introduction to cell reference
DAY 2: Introduction to Properties
Introduction to object model
Introduction to property and methods
Importance of arguments
Usage of different property and methods with cells
Introduction to sheet reference
Usage of different property and methods with sheets
DAY 3: Declaration of Variables
Introduction to workbook reference
Usage of different property and methods with workbook
Introduction of variable declaration
Importance of different data types
Different levels of declaration of variables
Different ways of declaration of variables
DAY 4: Loop Statements
Introduction to Message Box
Introduction to Input Box
Introduction to different Loop statements
FOR_NEXTLOOP
Activity of FOR_NEXTLOOP
DAY 5: Conditional Statements
Introduction to conditional statement
Introduction to different conditional statement
Activity on different conditional statement
Activity on different conditional statement with LOOP
DAY 6: Go To Command
Select case decision structure
Activity on select case structure
Introduction to GOTO label command
Activity on GOTO label command
DAY 7: Loops
Introduction to TO_DOLOOP
DO_WHILE LOOP
Activity on DO_WHILE LOOP
DO_UNTIL LOOP
Activity on DO_UNTIL LOOP
Introduction to FOR EACH NEXT LOOP
Usage of SET statement
Activity on FOR EACH NEXT LOOP
DAY 8: Data Segregation
Segregation of data in static way
Introduction to Rows count and Columns count
Segregation of data in dynamic way
DAY 9: User Defined Functions
Introduction to USER DEFINED function
Activities on USER DEFINED function
Activity on File Browser
DAY 10: Recording of Macros
Record Macro
Running a Macro
Running a Macro from the Macros Dialog Box
Creating a short key to run Macro
Running a Macro with a short key
Assigning a Macro to a menu or tool bar
Editing a Macro with VB
DAY 11: User Forms
Designing and Creating Forms
Working with controls
Creating custom dialog boxes
DAY 12: User Forms
User forms properties, Methods and Events
DAY 13: Activities
UDF on extracting numbers
UDF on extracting data
UDF on number to word
DAY 14: Activities
Segregation of data from one cell to different columns
Real time activities
We are located at
MADHAPUR: 1-90/2/A/1, Plot No: 2, Arunodaya Colony, Near Image
Hospital, Madhapur – 500081
Landmarks: In between The Mobile Stores and Club house showroom
building.
Beside image hospital.
Contact: 040-66812341 / 040-65555060 / 9550828939
GACHIBOWLI: 2nd Floor, Above Apollo Pharmacy, Opp.DLF Building 3rd Gate,
Gachibowli – 500032
Contact: 9885901144
Email: info@excelgyan.co.in
Website: www.excelgyan.co.in
