Advanced Data Analytics with Excel Training

Are you an Excel user who wants to advance your data analytics skills by implementing the end-to-end data analytics process? This training covers importing data from various data sources, transforming/cleaning/shaping data, implementing efficient data models with relationships, hierarchies, and data categorization, data visualization using pivot tables, sparklines, indicators, data bars, maps, and embedding reports in dashboards.
Course Details


1 day


Familiarity with Excel and its basic functions.

Target Audience

Excel users who want to see different analytics options in Excel.

Skills Gained

  • Import data from different data sources, such as Excel, CSV, Web, and SQL Server.
  • Transform & clean data using Power Query Editor.
  • Perform data modeling including creating relationships and hierarchies.
  • Add calculations, measures, and KPIs using DAX.
  • Data visualization using sparklines, databars, table, and pivot table.
  • Use filters and slicers to limit the data displayed on the report.
  • Forecast data and involving what-if scenarios.
  • Use Natural Language Processing (NLP) to ask questions based on your data.
Course Outline
  • Working with Tables and PivotTables in Excel
    • Configuring filters
    • Adding slicers
    • Configuring time-intelligence slicer
    • Adding conditional formatting to tables
    • Adding sparklines
    • Adding databars
    • Adding indicators/KPIs
    • -Using the Map visual
    • Categorizing geo-spatial data
    • Add the basic map visual
    • Add the 3-D map visual
  • Working with Q&A / NLP (Natural Language Processing) in Excel 365
    • Querying data using Q&A / NLP
  • Import data using Power Query in Excel
    • Importing Excel using Power Query
    • Importing CSV using Power Query
    • Importing Web data using Power Query
    • Importing  SQL Server data using Power Query
  • Transforming/cleaning data using Power Query
    • Remove rows
    • Remove columns
    • Replace values
    • Merge columns
    • Append tables
    • Merge tables
  • Data profiling using Power Query in Excel
    • Column quality
    • Column distribution
    • Column profile
  • Working with PowerPivot in Excel
    • Connecting to data sources
    • Using DAX to add calculated columns.
    • Using DAX to create measures.
    • Using DAX to create KPIs (Key Performance Indicators)
    • Using DAX to implement time-intelligence.
    • Configure relationship between tables.
    • Create hierarchies.
  • What-if analysis using Excel
    • Scenarios and variables
    • Using Goal Seek to get a desired result.
    • Working with Data Tables
    • Preparing forecasts