Course overview:

• Data management and analysis within Excel
  • Working with worksheets
  • Excel tables
  • Sorting & Filtering
  • Data Cleaning
    • Text to Column
    • Removing dublicates
    • Grouping & Subtotals
  • Conditional formatting
  • Defined Names
  • Calculation options
  • Data Validation
  • Charts
• Data mining in Excel
  • Formulas
    • Date & Time functions
    • Financial functions (PMT, RATE, NPER, PV, FV, …)
    • Logical functions (IF, IFERROR, NESTEDIF, AND, OR…)
    •  Lookup & Reference functions ( VLOOKUP, HLOOKUP, MATCH, INDEX…)
    • Statistical functions (AVERAGE, MEDIAN, MODE, STDEV, COUNT, COUNTIF…)
    • Mathematical functions (SUM, SUMIF, ROUND, RAND, RANDBETWEEN…)
    • Text functions ( LEFT, RIGHT, MID, FIND, CONCATENATE, LEN…)
  • Formula auditing
  • What-if analysis
• Data Modelling & Data Visualization
  • Pivot Table & Pivot Charts
    • Analyzing Pivot Table Data
    • Text Filters
    • Grouping and Pivot Charts
    • Value Field Settings and Show Values As
    • Calculated Fields and Calculated Items
    • Slicers and Timeliness
    • Data-Integrity Checks and Report Filters
    • Timelines and Dashboard Development
  • Power Pivot
    • Importing data from different sources
    • Working with tables
    • Relationships
    • DAX expressions for calculated columns and measures
  • Power Query
    • Build excel data model from single flat table
    • Import multiple tables
    • Create an Excel data model from the imported data
    • Create calculated measure
    • Explore several advanced DAX functions
  • Creating dynamic dashboards
  • Case studies with real datas