Advanced Excel: Functions, Power Query and Power Pivots

Advanced Excel Functions and Features

• Table tool

• Naming cells and ranges
• Data validation using custom formulas
• Conditional formatting using formulas
• Functions:
  • Text functions: TextSplit, TextJoin, TextAfter, TextBefore
  • Trim, Clean, Find/Search, Len
  • IF, IFS, and using AND & OR
  • CountIF, SUMIF, and AverageIF
  • XLookup and Index & Match
  • Sequence, Unique, Sort, Sort By, and Filter
• ​Formula auditing

• Number and cell format
• Report layout
• Grouping and un-grouping fields
• Default and customized sorting and filtering
• Sorting using a custom list
• Creating a calculated field
• Pivot charts
• Filtering using slicers and timelines
• Slicer settings and connections
• Customizing reports using the GetPivotData option

• Get and transform: Link your Excel to external other data sources
  • Excel files
  • Text files
  • Web
  • Folder: all files and latest file
• Power Query to transform and clean up data:
  • Naming, merging, splitting and removing columns
  • Filtering rows
  • Fill, Replace, Clean, Trim, and Format 
  • Adding Date columns
  • Adding columns from examples
  • Adding custom columns and conditional columns
  • Using Parameter
  • UnPivot data
  • Grouping
  • Combining queries:
    • Append
    • Merge: The different types of joining data

• Benefits and drawbacks of PowerPivot
• Merging data from multiple tables using relationships
• Creating better calculations using the DAX Formulas
• Using DAX to create calculated fields
• Formatting using DAX measures
• Date DAX measures
• Calculate and Related functions

Excel Automation Tools

• Data types
  • Currencies
  • Geography
  • Organization
  • Stocks
• Automate routine tasks with office scripts (e.g., formatting sheets)
• Practical examples of using Macros