Programmer ▪ Analyst ▪ Designer ▪ Administrator ▪ QA ISO 9001 : 2008 Certified Institute

MIS Course Contents Excel 2013-16 MS EXCEL, VBA, SQL – REPORTING & ANALYTICS

  1. What will it cover?

    With this Program you will able to learn various important aspects of Excel like Conditional Formatting, Validation, Pivot Table, Chart, Advance Filtering, Smart Art, Themes, Formula Auditing, Extraction data from Different Sources, Web Queries, Goal Seek, Solver, Table, Connections, Protection on different level and lots more…

    Additional features:

    • We will provide you sample data/reports for your practice; Assignments through which you can judge to yourself, Interview preparation question, platform where you can discuss your excel related queries/Job openings.
    • We will provide you study material related to this course.
    • You will also get recorded video of the classes that you have attended, so the content of the class remains with you for life time.

  2. Excel Content Outline


    Excel Basics:
    · Excel Terminology: Workbook, Worksheet, Row, Column, Cell
    · Excel Home Menu
    · Data and sheet formatting: Bold, Italic, Underline, Font family, font size, Color
    · Vertical, and Horizontal Alignment
    · Orientation & Indent
    · Wrap Text, Merge Cell
    · Data format: $, %, decimal position
    · Format Table & Format Cell
    · Insert Row, and Column
    · Delete Row, and Column
    · Column width, Row Height
    · Find, Replace, Go To
    · Format Cells
    · Paste Special
    · Hide & Unhide
    · Cell Reference
    · Fix Cell Range
    · Define Name
    · Spell Check
    · Translate
    · Comments
    · Hyperlink
    · Protect sheet, and workbook

    Conditional Formatting:
    · Beyond Simple Conditional Formats
    · Conditional Formatting - System defined option
    · Highlight duplicates
    · Conditional formatting - Custom Data Validation:
    · Extended Uses of Data Validation
    · working with Validation Formulae
    · Lists and Lists That Depend on Other List Selections ·

    Other Methods of Tracking down Invalid Entries Sorting & Filter:
    · Soring Level management
    · Filter, Advance Filter
    · Remove Duplicate

    Excel Advance

    WHAT IF?
    · Using Excel to help you make decisions
    · Use Goal Seek to find where you need to start to get where you need to go
    · Use an Excel Data Table to calculate dozens of possible outcomes
    · Create and manage alternative scenarios
    · Make more profit or incur less expense by using Excel Solver to identify the best solution Sparkline
    · In-cell charts – showing 12 times as much information in the same amount of space
    · Careful with that Axis
    · The different types of Sparkline: lines, columns and win loss
    · Sparkline based on a dynamic data range

    Working with External Data
    · Getting at your data using the Get External Data tools
    · Understand relational database
    · Excel 2013 Data Model create relationships within Excel

    Advanced Uses of Pivot tables
    · Use advanced pivot table techniques to do more with your data
    · Calculated Fields and Calculated items
    · Using pivot tables as the calculation engine behind management Excel 2010 and 2013

    Slicers and Timelines - make your pivot tables more interactive
    · Working with Pivot Charts
    · Building and interactive dashboard using pivot tables and Slicers

    Excel Function

    Lookup :- choose, hlookup, indirect, match, transpose, vlookup, lookup, lookup(vector),Offset

    Logical :- and, if, not, or,Iferror

    Date function :- date, datedif, datevalue, day, edate, minute, month, days360, eomonth, hour, timevalue, networkdays, now, second, time, today, weekday, workday, Workday.Intl, Networkday Intl, year etc..

    Financial function:- Rate, PMT, FV, Emi calculator etc….

    Mathematical:- Abs, Combin, Countif,Countifs, Even, Fact, Gcd, Int, Lcm, Mod, Odd, Pi, Power, Product, Quotient, Rand, Randbetween, Roman, Round, Roundup, Sign, Sum, Sumif, Sumifs, Countifs, Sumproduct, Rounddown, Sum (Running Total), Sumifs

    Statistical:- Average, Correl, Count, Counta, Large, Max, Min, Mode, Permut, Rank, Small, Frequency, Median, Split Forename And Surname, T Etc...

    Text :- Char, Clean, Code, Concatenate, Dollar, Exact, Find, Fixed, Left, Len, Value, Lower, Mid, Proper, Replace, Rept, Right, Substitute, Text, Trim, Upper Etc...

    Split & Merging Formula

    Dynamic Formulas

  3. VBA


    Excel Macro (VBA) –

    What is Variable?
    · Type, Scope, Assign, Value transfer, Declaration (Implicit / Explicit), Local, Static, Global

    Object Model
    · Hierarchy, Property/Event/Method, Difference Between Subroutine and Function with example

    Worksheet Concept
    · Different way to refer the Sheet (By Name, By Index), Visibility, Renaming,

    Deletion (Single/Multiple), Insertion (Single/Multiple)
    · Referring multiple sheets, Copy, Moving

    Workbook and Windows Concept
    · Opening, Activation, State (Minimize/Maximize/Resize), Active window, Not Operator

    Refer Cell, Range, Columns, Row
    · Consecutive Range, Split range, Column Selection (Single/Multiple), Row

    Selection (Single/Multiple), Difference between Cell and Range

    With Statement

    Current Region and Used Range concept

    Special Cells
    · Comments, Formula, Visible, Constant, Last Row, Blank

    Filter Data
    · Single Criteria, Multiple Criteria, AND operator, OR Operator, Like Operator, Top item, Bottom item

    IF Statement
    · Simple if, If and Else, Sorting example, Msgbox, If else with Msgbox integration, Multiple Elseif, Nested if

    String and Conversion Functions
    · Concatenation, Split, Ucase, Lcase, Instr, Validation, Len, Cint, Val Offset Statement
    · Current region, Resize Range, Set

    While Loop
    · With Conditions, Play with variable, Inputbox, Increment Decrement, Is Empty

    For Loop
    · Nested For Loop
    · For Each loop
    · Nested For Each Loop

    Function Procedure

    Function through looping (for and For each), Inbuilt Function (Multiple Examples)

    Select Case Statement
    · Difference between if and select case, Select case with Function Procedure,
    · Calling Procedure

    Array
    · Option Base, Fixed Size, One Dimensional, Dynamic array, LBound, UBound, Redim

    Error Handling Techniques
    · Error Type, On Error Resume Next, On Error Goto Label, On Error Resume Again
    · On Error Goto 0

    Calling Function in a Procedure
    · Through File Handling Example, Through Function Procedure

    Userform
    · Label, Combobox, Listbox, OptionButton, Checkbox, Image, Frame, ToggleButton
    · Scrollbar, SpinBar, Property Window, How to handle Event (On File opening; Click, DoubleClick; Initialization; Activate; before Entry; before Exit etc.)
    · Validate User input (Multiple Examples)
    · Form as per Application Size (Hiding Excel)
    . Disable Application Visibility

    Project Work

  4. SQL

    Access+ SQL (Basic)
    · Database terminology
    · Relational Database
    · Tables, Rows and Columns
    · Data Type
    · Referential Integrity

    DATA QUERY LANGUAGE (BASIC QUERIES)
    · SELECT COMMAND
    · Viewing Data in a Table
    · Displaying Unique Values Using the Distinct Clause
    · Order By
    · The Where Clause
    · Using Substitution Variable
    · Comparison Operator
    · Like operator
    · Wildcard Operators
    · IN operators
    · Between ...And ...Operator
    · IS NULL OPERATOR
    · Logical operator
    · Expressions in Select Clause
    · Column Aliases
    · Expression In where Clause and Order by Clause
    · Nested query
    · Join and its type
    · Inner join, outer join, left join , right join
    · Union, union all

    Numeric functions
    MOD, SQRT, ABS, Power, Least, Greatest, Round, Character Functions

    Text Functions
    Conversion, Lower,Upper, Initcap, Manipulation, Length, Concat, Substr, Instr, Ltrim, Rtrim, Replace, Reverse

    Date Functions
    Add_Months, Months_between, Sysdate, Next_day, Last_day

    AGGREGATE/GROUP FUNCTIONS (Group functions or Multiple Rows Functions)
    Sum, Avg, Min, Max, Count

Advertisment

ad

X

Request a call back