Data Analytics - Course syllabus (Excel + PowerBi + Python)

Data Analysis and Dashboard Reporting

Data Analysis using Excel & Power BI

Build Dynamic, Interactive Microsoft Excel Dashboards (Excel 2010, 2013, 2016,2019,O365)

What you'll learn 

• Understand and Identify the principles of data analysis 
• Effectively summarize and present data clearly 
• Use effective design principles when creating and presenting data 
• Take full advantage of the built in tools Microsoft Excel offers in order to analyze and build dashboard reports 
• Build interactive dashboard reports 
• Analyze Excel data using Excels TEXT based Functions 
• Data Mine using Excels Array Functions 
• Build Interactive Dashboard Reports with Buttons and Drop Down Menus 
• Streamline and Analyze Data with Excel PivotTables and Pivot Charts

Requirements 

• Experience working with Microsoft Excel on a Windows system
• concepts and features will also work with Excel 2013 (Professional) , 2016 , 365 , 2019

Description 

Microsoft Excel is one of the most powerful and popular data analysis desktop application on the market today. By participating in this Microsoft Excel Data Analysis and Dashboard Reporting course you'll gain the widely sought after skills necessary to effectively analyze large sets of data. Once the data has been analyzed, clean and prepared for presentation, you will learn how to present the data in an interactive dashboard report.

The Excel Analysis and Dashboard Reporting course covers some of the most popular data analysis Excel functions and Dashboard tools, including;

• VLOOKUP() Function for Looking up Information
• INDEX()/MATCH() Functions a Powerful Alternative to VLOOKUP
• TEXT() Function for Formatting
• CHOOSE() for Ultimate Flexibility in Presenting Data
• Advanced Filtering
• Summarizing Data with PivotTables and Pivot Charts
• Interactive Features to Create User Friendly Reports
• and more...

Who this course is for: 

• Experienced Microsoft Excel users who are looking to efficiently analyze large data sets and create dynamic dashboard reports

Excel Syllabus:

Excel basics

Applications of tools

Excel Intermediate

Advanced Excel

● Overview of Arrays, Formulas and Functions

● Finding Errors; New Excel Functions

● Multiple Workbooks and Windows; Comparing Workbooks

● Chart Types; Chart Recommendation

● Selecting Data, Positioning and Printing Charts

● Sorting

● Filtering

● Tables

● VLOOKUP

● MACROS

● Quick Analysis Tool

Data Analysis using Excel

● Overview of Data Analysis using Excel

● Foundational Concept of Data Analysis

● Pivot Tables for Data Analysis

● VLOOKUP for Exact & Approximate Match

● Charts for Data Analysis (Waterfall, Pivot etc)

● Visualize Data

● Customizing Charts & Graph in Excel VL

● VOOKUP for Data Analysis

Course content

1.      Managing Raw Data

1.1.                Excel Versions and features

1.2.                Working with Raw Data

1.3.                Data Housekeeping

1.3.1.     Import Data

1.3.2.     Text to Column Tricks

1.3.3.     Troubleshooting merged data

1.3.4.     Working with Unnecessary Spacings

1.3.5.     Troubleshooting Wrong Date and Time

1.3.6.     Data Cleaning and Trimming

1.3.7.     Working Text Functions

1.3.8.     Validating Data

 

2.      Working with a Single Dataset

2.1.                Transform Manual Database to Smart Datasets

2.2.                Smart Table Magics

2.3.                Smart Table Conditions

2.4.                Working with Data Sources

3.      Working with Data Analysis

3.1.                Preparing Functions

3.1.1.     Statistical Functions

3.1.2.     Logical Functions

3.2.  Quick Report with Pivot Tables

3.3.  Pivot Table Conditions

3.4.  Pivot Table Connection

3.5.  Handling Queries

3.6.  Working with Simplified Reports

 4.      Working with Multiple Datasets

4.1.                Advance Filter

4.2.                Consolidating Data

4.3.                Combining data from Multiple Dataset

4.4.                Appending Data

4.5.                Merging Data

4.6.                Lookup Functions

4.7.                Vlookup VS PowerPivot

 5.      Working with Data Reporting

5.1.                Pivot Table Data Sources

5.2.                Pivot Tables Queries and Solutions

5.3.                Pivot Table Data Grouping

5.4.                Pivot Table Subtotals

5.5.                Pivot Tables – Format Number

5.6.                Pivot Table Functions

5.7.                Pivot Table – Show Page Report Filter

5.8.                Pivot Table – Data Modelling

5.9.                Pivot Table – Row , Columns , Values and Filter Section

5.10.            Pivot Table – Rank and Comparison

5.11.            Pivot Table – Designs and Themes

5.12.            Pivot Tables – Complex Queries

5.13.            Pivot Tables – Options and Advance Format

 

6.      Working with Power Bi in Excel

6.1.                Power Query

6.2.                Power Pivot

 

7.      Preparing Dashboard

7.1.                Pivot Tables Queries

7.2.                Pivot Chart Queries

7.3.                Slicers

7.4.                Preparing Interactive Dashboard

7.5.                Adding Other Objects

7.6.                Working with 1-screen Excel Dashboard View

7.7.                Link and Add Slicers and Timeline

7.8.                Working With Sparklines

7.9.                Working with Conditional Formatting

 

8.      Automating the Dashboard Using Macros

9.      Prepare and Distribute Reports 

Power BI Syllabus:

1. Introduction to Power BI

2. Explore fundamentals of data visualization

3. Get data with Power BI Desktop

4. Clean, transform, and load data in Power BI

5. Model data in Power BI

6. How to build a simple dashboard

7. Use visuals in Power BI

8. Explore data in Power BI

9. Data analysis in Power BI

10. Publish and share in Power BI

11. Manage workspaces and datasets in Power BI

12. Introduction to creating measures using DAX in Power BI