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