Excel Macro course for freshers by Skyworld

Course Outline

Module 1 Basic & Advanced Excel Function

Worksheet Operations, Cell Operation, Format Cells, Freeze Panes, Techniques of Paste Special, Protect Worksheets & Workbooks , Format Painter, Data Sorting using custom list, Summarizing data using Auto Outline, Concept of Cell Referencing, Relative Referencing, Absolute Referencing, Mixed Referencing,

Important Functions in Excel:

AND,OR,NOT,COUNT,COUNTA,COUNTBLANK,COUNTIF,MAX,MIN,UPPER,LOWER,PROPER,RIGHT,LEFT
MID,POWER,SUM,SUMIF,AVERAGE ,EXACT,CONCATENATE,LEN,NETWORKDAYS,MATCH,IF,VLOOKUP,HLOOKUP,SUBTOTAL
Name Manager, Conditional Formatting, Highlighting Duplicates

Data Validation :--Different Data validation in Excel ,Using list in validation
Charts in Excel :--Elements of Charts ,Major charts used in Excel
Pivot Table :--Pivot Table creating Methods ,Rearranging a Pivot Table ,Filtering Pivot Table Data ,Performing Custom Calculation ,Creating Dynamic Dashboards using pivot charts and Slicer ,Consolidating Data from external source or multiple files in less than a minute
Data Filter :--Normal Data Filter ,Advance Filter ,Calculation based filter ,Filter using wild cards , Subtotal
Analyzing data with What-If Analysis ,Determine unknown with Goal Seek ,Analyze data with Data Table ,Sparklines for Data trends

Module 2: VBA

Variables, Arrays, Constants, Data Types, Modules, Functions and subroutines,
Decisions and Looping, Strings and Functions and Message Boxes, Operators, Debugging, Errors and the Error Function, Dialogs, Common Dialog Control, Command Bars and Buttons.
Excel Object Model, Object Model--Main Objects, Business tool objects.
Charts and Graphs, Working with Databases, API Calls, Class Modules, Pivot Tables, Ribbon
Converting Labels to Numbers and Numbers to Labels, Transposing a Range of Cells, Adding Formula Details into Comments, Calculating a Range, Reversing a Label, Who Created the Workbook, Evaluating a Cell, Sorting Worksheets into Alphabetical Order, Replacing Characters in a String, Timed Events, Auto-Totalling a Matrix of Numbers, Absolute and Relative Formulas, Cells Containing Formulas, Alternate Rows and Columns of the Spreadsheet, Changing a Range of Values, Cells by Reference to a Master Cell, Hidden Sheets Without a Password.
VBA Automation Programme: Data distribution ,Data consolidate ,Outlook connectivity program ,User from programs ,data base connectivity with access ,Data insert program ,Data fetch program, Workbook Consolidation Programme, Report Automation, Sample of Productivity Report, Sample of Break Schedule & Time Report, Absenteeism , Shrinkage Report, Sample of Sales Dashboard, Sample of KPI Dashboard & many more as per student requirement.

Module 3 Access

Introduction to Microsoft Access
Concept of MS Access
How to Start Access
Quick Access Toolbar

Type of Data Type

Text
Long
Number
Date & time
Currency
Auto number
Yes/no
Hyperlink
Attachment
Calculated
Lookup wizard

Creating Database and Tables
Working with fields and records

a. Modify Table
b. Find and Replace
c. Sorting and filter

General Properties

Field size
Format
Input Mask
Caption
Default Value
Validation rule
Validation text
Required
Allow zero length
Index

Lookup

Text Box
Combo Box
List Box

Relational Database Techniques

Types of Relationship

1. One to one
2. One to many
3. Many to many

Referential Integrity
Cast Cade Update
Cast Cade Delete
Joins

Working with Queries

Select Query
Make Table
Append Query
Update Query
Cross tab Query
Delete Query

Working with Forms

a. Overview
b. Form Wizard
c. From Scratch
d. Form design
e. Sub Forms
f. Form Commands
g. Sorting and filter
h. Switch-board

Working with Reports

a. Report Wizard
b. Report Design
c. Report Calculations
d. Report Format
e. Report Summary
f. Report labels
g. Report Printing

Sharing Data with Word and Excel

a. Importing
d. Export

Working with Charts
Working with Pivot-Charts

Module 4 SQL (Structured Query Language)

SQL Syntax, SQL SELECT, SQL SELECT DISTINCT, SQL WHERE, SQL AND & OR, SQL ORDER BY, SQL INSERT INTO, SQL UPDATE, SQL DELETE, SQL Injection
SQL SELECT TOP, SQL LIKE, SQL Wildcards, SQL IN, SQL BETWEEN, SQL Aliases, SQL Joins, SQL INNER JOIN, SQL LEFT JOIN, SQL RIGHT JOIN, SQL FULL JOIN, SQL UNION, SQL SELECT INTO, SQL INSERT INTO SELECT, SQL CREATE DB, SQL CREATE TABLE, SQL Constraints, SQL NOT NULL, SQL UNIQUE, SQL PRIMARY KEY, SQL FOREIGN KEY, SQL CHECK, SQL DEFAULT, SQL CREATE INDEX, SQL DROP, SQL ALTER, SQL Auto , Increment, SQL Views, SQL Dates, SQL NULL Values, SQL NULL Functions, SQL General Data Types, SQL DB Data Types
SQL AVG(), SQL COUNT(), SQL FIRST(), SQL LAST(), SQL MAX(), SQL MIN() ,SQL SUM(), SQL GROUP BY, SQL HAVING, SQL UCASE(), SQL LCASE(), SQL MID(), SQL LEN(), SQL ROUND(), SQL NOW(), SQL FORMAT()