Reporting, Analytics With Excel+Macro+SQL+Power BI (42Hours)

Course Curriculum

Chapter 1: Introduction

Lecture 1: Course Overview

Lecture 2: What is Analyst

Lecture 3: Excel and Advanced Excel Introduction

Lecture 4: Excel Macro Introduction and Basic Macro writing

Lecture 5: SQL Introduction and SQL Query writing

Lecture 6: Power BI Introduction, Import Data and Visualization

Chapter 2: Excel Most Important Functionality

Lecture 1: Excel Cell Reference

Lecture 2: Excel Shortcuts

Lecture 3: Format Painter

Lecture 4: Advanced Paste Special

Chapter 3: Data Extraction

Lecture 1: VLOOKUP Overview

Lecture 2: Extract Product Name of Highest Shipping Cost and Highest Loss with VLOOKUP

Lecture 3: Basic of MATCH Function

Lecture 4: Extract Multiple Columns Data Using VLOOKUP and MATCH

Lecture 5: Extract Data from Multiple Datasets Using VLOOKUP

Lecture 6: HLOOKUP Overview

Lecture 7: Extract Data from Multiple Rows and Columns Using HLOOKUP and MATCH

Lecture 8: Extract Data from Various Datasets Using HLOOKUP and MATCH

Lecture 9: INDEX Overview

Lecture 10: Extract Data for Single Column Using INDEX and Single MATCH

Lecture 11: Extract Data for Multiple Columns Using INDEX and Multiple MATCH

Lecture 12: Extensive Use of INDEX Function with Various Functions

Chapter 4: Data Cleaning

Lecture 1: Introduction and Importance of Data Cleaning and Transformation

Lecture 2: Number of Character Count in any Text with LEN Function

Lecture 3: Find Position of Character with Find and Search Functions

Lecture 4: Extract Data From Left Side Using LEFT

Lecture 5: Extract Data From Right Side Using RIGHT

Lecture 6: Extract Middle Value From Data Using MID

Lecture 7: Replace Single Character Using SUBSTITUTE Function

Lecture 8: Replace Complete Word Using SUBSTITUTE Function

Lecture 9: Specific Character Count Using SUBSTITUTE

Lecture 10: Dynamic SUBSTITUTE Function with Live Scenario

Lecture 11: Replace Existing Text with New Text Using REPLACE Function

Lecture 12: Extract Invoice Number From Data Using REPLACE

Lecture 13: Extract Last Word From Data

Chapter 5: Dynamic Range with OFFSET Function

Lecture 1: Basic of OFFSET Function

Lecture 2: Make a Dynamic Range with OFFSET Function

Lecture 3: Create a Dynamic Chart with OFFSET Function

Lecture 4: Use of Dynamic Range in Any Function

Lecture 5: Create a Dynamic Pivot Table

Chapter 6: Data Aggregation

Lecture 1: Basic of SUMPRODUCT Function

Lecture 2: Count Data on Single Condition Using COUNTIF and SUMPRODUCT

Lecture 3: Count Data on Multiple Conditions Using COUNTIFS and SUMPRODUCT

Lecture 4: Aggregate Data on Single Condition Using SUMIF and SUMPRODUCT

Lecture 5: Aggregate Data on Multiple Conditions Using SUMIFS and SUMPRODUCT

Lecture 6: Average on Single and Multiple Conditions with AVERAGEIF and AVERAGEIFS

Lecture 7: Advance use of Countifs, Averageifs and Sumifs with IF Functions

Lecture 8: Advance Use of SUMPRODUCT Function

Chapter 7: Time Series Analysis

Lecture 1: Basics of Date Functions

Lecture 2: Advance Date Function to Calculate Difference in Days

Lecture 3: Calculate Age and Birthday

Lecture 4: Date Functions with Live Scenario

Lecture 5: Calculate Aging of Invoice Payment

Chapter 8: Logical Function

Lecture 1: Returns Grade and Tax Using IF

Lecture 2: AND to Check Conditions

Lecture 3: OR to Check Conditions

Lecture 4: NOT to Reverse Condition

Chapter 9: Conditional Formatting

Lecture 1: Introduction to Conditional Formatting

Lecture 2: Highlight Number on Condition

Lecture 3: Highlight Text on Condition

Lecture 4: Highlight TopN Values

Lecture 5: Highlight with Data Bars, Color Scale, Icon Set

Lecture 6: Highlight Duplicates with Single or Multiple Conditions

Lecture 7: Highlight values on Drop Downlist

Lecture 8: Highlight Specific Date, Month, Year

Lecture 9: Highlight Minimum and Maximum Value

Chapter 10: Summarize Data Using Pivot Table

Lecture 1: Introduction of PIVOT TABLE

Lecture 2: Basic Feature of PIVOT TABLE

Lecture 3: More Features of PIVOT TABLE

Lecture 4: SLICER to Filter Data in PIVOT TABLE and PIVOT CHART

Lecture 5: Group and Ungroup in PIVOT TABLE

Lecture 6: Represent Data in Different % Calculation

Lecture 7: CALCULATED FIELD with Live Scenario

Chapter 11: Filter Relevant Data Using Filter And Advance Filter

Lecture 1: Filter with Text Value

Lecture 2: Filter with Number Value

Lecture 3: Custom Filter

Lecture 4: Advance Filter

Chapter 12: Represent Data Using Chart

Lecture 1: Introduction to Chart

Lecture 2: Dynamic Chart

Lecture 3: Speedometer Chart

Chapter 13: MIS Report Design

Lecture 1: Create Dynamic Sales Report

Lecture 2: Create Performance Report

Chapter 14: DASHBOARD Design

Lecture 1: Create a Dashboard on Orders Data

Chapter 15: Introduction and Basic Excel Macro

Lecture 1: Introduction of Excel Macro

Lecture 2: Cell Object