MIS Advance EXCEL , VBA , SQL

Best MIS Training @ Innozant

Innozant offer unparelelled job oriented MIS advance excel training. Using advanced excel analysis report can allow your business to keep everything filed in a neat and orderly fashion to speed things up.Microsoft Excel is an electronic spreadsheet application which is used for organizing, storing, and manipulating and analyzing data.MIS is an integrative field. MIS professionals are the "communication bridge" between business needs and technology.Management Information Systems (MIS) is the study of people, technology, and organizations. During your training, we will familiarize you with how to organize the unformated data,manipulate and analyze data and to make a meaningful report.

Innozant has been providing MIS course since long time and is one of the best computer training institute in Delhi- NCR for MIS advance Excel Training, Innozant offers facility of Computer training at New Ashok Nagar Delhi. Learning MIS Advance Excel is useful for beginners and working professionals who want to learn MIS Data Analysis Reporting, this is a short term job and quality oriented practical training, We focus on skill building approach and try to enhance students skills at par excellence.

Advanced Excel

MIS Advance Excel Training

Target Audience
Fresher and working professional want to enhance their analytical and reporting skills.We enhance skills of students/ Working professional with MIS advance excel data organizing,storing and analysing skills which have become part of life in today's computerized world.

Course Highlight

Basic Level Training
We offer basic level quality course where student learn and familiar with Basic use of Excel , how to use Ms Excel to perform calculations and financial management work by Certified and experienced trainers.
Advance Level Training
We offer advance level quality course where student can learn how to do advanced data analysis using Advanced Excel & solving analytical problems using Goal seeker, Solver, macros & Lookups etc.Innozant certified course which helps you to get skilled job with reputation in professional life.

Advantage Innozant
-: Training Satisfaction Guaranteed
-: Applied and Conceptual learning Process
-: No Limit on Practice sessions
-: Focus on practical oriented approach
-: Learn Job oriented feature i.e.Vlookup,Index,Match,offset etc
-: Competent Fees
-: Guaranteed delivery of said content
-: Experienced and renowned Trainer
-: Individual attention
-: Career counseling

Course Highlights

Introduction to Excel and Advance Excel
Excel Introduction
• An overview of the screen, navigation and basic spreadsheet concepts
• Various selection techniques
• Shortcut Keys
CUSTOMIZING EXCEL
• Customizing the Ribbon
• Using and Customizing AutoCorrect
• Changing Excel’s Default Options
• Adding a menu to the menu bar
FORMATTING AND PROOFING
• Formatting Cells with Number formats, Font formats, Alignment, Borders, etc
• Freeze Panes
• Conditional formatting
FORMULA REFRENCE
• Absolute, Mixed and Relative Referencing
EXCEL BUILT IN FUNCTIONS
• Text Functions
• Date and Time Functions
• Logical Functions
• Information Functions
• Database Functions
• Math Functions
• Statistical Functions
• Lookup & Reference Functions
• Financial Functions
WORKGROUP COLLABORATIONS
• Locking /Unlocking cells in a worksheet
• Protecting a worksheet
• Protecting a workbook
• Creating a shared workbook
• Tracking changes to a workbook
• Accepting & protecting a workbook
• Password protecting a workbook
• Merging workbooks
ADVANCED PASTE SPECIAL TECHNIQUES
• Paste Formulas, Paste Formats
• Paste Validations
• Transpose Tables
FORMULA TOOLS
• Tracing formula precedents
• Tracing cell dependents
• Error checking
SORTING AND FILTERING
• Filtering on Text, Numbers & Colors
• Sorting Options
• Advanced Filters on 15-20 different criteria(s)
USING RANGES
• Ranges in Ms Excel
• Naming Range
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
SUMMARIZING DATA
• Adding subtotals to a list
• Nesting subtotals
• Text to column
DATA VALIDATION
• Number, Date & Time Validation
• Text and List Validation
• Custom validations based on formula for a cell
• Dynamic Dropdown List Creation using Data Validation – Dependency List
• USING STYLES , THEMES , TEMPLATES AND GRAPHICS
• Applying Styles, Themes and Templates
• Adding Graphics object
PRINTING WORKBOOKS
• Setting Up Print Area
• Customizing Headers & Footers
• Designing the structure of a template
• Print Titles –Repeat Rows / Columns
USING MACROS
• Macro options
• Creating Macros
• Editing and Deleting Macros
• Use Relative References
DATA ANYLYSIS TOOL
• Goal Seek
• Scenario Manager
• Data Tables (PMT Function)
• Solver Tool
PIVOT TABLES
• Creating Simple Pivot Tables
• Basic and Advanced Value Field Setting
• Classic Pivot table
• Grouping based on numbers and Dates
• Calculated Field & Calculated Items
CHARTS AND SLICERS
• Creating a chart with the 2D or 3D
• Create column chart, pie chart
• Various Charts i.e. Bar Charts / Pie Charts / Line Charts
• Create speed – o- meter chart
• Moving a chart one to another sheet
• Formatting category & value Axis data
• Formatting a data series
• Changing a charts source data
• Using SLICERS, Filter data with Slicers
ARRAY FUNCTIONS
• What are the Array Formulas, Use of the Array Formulas?
• Basic Examples of Arrays (Using ctrl+shift+enter).
• Array with if, len and mid functions formulas.
• Array with Lookup functions.
• Advanced Use of formulas with Array.
LOOKUP FUNCTIONS
• Vlookup / HLookup
• Creating Smooth User Interface Using Lookup
• Row , Rows , Column , Columns
• Index and Match
• Nested VLookup
• Reverse Lookup using Choose Function
• Worksheet linking using Indirect
• Vlookup with Helper Column
• Address
• Offset
EXCEL DASHBOARD
• Planning a Dashboard
• Adding Tables and Charts to Dashboard
• Link with one sheet to another worksheets, One workbooks, multiple workbooks

Course Highlights

Introduction to VBA

Dashboard Reports Allow User to Get High-Level Overview of the Business and Help Them Make Quick Decisions. Dashboards are Often Called as Management Information System (MIS), Which Provides Information that Organizations Require to Manage Themselves Efficiently and Effectively.

• To Define KPIs (Key performance Indicator), Customer Service Dashboards or Project Management Dashboard (Gantt Chart)
• Dashboard Reports Based on Tables and Number or Charts/Graphs or Both
Introduction to Programming Introduction to logical thinking flowcharts & algorithms
• Define Objective, Start & End Points; Identifying Solution & Breaking it Into Sequential Steps Writing a Algorithm
• Step-by-Step Instructions, Flowcharts, Process Flow Diagrams. Excel Macros - an Introduction
Complete Review of the VBA Language(Subs, Functions, Variables, Arrays, Loops, Logic)
• Excel Macro Language Review (VBA) Including Variables, Data Types, Constants, Arrays, Operators, Expressions, Loops, Logic Decisions And Calling
• Overview Of Commanding Excel Using VBA Including A Discussion Of Objects, Properties And Methods
• The Power of Macros - What, When, How to use Macros.
• Introduction to Object Oriented Programming
• Objects, Its Functions, Methods and Properties Introduction to Events
• Details of Events, How & When to use of Events, Preparing to 'Macro' Visual Basic Editor (VBE) – Developer Tab, Security
• Introduction to the VBE, Properties window, Project Explorer, Password Protection of Code How to use the VBE - Features, Options, Intelligence Technology
• Debugging Mode, Bookmarks, Breakpoints, Watch Window, Immediate Window and Locals Window Inbuilt VBE Help Feature - Tips and Tricks
• Form Controls vs. ActiveX Controls Getting into the Code
• Message Box and Input Box Working with Data in Excel through VBA
• Data Types, Constants and Variables
• Different type of data type; How and When to use Variables to Store Information
• For-Next, For-Each, Do-While, Do until, Do Loop Decision-Making and Code Branching
• If-Then-Else, Select-Case, And/or Nested Conditions
• What is user’s Defined Functions?
• How to create & use them.
• Use of Arrays in VBA programming with one dimensional, two dimensional or multidimensional analysis
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 AnalWorking with Dynamic Ranges. Protecting Worksheets, Cells and Ranges. Working with Multiple Files. Opening & Saving Filesyze 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 Functions In VBA
• How to Create Basic Report Generation Tools Using Excel VBA, Microsoft Word And PowerPoint
• How to Use The Excel Visual Basic Macro Recorder To Record Excel Tasks In VBA And Then Interpret The Code
Overview of Using User forms To Create Business Wizard
• 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
Connection between Excel VBA & other platforms
• How to Establish Connection Between VBA and Internet Explorer to Open any Internet Website through VBA
• How to Establish Connection Between Excel VBA and Access database to update the data in access 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
Testing and Debugging Your Code
Effective Error Handling
Automation Development Reports & Live Projects

Course Highlights

Access is a relational database management system (RDBMS) from Microsoft that combines the relationalMicrosoftJet Database Engine with a graphical user interface and softwaredevelopment tool.It can alsoimport or link directly to data stored in other applications and databases.

Introduction to SQL
• SQL Course overview
• Installing the test environment
• What is SQL?
• Editors and Platforms to learn SQL
Complete SQL in a Class
• Using the basic SELECT statement
• Selecting and counting rows , columns
• Inserting ,updating and deleting data
• Import and Export data
Fundamentals of SQL
• Fundamentals of SQL
• SQL syntax overview
• Data Definition, Data Manipulation, Data Control, Transactional Control statements
• Creating tables
• Multiple related tables
• Deleting a table
• Inserting rows into a table
Explaining SQL Strings
• About SQL strings
• Finding the length of a string
• Selecting part of a string
• Removing spaces with TRIM
• strings uppercase and lowercase
Numbers and SQL
• About numeric types
• Finding the type of a value
• Integer division and remainders
• Rounding numbers
SQL Functions and Clause
• The Aggregate functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER
• The GROUP BY and HAVING clauses Grouping in a combination with joining
Triggers in SQL
• Concept of Trigger
• Create Trigger for (Insert,Update,Delete)
• Alter Trigger
What are Subselects and Views in SQL
• Creating a simple subselect
• Searching within a result set
• Creating a view
• Creating a joined view
Maintaining SQL Server Database
• Backup Database
• Restore Database
SQL Server Job Creation
• How to create job in SQL Server Agent
• How to schedule job
• What is NULL?
• How to schedule job
• Controlling column behaviours with constraints
• Changing a schema with ALTER
• Filtering data with WHERE, LIKE, and IN
• Removing duplicates with SELECT DISTINCT
• Sorting with ORDER BY
How relationship works in SQL
• Understanding joins
• Accessing related table with join
MS Access
• Access environment and tools
• Database terminology and concept
• Designing database in Access
• Understanding RDBMS
• Working with the Design side of Tables
• Create Query
• Join Tables That Have No Common Fields
• Work with Subdatasheets
• Create Sub queries
• Working with the runtime of Tables
• Data migration and importing
• Working with the Design side of Queries
• Working with the runtime of Queries
• Working with the Design side of Forms
• Adding Controls to Forms
• Creating Sub forms
• Organizing Information with Tab Pages
• Displaying a Summary of Data in a Form
• Applying Conditional Formatting
• Working with the runtime of Forms, Managing Switchboard
• Working with the Design side of Reports
• Organize Report Information
• Format Reports Include Charts in a Report
• Add a Calculated Field to a Report
• Add a Sub report to an Existing Report
• Working with the runtime of Reports
• Working with the Design side of Macros
• Creating a Macro Restricting Records Using a Condition
• Automating Data Entry Using a Macro
• Working with the runtime of Macros
• How to create a functional specification
• Build a real-world business application
• Putting altogether and deployment