OUTLINE

DURATION:  2 DAYS (16 HOURS)

DIFFICULTY LEVEL: ADVANCED

1. Introduction

Participants get introduced to the Excel business tools, how they work to extract, transform, and remodel data.

Big data components

Big data and small data

Sources of big data

Big data structure

Big data analysis

Tools and technology

The Microsoft Data Platform

2. The Excel Table

Participants learn to properly organize and maintain data tables, and filtering data to obtain information.

What is a flat file?

Overview of the Excel Table

Creating the Table

Filtering & sorting the Table

The Table Ribbon

Naming & resizing the Table

Table styles

Inserting slicers

Inserting formulas in the Table

Best practices on maintaining good data

Hands-on exercise in Tables relating to the topics listed above

3. Using PivotTables

Participants learn to organize, read, and analyze huge chunks of data using the PivotTable. They also learn ways to collate complex data from multiple sources which could not be done using traditional Pivot Tables.

Creating the Pivot Table

Populating Rows, Values, Columns & Filters

Formatting values in the Pivot Table

Pivot Table subtotals & grand totals

Pivot Table Report Layout & Styles

Components in Pivot Table Options

Renaming the Pivot Table

Drilling down into data

OLAP Pivot Tables

Hands-on exercise in Pivot Tables relating to the topics listed above

4. Database Normalization

Participants are introduced to the difference between normalized and denormalized data, learn the purposes and benefits of normalizing data and carry a hands-on exercise on it.

Overview of database normalization and its importance

Understanding structured data and tables

Understanding primary keys and foreign keys

Normalization methodology- 1NF, 2NF, 3NF

Hands-on exercise on normalizing a dataset

5. Excel Power Query

Power Query is a data transformation and data preparation in Microsoft Excel and Power BI. It allows users to connect to multiple data sources, transform and clean the data, and load it into a workbook or data model for further analysis.

What is Power Query?

Connecting to data source & importing data

Creating a new query

Merging & appending queries

Automating consolidation of files

Creating an index

Changing data types, data format & replacing values

Merging columns

Unpivoting & pivoting columns

Replacing values in columns

Adding layers & scenes

Visualize data over timelines

Playing & recording the map

Hands-on exercise in Power Query relating to the topics listed above

6. Excel Power Pivot

Power Pivot is a data modelling tool which enable participants to connect difference sources of data in a single platform so that they can analyze and produce reports effectively. Learn how to connect, summarize, and manage huge data volumes and reduce the turn-around time in reporting work.

Getting started with Power Pivot

Importing data into Power Pivot

Understanding Power Pivot & Relationships

Creating Relationships between Tables

KPIs

Hierarchies

Data Analysis Expressions (DAX) language- SUM, CALCULATE, RELATED, &&, FILTER, YEAR

Power Pivot and OLAP Pivot Tables

Hands-on exercise in Power Pivot relating to the topics listed above