Complete this course and get 90 days access to practice in e-learning version for free. You can choose the e-learning course in Czech, Slovak or English. This course is assigned for skilled Microsoft Excel users, who want to use special [...]
  • MSEX3
  • Duration 3 days
  • 0 ITK points
  • 19 terms
  • Praha (9 900 Kč)

    Brno (9 900 Kč)

    Bratislava (420 €)

  • Intermediate

Complete this course and get 90 days access to practice in e-learning version for free. You can choose the e-learning course in Czech, Slovak or English. This course is assigned for skilled Microsoft Excel users, who want to use special tools, techniques, methods and functions. You should already know Microsoft Excel on “Microsoft Excel - practical use” level.

This course is suitable for users of every version of Microsoft Office products. Excel is a well-matured application and control of its interface has not changed significantly since the 2007 version. There will be current Excel version installed in the classroom.

»
This course is assigned for skilled Microsoft Excel users, who want to use special tools.
You will learn all advanced techniques, methods and functions.

Microsoft Excel - practical use

Conditions in formulas

  • IF function, other logical functions
  • CHOOSE function 
  • COUNTIF, SUMIF
  • COUNTIFS, SUMIFS
  • Functions available only in new versions of Excel

Database functions

  • Critaria tables 
  • Kind of criteria 
  • Best practices

Lookup functions, joins of datasets

  • VLOOKUP, HLOOKUP, XLOOKUP functions
  • INDEX function
  • MATCH function 

Retrieving data from external databases

  • Basic ways of external data importing in Excel
  • Definition of datasource
  • Configuration of external data range
  • Import using PowerQuery addin

Datamodel

  • Relations between Excel Tables
  • Definition of Datamodel using PowerPivot addin (introduction to topic)
  • Relation between datamodel and PivotTable (extension of PivotTable calculation power)

PivotTable reports

  • Concept of PivotTables 
  • Aggregation funcions, analytical calculations
  • Additional calculations
  • Calculated fields, ratio
  • Data Consolidation using PivotTable
  • Synchronized controling of PivotTables group

Scenarios

  • Scenario as attribute of sheet
  • Stored cells 

Special mathematical methods

  • Solver 
  • Goal seek 
  • Matrix formulas

Advanced operations with worksheets/workbooks

  • Named ranges 
  • Pojmenování oblasti buněk na listu pomocí pole názvů
  • Příkaz pro práci s názvy
  • Global/local names
  • Absolute/relative names 
  • Named formulas & constants
  • Using of name in formula
  • Comparation to labels 
  • 3D formulas (Workbook as 3D structure) 
  • Workbook window visibility
  • Interactive dynamic graphical controls (Combobox, Checkbox, OptionButton, etc.) in forms, calculations, model and visual reports.

Worksheet/Worbook protection

  • Exceptions for specified users

Macros

  • Macro recorder 
  • Ways of macro execution 
  • Assignment to floating object in worksheet
Current offer
Training location
Course language

The prices are without VAT.