Formula's
- Sum,Product,Average,IF, Days360,Date,Ceiling,Floor,Round,Round up,Round down
- Power,Count,CountA,Count,Sum if,maximum,Minimum,Hlookup for single worksheet
- Vlookup for single worksheet
- Hlookup & Vlookup for multiple Sheet
- Index Show formula Or not
- Iferror,Lookup,Rate,Ppmt,Ipmt,Networkdays,Roundup
- Rounddown,Mode,Median,Received,Log,Dollar,AverageA
- Averageif,Averageifs,Mina,Maxa,Sumifs,arge,Pv,Fv,Coupdays,Coupnum
- Datedif,Daverage,Dproduct,Dmin,Dmax,,count,Dsum,Dget,Now,Today,Weekday
- SQRT,Mod,Upper,Lower,Left,Right,Exact,Len,Match,Roman,Proper,Pmt
- Concatenate
- IF Function & Nested IF Statements
- And & Or Functions
- Match and find record by VLOOKUP And HLOOKUP
- ISPMT,SLN,Effect,CUMPRINC,CUMIPMT,EoMonth,EDATE
- WORKDAYS,DDB,DB
- Approximate Match with Vlookup
- Exact match with Vlookup
- Nesting Lookup Function
- Choose Function
- Rank & Rank Average
- Rand & Rand Between
- Mod with Conditional Formatting
- Transpose,Find & mid,ISERR,OFFSET,IIR
- NPV,MONTH,HOURS,MINUTE,NETWORKDAYS.INSTL
- Transpose with Vlookup,ISNUM,ISERROR,IFERROR,SUBSTITUTE,INDIRECT
- SEARCH,TRIM,INDEX WITH SUM,COUNT,AVERAGE
Create Statements According to Function
MIS Report
- Profit & Loss a/c statement
- Balance sheet
- Worker contribution statement
- Cash flow statement
- Debtor’s & Creditors statement
- Inventory statement
- BRS Statement
- Fixed Assets & fund statement
- Payroll Report
- P.F & E.S.I Report
Working with the Web and External Data
- Inserting a Hyperlink
- Importing Data from an Access Database or Text File
- Importing Data from the Web and Other Sources
- Working with Existing Data Connections
WORKING WITH CHARTS
- Creating a Chart with the 2D or 3D
- Create Column Chart, Pie Chart
- Create Pateros Charts
- Create Speed- O-Meter Chart
- Moving a Chart One to another Sheet
- Resizing a Chart, Changing a Chart Type
- Editing Chart Text, Modifying Chart Options
- Formatting Category & Value Axis Data
- Formatting a Data Series
- Changing a Chart’s Source Data
Formats, Conditional Formatting & Sorting
- Cell Format.
- Data Bars, Color Scales & Icon Sets.
- Conditional formatting with formulas
- Conditional formatting with Form Controls.
- Sorting basic.
- Sorting based on Customized List.
- Customized Sorting (Advanced).
FORMATTING NUMBERS
- Using Conditional Formatting
- Removing Conditional Formatting
- Format As table
- Create our Styles
Data Management:
- Data Validation and dealing with Invalid Data.
- Group and Outline Data
- Data
- Data Consolidation.
- Data text to column.
- Custom Views
DATA ANALYSIS TOOLS
- Tracing Formula Precedents
- Tracing Cell Dependents
- Error Checking
- Creating a Pivot Table
- Rearranging a Pivot Table
- Filtering Pivot Table Data
- Creating a Pivot Chart
SUMMARIZING DATA
- Adding Subtotals to a List
- Nesting Subtotals
- Applying Advance Filters
- Adding Group & Outline Criteria to Ranges
- Using Data Validation
- Text to Column
WHAT –IF DATA ANALYSIS
- Using Goal Seek
- Using Data Table
- Creating & Displaying Scenarios
WORKING WITH LISTS
- Sorting Records in a List
- Using AutoFilter in a List
- Filter & Advance Filter
WORKGROUP COLLABORATIONS
- Locking/Unlocking Cells in a worksheet
- Protecting a Worksheet
- Protecting a Workbook
- Creating a Shared Workbook
- Tracking Changes to a Workbook
- Accepting & Rejecting Changes to a Workbook
- Password Protecting a Workbook
- Merging Workbooks
WORKGROUP COLLABORATION
- Web Page Preview
- Converting Worksheets into Web Pages
- Inserting Hyperlinks
- Viewing & Editing Comments
CUSTOMIZING EXCEL
- Creating a Macro with Condition
- Running a Macro, Editing a Macro
- Adding a menu to the Menu Bar
- Adding a Commands to a Menu
- Adding Buttons to a Toolbar
Print Settings
- Page Break
- Print Area
- Print Titles
- Background
Special Operation
- Link with One sheet,Another Worksheet , One Workbooks,Multiple Workbooks
- Find record by drop down list according To condition
- Dash Board
Understanding Databases
- Starting and Opening an Existing Database
- Moving Around in Access
- Understanding Datasheet View & Design View
- Using the Mouse Pointer to Navigate
- Using the Keyboard to Navigate
Creating Tables
- Creating a Database
- Creating a Table Using the Wizard
- Creating and Modifying a Table
- Adding Fields to Tables
- Adding and Editing Records
- Printing Tables
- Moving and Deleting Fields
- Deleting Records
Working with Tables
- Formatting a Table
- Modifying Field Properties
- Sorting Records in a Table
- Finding Records in a Table
- Using Filters with a Table
- Establishing Relationships Between Tables
- Creating Subdatasheets
- Importing Records From an External Source
Creating and Using Queries
- Creating and Running a Query
- Specifying Criteria in a Query
- Using Comparison Operators
- Creating a Calculated Field
- Creating a Multiple-Table Query
- Printing a Query
Designing a Form
- Creating a Form Using Auto Form
- Creating a Form Using the Form Wizard
- Adding Controls to a Form
- Modifying Control Properties
- Resizing and Moving Controls
- Entering Records into a Form
- Creating Calculated Controls
Designing a Report
- Creating a Report Using Auto Report
- Creating a Report Using Report Wizard
- Adding a Control to a Report
- Formatting a Report
- Resizing and Moving Controls
- Creating Calculated Controls
- Previewing and Printing
Automating Tasks
- Creating an Auto Keys Macro
- Using Controls to Run a Macro
- Assigning a Macro to an Event
- Assigning a Macro to a Condition
- Testing and Debugging a Macro
Understanding Databases
- Starting and Opening an Existing Database
- Moving Around in Access
- Understanding Datasheet View & Design View
- Using the Mouse Pointer to Navigate
- Using the Keyboard to Navigate
Creating Tables
- Creating a Database
- Creating a Table Using the Wizard
- Creating and Modifying a Table
- Adding Fields to Tables
- Adding and Editing Records
- Printing Tables
- Moving and Deleting Fields
- Deleting Records
Working with Tables
- Formatting a Table
- Modifying Field Properties
- Sorting Records in a Table
- Finding Records in a Table
- Using Filters with a Table
- Establishing Relationships Between Tables
- Creating Subdatasheets
- Importing Records From an External Source
Designing a Form
- Creating a Form Using Auto Form
- Creating a Form Using the Form Wizard
- Adding Controls to a Form
- Modifying Control Properties
- Resizing and Moving Controls
- Entering Records into a Form
- Creating Calculated Controls
Designing a Report
- Creating a Report Using Auto Report
- Creating a Report Using Report Wizard
- Adding a Control to a Report
- Formatting a Report
- Resizing and Moving Controls
- Creating Calculated Controls
Creating and Using Queries
- Creating and Running a Query
- Specifying Criteria in a Query
- Using Comparison Operators
- Creating a Calculated Field
- Creating a Multiple-Table Query
- Printing a Query
Automating Tasks
- Creating an Auto Keys Macro
- Using Controls to Run a Macro
- Assigning a Macro to an Event
- Assigning a Macro to a Condition
- Testing and Debugging a Macro
I - SQL Overview
- Relational database concepts, specific products
- SQL syntax rules
- Data definition, data manipulation, and data control statements
- Getting acquainted with the course database and editor
II - SQL SELECT statements
- Clauses
- The SELECT clause: columns and aliases, where expressions, order by expressions how null values behave
III - SQL Functions and Expressions
- Eliminating duplicates with DISTINCT arithmetic expressions
- Replacing null values
- Numeric operations, including rounding
- Date and time functions
- Nested table expressions
- Case logic
- Other expressions in specific dbms products
IV - SQL Updating
- The INSERT, UPDATE and DELETE statements
- Column constraints and defaults
- Referential integrity constraints
V - SQL Joins
- Inner joins with original and SQL 92 syntax
- Table aliases
- Left, right and full outer joins, Inner joins
- Self-joins
VI - SQL Sub queries and Unions
- Intersection with IN, and, Between
- Sub queries
- Difference with IS NULL and IS NOT NULL sub queries
- The purpose and usage of UNION and UNIONALL
VII - SQL Summarization
- The column functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER
- The GROUP BY and HAVING clauses Grouping in a combination with joining
MODULE 1
What is VBA & HOW VBA WORKS WITH EXCEL
- Quick Review of Macros
- Introducing the Visual Basic Editor
- Uses of record Macros
- Understanding and creating modules
MODULE 2
PROGRAMMING CONCEPTS
- VBA Sub and Function Procedures
- How to create a message box
- Write a Program to update and retrieve information using Input Box
- Understanding and using Select Case statement
- How do I define a variables and Rules for defining a Variables Name and Type
- Creating And using Variables
- Working with range Objects
- How to save and Protect Modules
MODULE 3
Decision Makers
- If……Then……Else
- If……Then……ElseIf……If
MODULE 4
Other Kinds of Loops
- Working with Do While u. Loop Procedure
- Do…… Until Loop and Do…… Loop Until
- Do……While loop and Do…… Loop While
- For each…… Next
- For……Next
MODULE 5
Workbook Obects Create or Add Single and Multiple Workbooks
- Workbook Save and Save AS
- Open Single and Multiple Workbooks
- Close Specify and Multiple Workbooks
- Activate From one workbook to another Workbook
- Open Workbook from Specific Path
- Get Workbook Name and Paths
- Hide and Unhide for Single and Multiple Workbooks
- Protect and Unprotect Worksheets
MODULE 6
Worksheet Objects insert a single and Multiple Worksheets
- Delete Specific and multiple worksheets
- Get Count of Worksheets
- Select a Specific and all Worksheets
- Get All Worksheets Name
- Hide and Unhide For Single and Multiple Worksheets
- Rename for Single and Multiple Worksheets
- Protect and unprotect worksheets
- Sort and Move worksheets
- Calculate entire worksheet
- Using VBA and worksheet Function
MODULE 7
Cell objects insert Single and multiple Row , Column and Cells
- Delete Single and Multiple row, Column and Cells
- Get Range or Address of Cell and Selection
- Navigate from one Cell to another Cell
- Select specific Range, Cell, Rows and Column
- Types of Selection and Offset method
- Insert Function In cell
MODULE 8
Reading and Writing Arrays
- Defining Arrays
- Arrays as Outputs
- Arrays as inputs
- The Arrays as a Collection
- The Array as an Array
MODULE 9
Form Controls and User Forms (Create and Design an user Form)
- Working With User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.
- How To Create Dynamic Dashboard On User Form With Different Controls
- How To Link Various User Form With Each Other To Create A Complete Interface Between User And System
MODULE 10
VBA Programming Functions
- Create a Sum Functions
- Create Multiply Function
- Create Count Function
- Extract Text & Number
- Proper Function
- Vlookup Function
- Square root Function
- Public or Private function
MODULE 11
Excel VBA Power Programming for VBA Macros
- Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening &saving Files
- How to Analyze Data On multi Worksheets And Build Summary sheets
- How to Access the Windows File and Folder System to Open and Close Workbooks
- How to protect your code Against Errors
- How to use Excel And VBA to Create Basic Dash Boards
- How to create Your own custom Business Worksheet Function in VBA
- How to create Basic Report Generation Tools Using Excel VbA, Microsoft Word and PowerPoint
- How to use the Excel Visual Basic Macro record Excel Tasks in VBA And then Interpret the code
MODULE 12
Connection between Excel VBA & other platforms
- How To Establish Connection Between VBA And Power Presentation To Create Power Point Through VBA
- How To Establish Connection Between Excel Vba And Outlooks Through Vba
- How To Establish Connection Between Excel Vba And MS Word Through Vba
