Mastering In Advanced Excel, Reporting And Vba Macro

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

Homepage

https://www.udemy.com/course/mastering-in-advanced-excel-reporting-and-vba-macro/