Data management and analysis within Excel

• Introduction to reporting in 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

Data mining in Excel

• Formulas
  • Date & Time functions
  • Text functions ( LEFT, RIGHT, MID, FIND, CONCATENATE, LEN…)
  • Logical functions (IF, IFS, SWITCH, IFERROR, AND, OR,…)
  • Lookup & Reference functions ( VLOOKUP, HLOOKUP, XLOOKUP, MATCH, INDEX…)
  • Mathematical functions (SUM, SUMPRODUCT, SUMIF, ROUND, RANDBETWEEN…)
  • Financial functions (PMT, IPMT, PPMT, NPV, IRR, PV, FV, SLN, DB,...)
  • Statistical functions (AVERAGE, MEDIAN, MODE, STDEV, COUNT, COUNTIF, CORREL,…)
• 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
  • Data cleaning
• Creating dynamic dashboards
• Case studies with real datas