Microsoft Excel Online for beginners by Ally Tech Services

Adv.Excel & Macros(vba)
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 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 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 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 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 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 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 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 13: Summarizing of Excel

Activities based on Real Time Scenarios
Summarizing Of Excel
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 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 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
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 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 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 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 4: Loop Statements

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

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 11: User Forms

Designing and Creating Forms
Working with controls
Creating custom dialog boxes

DAY 13: Activities

UDF on extracting numbers
UDF on extracting data
UDF on number to word

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 12: User Forms

Userforms properties , Methods and Events

DAY 14: Activities

Segregation of data from one cell to different
columns
Real time activities