Course MS Excel specialistLC-EXCEL

The course is available on demand.

Suggest a time that suits your schedule

Remote training: online live with a trainer and a group. Available on demand, at time and place convenient to you, for groups of at least 6 participants.

4.8/5 (1121)
Stars
exempt from VAT

Price: 640 EUR

ability to pay in 3 installments


percent icon first minute (30+ days before) - 3%

percent icon access to recordings if needed

shake hand icon For those interested, free workshops in HR

percent icon practical exercises and mini-projects

coffee cup icon refreshments included

desktop icon computer station included

Categories: Excel, VBA

This course prepares to bring skills in using MS Excel from basic to advanced levels. The course provides students with both an overview of the advanced features and functions of the program and knowledge of their practical applications. Practical exercises accompany every topic covered during the course. VBA basics will also be added, which will be explained and practically shown.

Duration

48h

Agenda

  1. Revise the basics of Excel
    • Data entry and editing, selecting, searching for cells and groups of cells
    • Copy, paste, fill cells and ranges
    • Graphic formatting
    • Cell styles (standard and custom)
    • Data types and formatting by data type
    • Comments: insertion, appearance, editing
    • Inserting and working with simple objects (graphics, frames)
    • Customize the ribbon
  2. Formulas
    • Naming cells and ranges
    • Relative, absolute and mixed addressing
    • Addressing from other sheets, using named ranges
    • Logical functions Or, And
    • Date and time functions
    • Text functions
    • Conditional function IF
    • Conditional autosums
    • Functions VLOOKUP, VLOOKUP
    • Transpose function
    • Quick analysis
  3. Naming cells
    • Name field
    • Name from selection
    • Dynamic names
    • Fast navigation
    • Readability of formulas
  4. Conditional formatting
    • Automatic formatting depending on the value
    • Rule management
    • Modifying standard rules
  5. Table object
    • Create and format tables
    • Dynamic named range
    • Convert to range
  6. Data validation
    • Checking the correctness of the entered data
    • Drop-down lists
    • Input message, types of warnings
  7. Data sorting and filters
    • Sort by value
    • Sort by multiple columns at once (custom sort)
    • AutoFilter, filtering by data type
    • Sorting and filtering with colors and icons
  8. Custom Lists
    • Filling the day of the week with months
    • Create your own custom lists
    • Sort by custom list
  9. Data Tools
    • Data Consolidation
    • Remove duplicates
    • Outline and grouping
  10. Partial Totals
    • Outline tool
  11. Check and correct formulas
    • A circular reference and setting the number of iterations of the calculation
  12. Working with charts
    • Basic chart types and specifying data sources
    • Format charts from gallery and manually
    • Create templates
    • New charts in Excel 2016
  13. PivotTables and PivotCharts
    • Create pivot tables
    • Calculations
    • Sorting and filtering
    • Create PivotCharts
    • Formatting
  14. Workbook management
    • Create templates
    • Cross-workbook links (fixing file links)
    • File properties, document inspection compatibility
  15. File sharing
    • Change management
    • Protection of ranges of workbooks and sheets
  16. Data Import
    • Simple import from database
    • Import data from a text file
  17. What if analysis
    • Search result
    • Scenario manager
  18. Advanced formulas
    • Addressing from other worksheets, using named ranges
    • Advanced date and time functions
    • Advanced text features
    • Functions VLOOKUP, VLOOKUP
    • Functions Index, Matching Position, Intermediate Adr, Ranges Intersection
    • Array formulas
  19. Naming Cells, Advanced Named Cell Work
    • Naming cells and ranges
    • Name visibility ranges
    • Referencing names from other worksheets
    • Dynamic names (no table object)
    • Name manager
    • Intermediate addr
  20. Conditional formatting
    • Dynamic formatting depending on the settings in the sheets
    • Formula dependent
  21. Table object
    • Structured references (headers, data, summary row)
    • Slicers
  22. Advanced Filters
    • Building multiple complex filtering conditions
  23. Data Tools
    • Data Consolidation
    • Text as columns
  24. Check and correct formulas
    • Dependency tracking
  25. Show formulas
    • Detector
    • Accuracy of calculations
    • Manual recalculation of the sheet and workbooks
  26. Working with charts
    • Sparklines
    • New charts in Excel 2016
  27. PivotTables and PivotCharts
    • Creation of pivot tables (dimensions and grouping, basic summaries, multiple summaries horizontal / vertical)
    • Calculations (show values ​​as)
    • Calculation fields and items
    • Sorting and filtering (by labels, by values)
    • Create PivotCharts
    • Slicers (including timeline, joining multiple tables / charts to the same slicers)
    • Formatting (styles, selecting table elements)
    • Conditional formatting (formatting visibility)
    • TakeTableData function
    • Sort by custom lists
    • Generating pivot tables based on a template
    • Data model (relationships).
  28. File sharing
    • Change management (logging changes, enabling changes, committing changes)
    • Protection of ranges of workbooks and sheets
    • Marking as final
  29. Data Import
    • Simple import from database
    • Import data from a text file
    • Data import from XM file

Download as PDF

Audience and prerequisites

This course is intended for professionals and office workers who want to learn advanced-level Excel skills.

Before attending this course, students must have:

  • Basic computer knowledge, such as keyboard and mouse skills.
  • Basic file-management skills. The student should know how to navigate to folders and files on a computer running Windows.
  • Basic knowledge of the Excel interface, workbook manipulation and formula creation.

Certificates

Course participants receive completion certificates signed by ALX.

There are currently no entries
Enter your e-mail address and we will notify you about future dates