Course code: MSEXNL« Back

Beyond Excel I.

The use of MS Excel is your bread and butter? Do you want to learn how to create advanced analysis without programming? Do you want to look beyond the horizon and use Excel to its maximum with the cooperation of other applications? Do you analyze large volumes of data and wait for the recalculation of formulas with VLOOKUP function? Do you download data using MS Query tool , but the amount of JOIN operations in queries from time to time betrays you? Using a pivot table, but their expressive power is not good enough for you? It is high time to use Excel IN DIFFERENT WAY.

 DateDurationCourse priceHandbook priceCourse languageLocation 
10/29/2019 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
11/12/2019 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
12/5/2019 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
1/13/2020 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
3/2/2020 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
3/26/2020 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
5/14/2020 2 8 000 CZK included in course price Český jazyk GOPAS Praha
 
GTK 10/23/2019 2 7 200 CZK included in course price Český jazyk GOPAS Brno
 
12/16/2019 2 7 200 CZK included in course price Český jazyk GOPAS Brno
 
2/27/2020 2 7 200 CZK included in course price Český jazyk GOPAS Brno
 
4/14/2020 2 7 200 CZK included in course price Český jazyk GOPAS Brno
 
9/19/2019 2 250,00 EUR included in course price Slovenský jazyk GOPAS Bratislava
 
11/14/2019 2 250,00 EUR included in course price Slovenský jazyk GOPAS Bratislava
 

AffiliateDurationCatalogue priceHandbook priceITB
Praha2 8 000 CZK included in course price 0
Brno2 7 200 CZK included in course price 0
Bratislava2 250,00 EUR included in course price 0

Who is the course for

Training for all Excel users with advanced knowledge and experience (Excel 3), who want to use Excel more efficiently in combination with other applications, with main focus on PowerPivot. Suitable training for business users, who need to perform advanced analysis in Excel and create reports. Everything without programming.

Required skills

Good knowledge of MS Excel in the scope of MSEX313 course. Basic knowledge of databases concepts are suitable, but not required.

Teaching methods

Professional explanation with practical samples and examples.

Teaching materials

PowerPoint handouts

Course outline

  • PowerPivot addin
    • Purpose of addin
    • Introduction
    • Technical limits
  • Excel Datamodel
    • Building blocks
      • fact tables
      • dimensions
      • KPI – Key Performance Indicators
      • hierarchies
        • parent-child, recursive
        • intertable hierarchy
        • time, geographic, product, …
    • Relations
    • datamodel accessing
    • Family of CUBE functions
    • linked tables
  • basic DAX functions
    • DAX functions structure, rules
    • comparison to Excel worksheet functions
    • Family of Path functions
    • RELATED, RELATEDTABLE
    • LOOKUPVALUE
  • Measures – aggregations
      • Power Query addin
        • Purpose of addin
        • Introduction
        • Technical limits
      • Datasources
        • File data sources
          • Textfiles TXT, CSV, PRN, XML
          • Excelu workbooks
          • DB files (Access, DBF, …)
        • Web queries
        • Filesystem, reading of all files in folder
        • Custom Query functions
        • Introduction to M language
      • Data manipulations
        • Text values operations
        • Datetime values operations
        • Data cleanup
        • Unpivot tool
      • Merge and append queries
        • Příručka Za hranice Excelu

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