Course code: MSEX313« Back

Microsoft Excel 2013 – Advanced Methods and Functions

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 training is replaced by training MSEX316 from autumn 2016.

Preparatory course for MOS certification This course has been removed from our portfolio
This section contains overview of retired courses which don’t belong to GOPAS course offer any more or were replaced by new course versions.
For more information about possibility to buy retired courses contact our customer services department (__Klientskyservis@gopas.cz) or business department (__Obchod@gopas.cz).

AffiliateDurationCatalogue priceHandbook priceITB
Praha3 9 900 CZK included in course price 0
Brno3 9 300 CZK included in course price 0
Bratislava3 330,00 EUR included in course price 0

User intensity level

Who is the course for

This course is assigned for skilled Microsoft Excel users, who want to use special tools.

What we teach you

You will learn all advanced techniques, methods and functions.

Required skills

Microsoft Excel - practical use

Teaching methods

Professional explanation with practical samples and examples.

Teaching materials

GOPAS guide book for this course.

Course Outline

Conditions in formulas

  • IF function, other logical functions
  • CHOOSE function 
  • COUNTIF, SUMIF
  • COUNTIFS, SUMIFS

Database functions

  • Critaria tables 
  • Kind of criteria 
  • Best practices

Lookup functions, joins of datasets

  • VLOOKUP, HLOOKUP functions
  • INDEX function
  • MATCH function 

Retrieving data from external databases

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

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
Příručka MS Excel 2013 - pokročilé metody a funkce [Cz/Cz]

Price:
included in course price
Příručka MS Excel 2013 - pokročilé metody a funkce [Cz/En]

Price:
included in course price
Příručka MS Excel 2013 - pokročilé metody a funkce [Sk/Sk]

Price:
included in course price
The prices are without VAT.