Excel Training

Excel Reviews

The course is practical oriented. Good place for learning.

Excel Course Content

Excel Syllabus

Microsoft Excel Fundamentals

• Excel Startup Screen
• Excel Interface
• Customizing the Quick Access Toolbar
• Structure of Excel Work book
• Saving Excel Document
• Opening Existing Excel Document
• Excel Shortcut Keys
• 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 in Excel

• VLOOKUP, HLOOKUP, INDEX, ADDRESS, MATCH, OFFSET, TRANSPOSE etc
• IF / ELSE, AND, OR, NOT, TRUE, NESTED IF/ELSE etc
• DGET, DMAX, DMIN, DPRODUCT, DSTDEV, DSTDEVP, DSUM, DVAR, DVARP etc
• DATE, DATEVALUE, DAY, DAY360, SECOND, MINUTES, HOURS, NOW, TODAY, MONTH, YEAR, YEARFRAC, TIME, WEEKDAY, WORKDAY etc
• CELL, ERROR.TYPE, INFO, ISBLANK, ISERR, ISERROR, ISEVEN, ISLOGICAL, ISNA, ISNONTEXT, ISNUMBER, ISREF, ISTEXT, TYPE etc
• RAND, ROUND, CEILING, FLOOR, INT, LCM, MOD, EVEN, SUMIF, SUMIFS etc
• AVEDEV, AVERAGE, AVERAGEA, AVERAGEIF, COUNT, COUNTA, COUNTBLANK, COUNTIF,FORECAST, MAX, MAXA,MIN, MINA, STDEVA etc
• LEFT, RIGHT, TEXT, TRIM, MID, LOWER, UPPER, PROPER, REPLACE, REPT, FIND, SEARCH, SUBSTITUTE, TRIM, TRUNC, CONVERT, CONCATENATE, DOLLAR etc

Excel Function Categories

• Financial functions
• Statistics Functions
• Scientific functions

Working with Excel Template

• Existing templates
• Creating Custom template

Excel 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

Excel Charts

• Column Chart
• Line Chart
• Pie Chart
• Doughnut Chart
• Bar Chart
• Area Chart
• XY (Scatter) Chart
• Bubble Chart
• Stock Chart
• Surface Chart
• Radar Chart
• Combo Chart

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

MIS Reports

• What is MIS? Intoduction & Definition
• Components & Role of MIS (Management Information System)
• Objectives & Needs of MIS (Management Information System)
• Types of Information System: TPS, DSS & Pyramid Diagram
• Ethical & Security Issues in Information System

VBA & Macros

Introduction

• What is VBA
• Use of VBA
• Recording Macro
• Procedure and function in VBA
• All Keywords
• Developer Tab
• Macro examples

Variables and Datatypes

• What is Variables
• Using Non-declared Variables
• Variable Data types
• Using Const variables

Excel Objects

• Application Objects
• Workbook Objects
• Worksheet Objects
• Range Objects

Message Box Input Box

• Prompt, Buttons, Title, Helpfile context
• Prompt, Title, Default, XPos, YPos, HelpFile, Context

VBA Works-1

• Cell Referencing
• VBA-Copy paste
• VBA-Font
• With Block
• VBA-Borders
• VBA-Alignment
• VBA-Font Color
• Background Color
• Paste Special
• Orientation
• Wrap text
• Merge Unmerge
• Clear Cell
• Delete Cells
• Rows-columns Insert
• Rows-columns Delete
• Columan Width
• Row Height
• Activate-Select
• Coumns-Hide unhide
• Sheet Protection
• Activate Sheet
• Create Work book
• Get Workbook name
• Save-close Work book
• Open-close work book
• Delete Workbook
• Create Folder
• Comment

Looping Statements

• For loop
• For each loop
• While wend loop
• Do while loop
• Do until loop

Decision making Statements

• If statement
• If…else
• If…elseif…else
• Switch statement

VBA-String

• InStr
• InstrRev
• Lcase
• UCase
• Left
• Right
• Mid
• Ltrim
• Rtrim
• Trim
• Len
• Replace
• Space
• StrComp
• String
• StrReverse

Date and Time

• Date
• cDate
• DateAddateDiff
• DatePart
• DateSerial
• FormDateTime
• IsDate
• Day
• Month
• Year
• MonthName
• WeekDay
• WeekDayName
• Now
• Hour
• Minute
• Second
• Time
• Timer
• TimeSerial
• TimeValue

Array

• Array Declaration
• ReDim
• LBound
• UBound
• Split
• Join
• Filter
• IsArray
• Erase

VBA-Error handling

• Synatx Error
• RunTime Error
• Logical Errors

User Form

• Label
• TextBox
• ComboBox
• ListBox
• Checkbox
• Option Button
• Radio Button
• image
• Command Button