MS EXCEL(VBA, MACROS) | Demo Vedio| Outline|Duration: 45 Hours |Class Room & Online Training

MICROSOFT EXCEL OUTLINE

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

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

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

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

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

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

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

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

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

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

Activities

  • INDEX and MATCH
  • Dynamic Pivot ranges
  • Creation of Dynamic ranges
  • Comparison of charts using OFFSET function
  • Dynamic charts using OFFSET and Scroll bar

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

Summarizing of Excel

  • Activities based on Real Time
  • Scenarios Summarizing Of Excel

VBA MACROS CONTENT

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

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

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

Loop Statements

  • Introduction to Message Box
  • Introduction to Input Box
  • Introduction to different Loop statements
  • FOR_NEXTLOOP
  • Activity of FOR_NEXTLOOP

Conditional Statements

  • Introduction to conditional statement
  • Introduction to different conditional statement
  • Activity on different conditional statement
  • Activity on different conditional statement with LOOP

Go To Command

  • Select case decision structure
  • Activity on select case structure
  • Introduction to GOTO label command
  • Activity on GOTO label command

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

Data Segregation

  • Segregation of data in static way
  • Introduction to Rows count and Columns
  • Count Segregation of data in dynamic way

User Defined Functions

  • Introduction to USER DEFINED
  • Function Activities on USER DEFINED
  • Function Activity on File Browser

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

File system objects and file directories

  • Designing and Creating objects (files, folders)
  • Performing different operations on objects (insert, update, delete)
  • Looping the objects

Active directory objects (ADO’ s)

  • Basic active directory Queries
  • Working with active directory objects
  • Advanced active directory queries
  • Different types of connection strings
  • Working with Sql queries, joins
  • Working with different sql and access db objects

User Forms

  • Designing and Creating Forms
  • Working with controls
  • Creating custom dialog boxes
  • User forms properties, Methods and Event

Error handling

  • On error go to zero
  • On error go to resume
  • On error go to label

User defined functions

  • UDF on extracting numbers
  • UDF on extracting data
  • UDF on number to word
  • Segregation of data from one cell to different columns Real time activities

Activities

  • Working on dynamic outlook mails (email automation)
  • Working on PPT automation
  • Working on word automation

What We Train


Flair Technologies!