Skip to main content

Advanced Data Analytics with Excel Training

Back to Course Schedule
Date(s): Feb 10, 2025
Time: 8:00AM - 4:30PM
Registration Fee: $459.00
Cancellation Date: Feb 03, 2025
Location: SAO COMPUTER TRAINING ROOM
City:
Local Hotels:
Parking Info:

Parking for SAO, Professional Development courses is in Garage B (1511 San Jacinto Blvd.). The Garage signage may read 1511 San Jacinto or Garage B. The elevator in Garage B is not reliable. If you are unable to walk the stairs, please contact the professionaldevelopment@sao.texas.gov for alternate parking arrangements. Handicapped parking is free at the meters around the downtown area.

A course coordinator will email you a parking permit prior to the course start date. A permit must be displayed or you will be ticketed.


Course Description

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.


Potential CPE Credits: 8.0
Technical Hours: This class meets 8.0 CPE credits of technical training in compliance with Texas Admin. Code Rule 523.102.

Instruction Type: Live
Experience Level: ALL
Category: Computer Software and Applications

Course Objectives

Upon completion of this course, participants will be able to:

  • 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.


Detailed Course Outline

  • Introduction

  • Working with Tables and PivotTables in Excel

    • Configuring filters

    • Adding slicers

    • Configuring time-intelligence slicer

    • Adding conditional formatting to tables

    • Adding sparklines

    • Adding data bars

    • 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 and 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 Power Pivot 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

  • Conclusion


Prerequisites

Familiarity with Excel and its basic functions.


Instructors

Holly French

Holly is an exceptional lead instructor with an extensive background delivering trainings tailored to client requirements on Microsoft Power Platform, with a special emphasis in Power BI. She is an expert in creating custom curriculum and manuals and is experienced in development and presentation of customized training solutions. Holly stands out as a top choice of key customers as she consistently provides a high caliber learning engagement as a dynamic, and trusted educator.


Back to Course Schedule