Training agenda
  1. Data preparation
    • Data object
    • Data correctness tool
    • Flash Fill
    • Data type conversion
    • Data import – Power Query
    • How to use named cells  and scopes in formulas
  2. Data analysis tools review
    • Data table
    • Search for result
    • Scenarios
    • Prognosis sheet
    • Advanced filtering
  3. Functions
    • Searching (VLOOKUP, MATCH, OFFSET/INDEX; …)
    • Logical functions (AND, OR, IF, IFERROR, …)
    • Database functions (DCOUNT, DSUM, DAVERAGE, DMAX, DMIN, …)
    • Date and time (DATE, NETWORK DAYS/NETWORK DAY, DAY, MONTH, YEAR, TODAY, …)
    • Text (LEN, LEFT, RIGHT, MID, LOWER, UPPER, PROPER, TRIM, VALUE, CONCATENATE, FIND, SEARCH, …)
    • Mathematical (AGGREGATE, SUM, SUMPRODUCT, SUMIFS, ROUND, …)
    • Financial (PPMT, …)
  4. Pivot Table
    • How to prepare data correctly
    • Refreshing data – dynamic scope
    • Drill down – moving on to source data
    • Practical advice related to the use of various areas
    • Grouping, calculating elements, calculating fields
    • Automatic report generating for each value from the selected field
    • Calculating percentage shares
    • The use of fragmentator and timeline to create dashboards
    • When to use Power Pivot (introduction to data model)
  5. Graphs
    • Matching a graph to data
    • Time course graph (one-cell)
    • Presenting results on 2D
    • Graph template
    • Pivot Chart
  6. Advanced conditional formatting
    • Formatting based on formulas
    • The use of formants to navigate variables
    • The use of data bars, color scales and icons
    • Management of rules
  7. Introduction to macros
    • Security
    • Registering and launching
    • Assigning macros to buttons on command bars