Reporting And Analytics With Excel+ Excel Macro+Sql+Power Bi

Combo pack of Excel, Excel Macro and Excel VBA, SQL Server and Power BI with complete Practical Knowledge

What you'll learn

Advanced Reporting and Analytics with Advanced Function and Formula in Excel

Automation with VBA Macro Development with Different Projects

Database Management and Query Optimization with SQL Server Database

Power BI Report and Dashboard Designing and Data Modeling with and Use of (Data Analytics Expression)

Requirements

No specific requirement for this course

Description

Are you ready to take your Microsoft Excel skills to the next level? Do you want to become proficient in Management Information System (MIS) reporting and harness the power of Visual Basic for Applications (VBA) and SQL Server for automation and Data Management? This comprehensive course is designed to make you an expert in advanced Excel techniques, MIS reporting, and VBA macro development, SQL Server Database, Power BI Data Modeling, Report and Dashboard Design, Power Query and Dax (Data Analysis Expressions)**Advanced Excel Skills:** This course will empower you with advanced Excel skills, including data analysis, complex formulas, pivot tables, and data visualization. You'll learn how to tackle complex tasks with ease. - **MIS Reporting Excellence:** We'll delve into the world of MIS reporting, teaching you how to create, manage, and present data effectively for informed decision-making. You'll gain the knowledge and practical skills needed for efficient data reporting and analysis. - **VBA Macro Development:** Take control of Excel with VBA. Learn how to automate repetitive tasks, create custom functions, and build interactive user interfaces. VBA mastery will save you time and boost your productivity. **Hands-On Learning:** We believe in learning by doing. You'll have ample opportunities for hands-on practice with Excel and VBA. By the end of the course, you'll have a portfolio of work that showcases your skills. - **Expert Instruction:** Our experienced instructors are passionate about Excel, MIS, and VBA. They will guide you through the course, offering insights, tips, and best practices.

Overview

Section 1: Introduction

Lecture 1 Course Introduction

Lecture 2 Excel and Advanced Excel Introduction

Lecture 3 Excel Macro Introduction and Basic Macro writing

Lecture 4 SQL Introduction and SQL Query writing

Lecture 5 Power BI Introduction, Import Data and Visualization

Section 2: Excel Most Important Functionality

Lecture 6 Excel Cell Reference

Lecture 7 Excel Shortcuts

Lecture 8 Format Painter

Lecture 9 Advanced Paste Special

Section 3: Data Extraction

Lecture 10 VLOOKUP Overview

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

Lecture 12 Basic of MATCH Function

Lecture 13 Extract Multiple Columns Data Using VLOOKUP and MATCH

Lecture 14 Extract Data from Multiple Datasets Using VLOOKUP

Lecture 15 HLOOKUP Overview

Lecture 16 Extract Data from Multiple Rows and Columns Using HLOOKUP and MATCH

Lecture 17 Extract Data from Various Datasets Using HLOOKUP and MATCH

Lecture 18 INDEX Overview

Lecture 19 Extract Data for Single Column Using INDEX and Single MATCH

Lecture 20 Extract Data for Multiple Columns Using INDEX and Multiple MATCH

Lecture 21 Extensive Use of INDEX Function with Various Functions

Section 4: Data Cleaning

Lecture 22 Introduction and Importance of Data Cleaning and Transformation

Lecture 23 Number of Character Count in any Text with LEN Function

Lecture 24 Find Position of Character with Find and Search Functions

Lecture 25 Extract Data From Left Side Using LEFT

Lecture 26 Extract Data From Right Side Using RIGHT

Lecture 27 Extract Middle Value From Data Using MID

Lecture 28 Replace Single Character Using SUBSTITUTE Function

Lecture 29 Replace Complete Word Using SUBSTITUTE Function

Lecture 30 Specific Character Count Using SUBSTITUTE

Lecture 31 Dynamic SUBSTITUTE Function with Live Scenario

Lecture 32 Replace Existing Text with New Text Using REPLACE Function

Lecture 33 Extract Invoice Number From Data Using REPLACE

Lecture 34 Extract Last Word From Data

Section 5: Dynamic Range with OFFSET Function

Lecture 35 Basic of OFFSET Function

Lecture 36 Make a Dynamic Range with OFFSET Function

Lecture 37 Create a Dynamic Chart with OFFSET Function

Lecture 38 Use of Dynamic Range in Any Function

Lecture 39 Create a Dynamic Pivot Table

Section 6: Data Aggregation

Lecture 40 Basic of SUMPRODUCT Function

Lecture 41 Count Data on Single Condition Using COUNTIF and SUMPRODUCT

Lecture 42 Count Data on Multiple Conditions Using COUNTIFS and SUMPRODUCT

Lecture 43 Aggregate Data on Single Condition Using SUMIF and SUMPRODUCT

Lecture 44 Aggregate Data on Multiple Conditions Using SUMIFS and SUMPRODUCT

Lecture 45 Average on Single and Multiple Conditions with AVERAGEIF and AVERAGEIFS

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

Lecture 47 Advance Use of SUMPRODUCT Function

Section 7: Time Series Analysis

Lecture 48 Basics of Date Functions

Lecture 49 Advance Date Function to Calculate Difference in Days

Lecture 50 Calculate Age and Birthday

Lecture 51 Date Functions with Live Scenario

Lecture 52 Calculate Aging of Invoice Payment

Section 8: Logical Function

Lecture 53 Returns Grade and Tax Using IF

Lecture 54 AND to Check Conditions

Lecture 55 OR to Check Conditions

Lecture 56 NOT to Reverse Condition

Section 9: Conditional Formatting

Lecture 57 Introduction to Conditional Formatting

Lecture 58 Highlight Number on Condition

Lecture 59 Highlight Text on Condition

Lecture 60 Highlight TopN Values

Lecture 61 Highlight with Data Bars, Color Scale, Icon Set

Lecture 62 Highlight Duplicates with Single or Multiple Conditions

Lecture 63 Highlight values on Drop Downlist

Lecture 64 Highlight Specific Date, Month, Year

Lecture 65 Highlight Minimum and Maximum Value

Section 10: Summarize Data Using Pivot Table

Lecture 66 Introduction of PIVOT TABLE

Lecture 67 Basic Feature of PIVOT TABLE

Lecture 68 More Features of PIVOT TABLE

Lecture 69 SLICER to Filter Data in PIVOT TABLE and PIVOT CHART

Lecture 70 Group and Ungroup in PIVOT TABLE

Lecture 71 Represent Data in Different % Calculation

Lecture 72 CALCULATED FIELD with Live Scenario

Section 11: Filter Relevant Data Using Filter And Advance Filter

Lecture 73 Filter with Text Value

Lecture 74 Filter with Number Value

Lecture 75 Custom Filter

Lecture 76 Advance Filter

Section 12: Represent Data Using Chart

Lecture 77 Introduction to Chart

Lecture 78 Dynamic Chart

Lecture 79 Speedometer Chart

Section 13: MIS Report Design

Lecture 80 Create Dynamic Sales Report

Lecture 81 Create Performance Report

Section 14: DASHBOARD Design

Lecture 82 Create a Dashboard on Orders Data

Section 15: Introduction and Basic Excel Macro

Lecture 83 Introduction of Excel Macro

Lecture 84 Cell Object

Lecture 85 Range Object

Lecture 86 Worksheet Object

Lecture 87 Workbook Object

Section 16: Advanced Excel Macro Development with Loops and Operators

Lecture 88 Advanced Macro Development with Multiple Live Scenario

Section 17: Connection String to Import and Export Data from Database

Lecture 89 Import Data from Access Database

Lecture 90 Export Data in Access Database

Lecture 91 Import Data from SQL Database

Lecture 92 Export Data in SQL Database

Section 18: Connection String to Connect Power Point Using VBA

Lecture 93 Connect and Open Power Point

Lecture 94 Export Data into Power Point Using VBA

Lecture 95 Create Chart and Export in Power Point Slide

Section 19: Web Scraping

Lecture 96 Scrap Data from Website

Section 20: User Form Design in VBA

Lecture 97 Data Entry and Search Form Design

Section 21: SQL Server Introduction

Lecture 98 Introduction of SQL Server

Lecture 99 SQL Server Interface

Lecture 100 Server and Database

Lecture 101 SQL Table, Record, Field

Section 22: SQL Table

Lecture 102 Create Table

Lecture 103 Create Table without Create Statement

Lecture 104 Delete Table

Lecture 105 Alter Table

Section 23: Data Insert, Import and Export

Lecture 106 Insert Data with Graphical User Interface

Lecture 107 Insert Data into Table

Lecture 108 Bulk Insert Data into Table

Lecture 109 Import Data into Table

Lecture 110 Export Data from Table

Section 24: Delete and Truncate

Lecture 111 Delete Data on Condition

Lecture 112 Delete Entire Data from Table

Section 25: Update Data

Lecture 113 Update Entire Field Data in Table

Lecture 114 Update Data on Condition

Section 26: Select Statement to View Data (Where Clause)

Lecture 115 View Data

Lecture 116 View Distinct Data

Lecture 117 View Filtered Data

Section 27: Filter Data with Where Clause (And, Or, IN, Between, LIKE, TOP Operators)

Lecture 118 View Filtered Data with AND Operator

Lecture 119 View Filtered Data with OR Operator

Lecture 120 View Filtered Data with IN Operator

Lecture 121 View Filtered Data with BETWEEN Operator

Lecture 122 View Filtered Data with LIKE Operator

Lecture 123 View Top and Bottom Records with TOP Clause

Section 28: SQL Order By

Lecture 124 Sort Data in Ascending or Descending Orders

Lecture 125 Sort Data In Ascending Orders

Lecture 126 Sort Data in Descending Orders

Section 29: Group By and Having

Lecture 127 Extract Distinct Data

Lecture 128 Aggregate Data with Group By

Lecture 129 Apply Condition with Having Clause

Section 30: SQL Sub Query

Lecture 130 Extract Data of Max Value

Lecture 131 Extract Data of Min Value

Lecture 132 Extract Data more than Average Value

Lecture 133 Extract 3rd Highest Sales

Section 31: SQL Joins

Lecture 134 Introduction of Join

Lecture 135 Inner Join

Lecture 136 Left Join

Lecture 137 Right Join

Lecture 138 Full Join

Lecture 139 Cross Join

Lecture 140 Self Join

Section 32: SQL Union

Lecture 141 Union

Lecture 142 Union ALL

Lecture 143 Intersect

Lecture 144 Except

Section 33: SQL Case Statement

Lecture 145 Create a Conditional Field

Section 34: Common Table Expression (CTE)

Lecture 146 Row_Number

Lecture 147 Rank Function

Lecture 148 Dense_Rank Function

Lecture 149 Extract Product Name of 3rd Largest Sales

Lecture 150 Extract Each Category 2Max Value Data

Section 35: Basic Concept of Power BI

Lecture 151 Introduction of Power BI

This course is designed for them who is looking career in Analyst, BI Developer, MIS Reporting using Excel and Power BI