MS Excel - advanced level

    Czas trwania
    Godzina rozpoczęcia
    Stopień trudności

      MS Excel - advanced level


      Remote and stationary training options

      About training

      This course is for people who wants to gain the efficiency in working with MS Excel on advanced level.

      View the training programme

      What will you gain from the training?

      Training objectives

      Participants will recap the intermediate and learn the advanced functions of Microsoft Excel and know their use in practice.
      After completing the course, participants will know how to:

      • perform intermediate operations on copying and pasting data
      • protect cells, sheets and workbooks
      • use data filter and subtotals
      • use advanced filter
      • perform operations on dates and hours
      • use text conversion functions
      • use logical functions with multiplied conditions
      • use nested functions
      • insert data from other applications
      • analyse data using goal seek and solver add-in
      • analyse data using pivot tables and charts
      • consolidate data from different source files
      • record macro and review the VBA code
      Is this training for you?

      Audience profile and Requirements 

      This course is for people who wants to gain the efficiency in working with MS Excel on advanced level.

      Duration

      Duration:

      • 3 days, 24 training hours 
      Detailed training program

      Detailed training program

      Data management

      • Efficient data entering
      • Efficient selecting
      • Go To and Go To Special tools
      • Sorting data
      • Custom lists
      • Autofilter and Advanced filter
      • Advanced operations on copying and pasting data

      Adjustment of the working environment

      • Hotkeys, facilities
      • Create and modify toolbars
      • Excel Options (autosave, input, calculations, etc.)

      Formatting

      • Cell styles
      • Format painter
      • Number formats

      Functions

      • Subtotals function and subtotal tool
      • Statistical functions (average, maximum, minimum and quantity) 
      • Text conversion functions
      • Date and time functions
      • Sheet inspection

      Tables and list management

      • Using names of cells and ranges
      • Format as Table

      Conditional functions

      • IF
      • Logical functions
      • Informational functions
      • Avoiding errors
      • Other conditional functions (countif, sumif  and others)
      • Creation of complex / multiple formulas

      Data consolidation

      • Getting external data (CSV, TXT, Access)
      • Data consolidation functions and tools
      • Search functions (lookup, vlookup, hlookup, index, match)

      Review

      • Collaboration
      • Tracking changes
      • Comments

      Protection

      • Protect worksheet and workbook
      • Secure and encrypt files

      Pivot Tables and Pivot Charts

      • How to effectively create a pivot table
      • Changing style, layout, totals, subtotals
      • Formatting pivot table fields
      • Sorting and filtering data
      • Grouping the data
      • Using calculated fields and calculated items
      • Getting data from Pivot Table
      • Advanced Pivot Table options
      • Pivot Charts

      General Analysis Tools

      • Scenarios
      • Custom Views
      • Goal Seek
      • Solver
      • Data Tables
      • One Input
      • Two Input

      Cooperation with databases

      • Microsoft Query
      • Getting data from Oracle, SQLServer, MS Access

      Macros

      • 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

      Training delivery method

      The training is carried out in the form of theoretical part in the form of mini lectures and the practical part in the form of computer exercises.

      DOWNLOAD FILE

      Development path
      • VBA language in MS Excel

      Podobne tematy

      Customer care

      Sylwester Woźniak
      Sylwester Woźniak

      sylwester.wozniak@comarch.com