Course Catalog  > Data & Databases  > Data Visualization
WA3314

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.
Request Information
Request On-Site or Customized Course Info
Course Details

Duration

1 day

Prerequisites

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