BETA

Believe in yourself and you can do unbelievable things.

MS Excel Advanced

Home MS Office MS Excel Advanced

  • OVERVIEW:

     

    Excel can be a complex and confusing program to use, but this course breaks it down into bite-sized pieces and teaches it in plain English. No prior experience in Excel or spreadsheets is required to get the most out of this excel beginners to intermediate level course.

     

    OBJECTIVES:

     

    To be able to work and do basic and complex accounting and reporting through excel, applying formulas, sorting, filtering, and creating charts and pivots. To be able to perform all your professional work requirements easily and efficiently.

     

    TARGET AUDIENCE:

     

    Ideal for professionals and college students looking for advanced skills in excel to perform there complex professional work or to create presentations in college and bag a good job in interviews.

     

    ELIGIBILITY:

     

    Operating knowledge of computers, basic idea about the purpose of MS EXCEL and how to use internet.

     

     

    PRE-REQUISITES:

     

    • PC/Laptop,
    • Microsoft Excel 2007 and above, Internet connection(for online classes)

     

  • COURSE STRUCTURE:

     

     

    LESSON 1:  Basics review                                                                                                                                         1 hour

     

    • Basic formulas
    • Keyboard Shortcuts

     

    LESSON 2: Addressing Method                                                                                                                                2 hours

     

    • Absolute referencing
    • Mixed Referencing

     

    LESSON 3. Conditional Functions                                                                                                                            2 hours

     

    • Benefits And Purpose
    • IF Statements
    • Nested If
    • SUMIF And SUMIFS
    • AVERAGEIF And AVERAGEIFS
    • COUNTIF and COUNTIFS
    • IFERROR
    • Nesting IF WITH AND, OR
    • INDEX AND MATCH

     

    LESSON 4: Lookup Functions                                                                                                                                   3 hours

     

    • Vertical Lookup (Vlookup)
    • Vlookup for multiple column –ARRAY FORMULA
    • Vlookup with INDEX AND MATCH
    • Horizontal Lookup (Hlookup)

     

    LESSON 5: Linking Sheets and Files                                                                                                                  30 minutes

     

    • Changing the default number of workbook sheets
    • Linking sheets in the same file
    • Linking different Excel files
    • Using Edit, Links
    • Viewing Different Files On One Screen
    • Saving a workspace
    • Window Split
    • Freeze Panes
    • Viewing Different Parts Of The Sheet On One Screen
    • Watch Window

     

    LESSON 6: Applying and Managing Conditional Formatting                                                                              30 minutes

     

    • Getting current date & Time
    • Formatting dates

     

    LESSON 7:What – If – Analysis                                                                                                                                 2 hours

     

    • Goal Seek
    • Scenario Manager
    • Data Tables

     

     

    LESSON 8: Essential List Management                                                                                                               30 minutes

     

    • Sorting Data (By Values, By Cell Color, By Font Color, By Cell Icons)
    • Multi Column Sort
    • Filter (By Values, By Cell Color, By Font Color, By Cell Icons)
    • Multi Column Filter
    • Advanced Filter
    • Adding Subtotals
    • Freeze Panes
    • Group and Outline
    • Data Form
    • Format As Table Feature
    • Data Consolidation.

     

    LESSON 9: Formulae Auditing Formula View                                                                                                    30 minutes

     

    • Tracing Precedents
    • Tracing Dependents
    • Using Watch Window
    • Go To Special

     

    LESSON 10: Protecting and Sharing                                                                                                                          1 hour

     

    • Sharing a file
    • Tracking changes
    • Accepting or rejecting changes
    • Applying Data validation rules
    • Inserting comments
    • Protecting cells, sheets, files
    • Password protecting a file
    • Password protecting a cell range

     

    LESSON 11: Charts/Graphs Advanced Techniques                                                                                                     1 hour

     

    • Creating Chart Using Shortcut Keys
    • Saving Custom Chart as Templates
    • Setting Chart as Default
    • Applying Trendlines
    • Formatting and Editing Series, Plot Area, Data Points Chart Area, legends, etc..

     

    LESSON 12: Summarizing Data with Pivot Tables                                                                                                     1 hour

     

    • Inserting calculated fields
    • Manipulating Fields
    • Changing Value Filed Settings
    • Using Report Filter
    • Grouping Data containing Dates and Numbers
    • Formatting Pivot Table
    • Showing and Hiding the Grand Totals
    • Refreshing Data In Pivot Table
    • Changing The Scope Of The Data source
    • Summarizing Values by Sum, Count, Average, Max, and Product
    • Show Values As % of Grand Total, % of Column Total, % of Row Total
    • Pivot Table Options
    • Pivot Chart

     

    LESSON 13: Introduction to Macros                                                                                                                           1 hour

     

    • Displaying the Developer Tab
    • Review And Purpose Of Macros
    • Where To Save Macros
    • Absolute and relative record
    • Running macros: Assigning to Quick Access Toolbar, shapes, Pictures and keyboard shortcuts

     

    Tips & Tricks to use Excel efficiently

  • Below mentioned training methodologies will be adopted while training:

     

    • Pre & Post Assessment
      • Objective and fair assessment of trainees post training
      • Demarcation between Theory and In class Practicals
      • Be as much interactive as possible
      • Encourage participants to share their ”burning questions” about the topic
      • Launching a poll question for participants to answer
      • Try to explain using famous models and studies
      • Incorporate Case based learning (case videos/ movies) and storytelling, if applicable
      • Integrate soft skills in the training, if applicable
      • Motivate learners to apply concepts from domain subjects in a real world situation
      • Practical Home work
      • Provision of skill Certification information to trainees, wherever applied and required
      • Encourage regular trainee interaction during , in-between and after the course
      • Aim to design a module that engages participants every 4 minutes
      • Encourage participants to use platform tools like Poll, Chat, Raise Hand, Screen Sharing etc.
      • Provision of learning/ reference material to the trainees
      • Regular post session response to the trainees
      • Record of participation and Certificate of achievement issued by EduSmart Skills
  • Frequently Asked Questions about MS Excel Advanced

    You will be able to work with all the basic and complex formulas, formatting charts, pivots and will be able to generate reports and logically visualize end results in excel. You will be able to create full-fledged formula driven files, pivot tables and pivot charts, Normal charts and much more

    Everything will be explained through Live examples, the structure of the course takes you through the contents one at a time. There will be practice questions for you to attempt at the end of each topic.

    Yes, you will get an elaborate handout with theoretical answers.

    Understanding about what excel is and what it does.

    The sessions will be interactive and you can ask as many questions as you want.

    We can customize the course in your desired time frame. Please contact our customer service team for details

    We would be happy to develop a course to meet your requirements.

    If you are aware of the classes or dates you may be missing, please inform the trainer in advance and we will be happy to reschedule it for you or provide you alternate options.

    Edusmart Skills is an online education platform and has no engagement in the job placement. However, our trainers will be glad to guide you in the right direction.

  • EDUSMART SKILLS CERTIFICATION

     

    The entire training course content is in line with respective certification program and helps you clear the requisite exams with ease and get the best jobs in the top MNCs.

    As part of this training you will be working on the assignments that have immense implications in the real world industry scenario thus helping you fast track your career effortlessly.

    During the program there will be assessments that perfectly reflect the type of questions asked in the exams and helps you score better.

    EduSmart Skills Course Completion certificate will be awarded on the completion of course.

  • Students Reviews for MS Excel Advanced