Data Analytics Certificate Program

Part 1: Worksheets and Workbooks Management

– Understanding Worksheets and Workbooks

– Manipulating Worksheets: Rename, Insert, Delete, Copy, Move

– Worksheet Customization: Tab Color and Grouping

Part 2: Data Visualization with Charts and Graphs

– Creating Various Chart Types

– Chart Title Editing and Formatting

– Creating Charts on Different Platforms

Part 3: Excel Tables and Data Management

– Introduction to Excel Tables and Their Benefits

– Creating and Modifying Excel Tables

– Key Features of Excel Tables and Data Management

– Introduction to Pivot Tables and Pivot Table Creation

– Advanced Pivot Table Techniques

Part 4: Data Analysis and Advanced Functions

– Sorting and Filtering Data

– Analyzing Data Using COUNTIF and SUMIF

– Advanced Data Analysis: Pivot Tables and Data Toolpak

– Exploring What-If Analysis, Descriptive Statistics, and Forecasting

– Regression, Anova, and Simulation Analysis

Part 5: Formulas and Functions Mastery

– Understanding Basic Excel Functions: SUM, AVERAGE, COUNT

– Mathematical and Logical Functions: MODULUS, POWER, IF

– Text and String Functions: CONCATENATE, LEN, REPLACE

– Advanced Text Functions: SUBSTITUTE, RIGHT, LEFT, MID

– Date and Time Functions: NOW, TODAY, TIME

– Lookup and Reference Functions: VLOOKUP, HLOOKUP, INDEX-MATCH

Part 6: Excel Macro and VBA Basics

– Introduction to Excel Macros and VBA

– Creating and Naming Excel VBA Macros

– Basics of Writing and Running Excel Macros

Introduction to SQL and Relational Databases

DDL Statements
DML Statements
DQL Statements

Working with Data

Filtering and Sorting Data

Joining Tables

Aggregating Data

Data Manipulation

Advanced Filtering and Sorting

Views and Indexes

Data Integrity and Constraints

Advanced Joins and Subqueries

Part One: Getting Started with pandas

Introduction to pandas Data Structures

Overview of pandas and its core data structures: Series and DataFrame.

Understanding the role of Index Objects in data labeling.

Essential Functionality

Reindexing: Changing row/column labels to align data.

Dropping entries from an axis.

Indexing, selection, and filtering data.

Arithmetic operations and data alignment.

Function application and mapping across data.

Summarizing and Computing Descriptive Statistics

Computing summary statistics like mean, median, and more.

Introducing concepts of correlation and covariance.

Finding unique values, value counts, and membership.

Handling Missing Data

Filtering out missing data.

Filling in missing data using appropriate strategies.

Hierarchical Indexing

Understanding hierarchical indexing for multiple levels of labels.

Reordering and sorting levels for data arrangement.

Generating summary statistics at different index levels.

Part Two: Data Loading, Storage, and File Formats

Reading and Writing Data

Reading and writing data in text format.

Working with text files in pieces for memory efficiency.

Dealing with delimited formats.

Reading and writing JSON data.

Extracting data from XML and HTML through web scraping.

Interacting with External Data Sources

Reading Microsoft Excel files.

Connecting to and querying databases.

Part Three: Data Wrangling: Clean, Transform, Merge, Reshape

Combining and Merging Data

Performing database-style DataFrame merges.

Merging DataFrames on index.

Concatenating data along an axis.

Combining data with overlap.

Reshaping and Pivoting

Reshaping data for analysis and visualization.

Utilizing hierarchical indexing for reshaping.

Pivoting data from “long” to “wide” format.

String Manipulation

String object methods for data manipulation.

Working with regular expressions.

Applying vectorized string functions in pandas.

Part Four: Plotting and Visualization

Introduction to matplotlib

Brief overview of matplotlib API.

Creating figures and subplots.

Customizing colors, markers, and line styles.

Plotting Functions in pandas

Creating various types of plots using pandas.

Line plots, bar plots, histograms, density plots, scatter plots.

Python Visualization Tool Ecosystem

Exploring other Python visualization libraries

Part Five: Data Aggregation and Group Operations

GroupBy Mechanics

Understanding the GroupBy operation.

Iterating over groups.

Selecting specific columns from grouped data.

Data Aggregation

Applying aggregation functions to grouped data.

Column-wise and multiple function application.

Returning aggregated data in different forms.

Group-wise Operations and Transformations

Applying split-apply-combine techniques.

Quantile and bucket analysis.

Examples of group-specific value filling, random sampling, permutation, weighted average, and correlation.

Pivot Tables and Cross-Tabulation

Creating pivot tables for data analysis.

Using cross-tabulations (crosstab) to analyze categorical data.

• Define Statistics and Its Significance.
• Discussing Types of Data: Categorical and Numerical
• Differentiating between Inferential and Descriptive Statistics
• Understanding Measures of Central Tendency: Mean, Median, Mode
• Exploring Measures of Dispersion: Variance and Standard Deviation
• Introduction to Probability Fundamentals, Rules, and Notation
• Explaining Probability Distribution – Discrete versus Continuous
• Examining Normal Distribution and Its Characteristics
• Highlighting the Central Limit Theorem and Its Relevance
• Analyzing Skewness and T-Distributions

Part One: Introduction to Power BI

Overview of Power BI

Uploading Data

Quick Insights

Introduction to Reports

Visual Interactions

Decorating and Saving Reports

Pinning Reports

Refreshing Data

Applying Filters

Part Two: Data Handling and Management

Understanding Data Refresh

Power BI Refresh Architecture

Power BI Desktop Introduction

Publishing to Power BI

Automatic Refresh Configuration

Connecting to Databases

Loading Data from Various Sources

Utilizing Query Editor

Table Management: Hiding and Removing

Managing Seasonality and Sorting

Part Three: Building Effective Reports

Loading Individual Tables

Implementing Measures

Calculated Columns

Enhancing Reports with Measures

Selecting Appropriate Visualizations

Standard vs. Custom Visuals

Exploring Custom Visualizations

Utilizing DAX in Data Models

Creating High-Density Reports