Be Certified Excel Professional

    0
    31
    Map Unavailable

    Date/Time
    Date(s) - 16/12/2018
    11:00 am - 5:00 pm

    Categories No Categories


    To book for the event,

    Payment method 1: click here

    Payment method 2: Payment via PAYTM on 9654976160, Please note that if you do payment on paytm then a cashback of 10% will be provided by us. You can choose any payment method

    ( For employees of partner corporates, 20% cashback will be provided. After payment just tell us your employee ID and company name on 9654976160 / 9643749760)

    Let’s learn Excel and be efficient in working. Get certified and improve chances to crack exams on Excel.

    Save your time by learning from MOS and get the certificate after completion of the workshop. Excel is used in day to day activities but many of us do not know “How its power can be utilized in their work”

    The trainer claims a 100% refund in case you do not find it valuable so what are you waiting for?

    Declutter your desk.

    Improve efficiency.

    Automate your work.

    WHAT WE WILL COVER

    SECTION FUNCTION / TECHNIQUE
    WORKSHEET OPERATIONS Ribbon
    QAT
    Alt KeyTips
    Essential Shortcuts
    Custom Tabs
    Paste Link
    Paste Special
    Transpose
    Custom Format Shortcuts
    Line Break vs. Wrap Text
    RANGE Define Range Names
    Name Manager
    Define Constants
    $ sign for Absolute, Relative and Mixed Referencing
    Create links between closed workbooks
    DATA CLEANING Extract day from the date
    Clean date, time and numbers
    Detect blank, hard-coded, formula driven
    Fill intermittent cells
    Machine learning using FlashFill
    SORT AND FILTER Custom Sort
    Multiple filters on one sheet
    Shortcuts to create table
    Advanced Filters
    Creating Structured References with Tables
    Using SUBTOTAL and AGGREGATE
    FORMULAS AND FUNCTIONS Formula Structure
    Function Argument box
    See formula text
    Basic Functions:
    SUM, AVERAGE, COUNTBLANK, COUNT NUMBERS, COUNT TEXT, COUNT ALL, MAX, MIN
    Advanced Functions:
    SUMPRODUCT, ROUND, ROUNDUP, ROUNDDOWN
    Get closest perfect divisor MROUND
    Ignore errors and Hidden Rows / Find calculation from the filtered list:
    Find specific error types / text / numeric etc.
    Auto vs Manual calculations
    REFERENCING Linking cells between worksheets
    SUMIFS AND COUNTIFS SUMIFS with 2 criteria
    SUMIFS with range logic (E.g. from-to date)
    Using SUMIFS to calculate running cumulative total based on any primary key
    SUMIFS vs. AVERAGEIFS
    Using COUNTIFS to calculate running count based on any primary key. E.g. 1st, 2nd 3rd-time occurrences of any key
    IF IF with multiple AND OR Operators
    Using Array alternatives
    DATE AND TIME Separate elements using DAY, MONTH, YEAR
    Get complete date using DATE
    EDATE – Calculating expiry date. E.g. 3 months contract
    EOMONTH – Calculating 10th of next month for due dates.
    NETWORKDAYS.INTL, WORKDAY.INTL | Custom date formats
    Ignore weekends
    Create employee timesheet including Overtime
    Summing up hours
    Creating dynamic payment period
    TEXT LEFT, RIGHT, MID – Extract characters from a cell | TRIM, SUBSTITUTE – Cleaning data of a cell
    PROPER, UPPER, LOWER –  Change case of the text | CONCATENATE vs & – Joining strings
    Separating First Name, Middle Name, Last Name
    Removing titles from Names
    PIVOT TABLES Basics | Essential Setting (Classic View)
    Date Grouping | Move column field headings to right / left
    Calculating Count, Sum, %
    Color-scales based Conditional Formatting (Heat Map)
    Sparklines (in-cell trendline chart)
    Slicer (user-driven filter)
    PivotChart Shortcut
    Creating 100s of worksheets in seconds
    LOOKUP Overview of different forms of Lookup (use-case) – 1D exact, slabs-based, 2D, reverse, fuzzy
    VLOOKUP with TRUE – doesn’t mean approximate match
    VLOOKUP with TRUE vs. complex Nested IF statements
    VLOOKUP with MATCH for 2D lookup | Reverse Lookup with INDEX and MATCH
    Fuzzy Lookup for similar names with spelling variations.
    Overview – OFFSET | LOOKUP for case sensitive matches | LOOKUP with data array inside the formula

    LEAVE A REPLY

    Please enter your comment!
    Please enter your name here