Course

📊 Advance Excel with Power Query more details...
Module 1: Basic Excel Features and Functionality part 1
  • Understanding the number format codes
  • Changing the font color with number format codes
  • Hide Number and Text based on criteria
  • GOTO SPEICAL Feature
  • Creating Name range and Name Manager
  • Hyperlink from a different sheet, workbook, cell, defined names and websites
  • Remove Duplicates
  • Data Validation: Data List, rules, modify error messages
  • Grouping, ungrouping and Subtotal in Excel
Module 2: Basic and Intermediate Excel Features and Functionality part 2
  • COUNT, COUNTA, COUNTBLANK
  • COUNTIF
  • COUNTIF with wildcard
  • Uses of COUNTIF with real-time example
  • Unique Entry using the COUNTIF function
  • Find duplicates using COUNTIF Function
  • Running Count
  • Example of COUNTIFS
  • Example of SUMIFS
  • Example of AVERAGEIFS
  • Example of DCOUNT
  • Example of COUNTA
  • Example of DSUM
  • Example of DAVERAGE
Module 3: Number Function & Formulas
  • COUNT, COUNTA, COUNTBLANK
  • COUNTIF
  • COUNTIF with wildcard
  • Uses of COUNTIF with real-time example
  • Unique Entry using the COUNTIF function
  • Find duplicates using COUNTIF Function
  • Running Count
  • Example of COUNTIFS
  • Example of SUMIFS
  • Example of AVERAGEIFS
  • Example of DCOUNT
  • Example of COUNTA
  • Example of DSUM
  • Example of DAVERAGE
Module 4: Logical and SUMPRODUCT Functions
  • Logical Functions: IF, Nested IF, AND, OR, NOT, XOR, IFERROR
  • SMALL and LARGE function
  • SUMPRODUCT Function
  • Use SUMPRODUCT for COUNT
  • Use SUMPRODUCT for SUM
  • Use SUMPRODUCT for COUNTIF and COUNTIFS
  • Use SUMPRODUCT for SUMIF and SUMIFS
  • Use SUMPRODUCT for AVERAGE and AVERAGEIFS
  • Use SUMPRODUCT for MAXIF and MAXIFS
Module 5: Text functions in Excel to solve complex text problems
  • Introduction of TEXT Functions (LEFT, RIGHT, MID, REPLACE, SUBSTITUTE, etc) available in Excel
  • Use text functions to Split the words
  • Use text functions to split characters and numbers
Module 6: Date & Time Functions in Excel, creating a dynamic calendar for whole months and years
  • Introduction of Date Functions available in excel.
  • Create and Design Calendar using Different Date Functions (WEEKDAY, WEEKNUM, EOMONTH, EDATE) in excel.
  • Calculate Age using DATEDIF Function
  • Calculate the date and time difference
  • count working days using NETWORKDAYS Function
Module 7: LOOKUP, VLOOKUP, HLOOKUP,XLOOKUP, Dynamic LOOKUP functions
  • Introduction of LOOKUP VLOOKUP and HLOOKUP
  • VLOOKUP with Exact Match
  • VLOOKUP with Approximate Match
  • Usage of VLOOKUP Functions using ROW and COLUMN functions
  • Example of VLOOKUP with Array
  • Example of HLOOPKUP with multiple Examples
  • Example of LOOKUP with multiple examples
Module 8: INDEX and OFFSET function, Uses of Array in Excel
  • Introduction of the INDEX Function
  • Introduction of OFFSET Function
  • Use of INDEX Function
  • Advance uses of Array Function using INDEX, OFFSET, MATCH, SMALL, LARGE, ROW, and COLUMN function
  • Use of ROW and ROWS function using INDEX and OFFSET function
Module 9: INDIRECT and ADDRESS Function
  • Introduction of INDIRECT Function
  • Introduction of ADDRESS Function
  • Uses of INDIRECT Function
  • Uses of ADDRESS Function
Module 10: Data Analysis using Power Query and Power Pivot in Microsoft Excel
  • Creating a Pivot table and Pivot chart in Excel
  • Adding Slicer and Timeline to analyze in Excel
  • Using the power pivot to do the Data Analysis
  • Using Power Query to Source the data from Different Sources
Module 11: MIS Reports and Dashboards
  • Introduction of Dashboards and MIS Reports
  • Adding and Using User Form control in Excel
  • Recording Macro and run it through a command button
  • Creating Dynamic Formula based on User Form control
  • Creating Dynamic Charts using Formula
  • Creating 2D charts in Excel
  • Importance of charts in Dashboards and MIS Reports
Module 12: Creating Dynamic Dashboard on Live data Project
  • Project work on MIS Dashboard
Module 13: Excel 365 Function and usage
  • Data Import from multiple sources
  • XLOOKUP
  • FILTER
  • ColumnIndex
📈 Power BI
Introduction to Power BI
  • Power BI Desktop
  • Data Loading
DAX & Visualization
  • DAX Basics
  • Charts & Dashboards
🗄 SQL Server
SQL Basics
  • SELECT, WHERE
  • JOINS
Advanced SQL
  • Stored Procedures
  • Indexes
🐍 Python
ADF Basics
  • Linked Services
  • Datasets
Pipelines & Triggers
  • Pipeline Creation
  • Scheduling
☁ Azure Data Factory
Python Basics
  • Loops
  • Variables
Data Handling
  • Pandas
  • NumPy
Subscribe to: Comments (Atom)