Mastering In Advanced Excel, Reporting And Vba Macro

Becoming an Excel Expert: Advanced MIS Reporting and VBA Macro Development

What you'll learn

Advanced Functions and Formulas

Advanced Topic of Advance Excel (Pivot, Conditional Formatting, Chart, Advance Filter etc)

Report and Dashboard Design

VBA Macro Development

Requirements

Basic Excel require to learn advance excel and reporting

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) for automation? This comprehensive course is designed to make you an expert in advanced Excel techniques, MIS reporting, and VBA macro development.**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 Trainer Introduction

Lecture 2 Course Overview

Lecture 3 Excel Overview

Lecture 4 Limitation of Excel

Lecture 5 What is Advanced Excel

Lecture 6 Cell Referencing

Section 2: Data Extraction Using Lookup Function

Lecture 7 VLOOKUP Overview

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

Lecture 9 Basic of MATCH Function

Lecture 10 Extract Multiple Columns Data Using VLOOKUP and MATCH

Lecture 11 Extract Data from Multiple Datasets Using VLOOKUP

Lecture 12 HLOOKUP Overview

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

Lecture 14 Extract Data from Various Datasets Using HLOOKUP and MATCH

Lecture 15 INDEX Overview

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

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

Lecture 18 Extensive Use of INDEX Function with Various Functions

Section 3: Data Aggregation Using Conditional Function

Lecture 19 Basic of SUMPRODUCT Function

Lecture 20 Count Data on Single Condition Using COUNTIF and SUMPRODUCT

Lecture 21 Count Data on Multiple Conditions Using COUNTIFS and SUMPRODUCT

Lecture 22 Aggregate Data on Single Condition Using SUMIF and SUMPRODUCT

Lecture 23 Aggregate Data on Multiple Conditions Using SUMIFS and SUMPRODUCT

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

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

Lecture 26 Advance Use of SUMPRODUCT Function

Section 4: Logical Output Using Logical Function

Lecture 27 Returns Grade and Tax Using IF

Lecture 28 AND to Check Conditions

Lecture 29 OR to Check Conditions

Lecture 30 NOT to Reverse Condition

Section 5: Data Cleaning And Extraction

Lecture 31 Introduction and Importance of Data Cleaning and Transformation

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

Lecture 33 Find Position of Character with Find and Search Functions

Lecture 34 Extract Data From Left Side Using LEFT

Lecture 35 Extract Data From Right Side Using RIGHT

Lecture 36 Extract Middle Value From Data Using MID

Lecture 37 Replace Single Character Using SUBSTITUTE Function

Lecture 38 Replace Complete Word Using SUBSTITUTE Function

Lecture 39 Specific Character Count Using SUBSTITUTE

Lecture 40 Dynamic SUBSTITUTE Function with Live Scenario

Lecture 41 Replace Existing Text with New Text Using REPLACE Function

Lecture 42 Extract Invoice Number From Data Using REPLACE

Lecture 43 Extract Last Word From Data

Section 6: Time Series Analysis

Lecture 44 Basics of Date Functions

Lecture 45 Advance Date Function to Calculate Difference in Days

Lecture 46 Calculate Age and Birthday

Lecture 47 Date Functions with Live Scenario

Lecture 48 Calculate Aging of Invoice Payment

Section 7: Dynamic Range with OFFSET Function

Lecture 49 Basic of OFFSET Function

Lecture 50 Make a Dynamic Range with OFFSET Function

Lecture 51 Create a Dynamic Chart with OFFSET Function

Lecture 52 Use of Dynamic Range in Any Function

Lecture 53 Create a Dynamic Pivot Table

Section 8: Connection Using Power Query

Lecture 54 Connect and Import Data from Ms Access Data

Lecture 55 Connect, Transform and Extract Data from Ms Access to Excel

Lecture 56 Connect and Transform Text Data

Section 9: Highlight Data On Condition Using 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: Prediction Using What If Analysis

Lecture 80 Adjust Value Using GOAL SEEK

Lecture 81 Predict Output Using DATA TABLE

Lecture 82 Create Scenario Manager

Lecture 83 Predict Value to Achieve Target

Section 14: MIS Report Design

Lecture 84 Create Dynamic Sales Report

Lecture 85 Create Performance Report

Section 15: DASHBOARD Design

Lecture 86 Create a Dashboard on Orders Data

Section 16: Macro Development

Lecture 87 Basic Macros Development

Lecture 88 Advanced Macros Development

Lecture 89 Form Design

The person, who wants to make career in Analytics and MIS Reporting