Introduction to Advanced MS Excel:-

• Circular References error, Rectify Circular Reference, Formula Auditing, Structured Reference

Solve The Real World Excel Problems with Functions:

• Mathematical Functions:-

Abs, Sum, Sumif, Sumifs,Count, Counta, Countif, Countifs,Countblank, Average, Averagea, Averageif, Averageifs,Subtotal, Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct

• If Formulas: 
• If, If with OR, If with AND, If with AND &OR, If with Countif, If With Sumif.
• If with Trim, If with Concatenation, If with Left, Mid, Right.
• Nested If (For Multiple Conditions), If condition used more than one time in the same formula.
• Nested if with left, Mid, Right
• More If Formulas: 
• Nested if with Multiple Text Functions, TAX Calculation, Other Critical Lookup Formulas
• Nested if with VLook-up And Hlookup.
• Introduction to Name Manager: Name Ranges and Apply the Name Ranges on the combination of Cells.
• Date & Time Function:-

Date, Day, Month, Year, Edate, Eomonth, Networkdays, Workday, Weeknum, Weekday, Hour, Minute, Second, Now, Today, Time

• Statistical Function & Other Functions :-

Information Function: IsOdd, IsEven, IsError, IsNumber, IsText, IsBlank, IsRef, ISNA
Some Other Data Base Functions :Dsum,DCount, DAverage, DMax, Dmin

• Lookup & Reference Functions:-
• Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset, Transpose.
• Vlookup, Hlookup with Name Range And Match .
• Find Data In Opposition by Index Match.
• Vlookup, Hlookup with Multiple Functions, Vlookup, Hlookup with arrays.
• Array With Multiple Formulas:
• Array Formulas work and Use of the Array Formulas, Acceptance of Array Formulas in today’s scenario.
• Array With Sumifs, Countifs, Sumproduct , Large Functions.
• Text Functions & Data Validation :-

Char, Clean, Code, Concatenate, Find, Search, Substitute, Replace, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Value, Large, Small, Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split.

• Pivot Table and Pivot Charts with Slicer:
• Do the Multiple Field Setting in Pivot Table.
• Pivot form Multiple Source of Data, Data Ranges, Name Range.
• Group Pivot Table Items, Multi-level Pivot Table, Calculated Field/Item
• Perform the % calculation on the basis of multiple fields, Using Slicer.
• Advanced Chart Technique:-

How To Make Dynamic Charts, Bar Charts, Pie Charts, Scatter Chart, Line Chart, Column Chart, Gantt Chart.

• Advanced Dashboard:-
• Preparation of Advanced Level of Charts: Gnatt Chart, Bubble Chart, Waterfall Chart, Use of Data Validation in Charting And Handle With Controls (Combo Box, Spin Box, List Box and Option Box.)
• Offset function, Combination Of Charts, Time Series Analysis, Visualizing Data.
• Use of Formulas Like Offset, Match, Sumif, Sumifs and many more to prepare the Dashboards.
• Data Collection Method With Data Quality:-
• Analysis:-
• Single/Multidimensional Analysis, Like Three Dimensional (3D) Tables, Sensitive Analysis Like Data Table,
• Solve Real World Problem: Goal Seek, One-Variable Data Table, Two-Variable Data Table, Scenario Manager.
• Report Development
• Live Project Session

• What is VBA & How VBA Works with Excel?
• Quick Review of Macros
• Introducing the Visual Basic Editor
• Uses of record Macros
• Understanding and creating modules
• Programming Concepts
• VBA Sub and Function Procedures
• How to create a message box
• Write a Program to update and retrieve information using Input Box
• Understanding and using Select Case statement
• How do I define a variables and Rules for defining a Variables Name and Type
• Creating And using Variables
• Working with range Objects
• How to save and Protect Modules
• Decision Makers
• If……Then……Else
• If……Then……ElseIf……If
• Other Kinds of Loops
• Working with Do While u. Loop Procedure
• Do…… Until Loop and Do…… Loop Until
• Do……While loop and Do…… Loop While
• For each…… Next
• For……Next
• Workbook Objects Create or Add Single and Multiple Workbooks
• Workbook Save and Save AS
• Open Single and Multiple Workbooks
• Close Specify and Multiple Workbooks
• Activate From one workbook to another Workbook
• Get Workbook Name and Paths
• Hide and Unhide for Single and Multiple Workbooks
• Protect and Unprotect Worksheets
• Worksheet Objects insert a single and Multiple Worksheets
• Delete Specific and multiple worksheets
• Get Count of Worksheets
• Select a Specific and all Worksheets
• Get All Worksheets Name
• Hide and Unhide For Single and Multiple Worksheets
• Sort and Move worksheets
• Calculate entire worksheet
• Using VBA and worksheet Function
• Cell objects insert Single and multiple Row , Column and Cells
• Delete Single and Multiple row, Column and Cells
• Get Range or Address of Cell and Selection
• Navigate from one Cell to another Cell
• Select specific Range, Cell, Rows and Column
• Types of Selection and Offset method
• Insert Function In cell
• Reading and Writing Arrays
• Defining Arrays
• Form Controls and User Forms (Create and Design an user Form)
• Working With User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.
• How To Create Dynamic Dashboard On User Form With Different Controls
• VBA Programming Functions
• Create a Sum Functions
• Create Multiply Function
• Create Count Function
• Extract Text & Number
• Proper Function
• Vlookup Function
• Public or Private function
• Excel VBA Power Programming for VBA Macros
• Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening  & saving Files
• How to Analyze Data On multi Worksheets And Build Summary sheets
• How to Access the Windows File and Folder System to Open and Close Workbooks
• How to protect your code Against Errors
• How to create Basic Report Generation Tools Using Excel VbA, Microsoft Word and PowerPoint
• Connection between Excel VBA & other platforms
• How To Establish Connection Between Excel Vba And Outlooks Through Vba
• Effective Error Handling
• Automation Development Reports & Live Projects

MS Access is a Microsoft Relational Database Management System (RDBMS) Topics Covered as follows:
MS - Access:-

• Understanding Databases
• Starting and Opening an Existing Database
• Moving Around in Access
• Understanding Datasheet View & Design View
• Using the Mouse Pointer to Navigate
• Using the Keyboard to Navigate
• Creating Tables
• Creating a Database
• Creating a Table Using the Wizard
• Creating and Modifying a Table
• Adding Fields to Tables
• Adding and Editing Records
• Printing Tables
• Moving and Deleting Fields
• Deleting Records
• Working with Tables
• Formatting a Table
• Modifying Field Properties
• Sorting Records in a Table
• Finding Records in a Table
• Using Filters with a Table
• Establishing Relationships Between Tables
• Creating Subdatasheets
• Importing Records From an External Source
• Designing a Form
• Creating a Form Using Auto Form
• Creating a Form Using the Form Wizard
• Adding Controls to a Form
• Modifying Control Properties
• Resizing and Moving Controls
• Entering Records into a Form
• Designing a Report
• Creating a Report Using Auto Report
• Creating a Report Using Report Wizard
• Adding a Control to a Report
• Formatting a Report
• Resizing and Moving Controls
• Creating Calculated Controls
• Creating and Using Queries
• Creating and Running a Query
• Specifying Criteria in a Query
• Using Comparison Operators
• Creating a Calculated Field
• Creating a Multiple-Table Query
• Automating Tasks
• Creating an Auto Keys Macro
• Using Controls to Run a Macro
• Assigning a Macro to an Event
• Assigning a Macro to a Condition
• Testing and Debugging a Macro

SQL:-

• SQL Overview
• Relational database concepts, specific products
• SQL syntax rules
• Data definition, data manipulation, and data control statements
• Getting acquainted with the course database and editor
• SQL SELECT statements
• Clauses
• The SELECT clause: columns and aliases, where expressions, order by expressions how null Values behave
• SQL Functions and Expressions
• Eliminating duplicates with DISTINCT arithmetic expressions
• Replacing null values
• Numeric operations, including rounding
• Date and time functions
• Nested table expressions
• Case logic
• Other expressions in specific DBMS Products
• SQL Updating
• The INSERT, UPDATE and DELETE statements
• Column constraints and defaults
• Referential integrity constraints
• SQL Joins
• Inner joins with original and SQL 92 syntax
• Table aliases
• Left, right and full outer joins, Inner joins
• Self-joins
• SQL Sub Queries and Unions
• Intersection with IN, and, Between
• Sub queries
• Difference with IS NULL and IS NOT NULL sub queries
• The purpose and usage of UNION and UNIONALL
• SQL Summarization
• The column functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER
• The GROUP BY and HAVING clauses Grouping in a combination with joining

Module 4.1 – Power BI Desktop, Custom Visuals,
Introduction to Power BI:- Power BI Component, Types of Reports in Real-time Usage, Power BI Tools and Implementation Plan Power BI Licensing and Excel Analytics.
Power BI Desktop Tool:-

• Report Visuals, Fields, Pages and Filters
• Data and Relationship Option, PBI Canvas
• Get Data from DAT Files, Excel Files, Access Files
• PBIX and PBIT Files  And Re-Using Reports
• Data Import Options Designing Simple / Basics Reports in PBI
• Visual Interactions in Power BI - Options Spotlight Options with Visuals, Real-time Use
• Slicer Visual in Power BI and Data Filters

Hierarchies & Filters:-

• Creating Hierarchies. Drilldown, Drill Up Reports
• Filters : Types and Usage in Real-time, Conditional Filters, Visual Filters, Page Filters, Report Filters
• Drill-thru Filters with Hierarchy Levels TOP N Filters – Usage Filtering at Category Level, Import and Direct Query with Power BI.

Power Bi Visuals:-

• Table Visuals & Properties, Data Bar and Data Scaling Options
• Divergent Colors and Data Labeling Matrix : Sub Totals, Grand Totals
• Slicer Visual - Properties, Alignment Single Select and All Options
• Chart Reports - Common Properties Axis, Legend Types- Stacked Bar, Column, Line charts Clustered Bar, Column, Line Chart

Power Bi Visuals with Different Types:-

• Tree Map, Funnel and Gauge Reports, Map Reports
• Single Row Card and Multi Row Cards
• Callout Values in KPI Reports and Use, Indicator, Trend and Target Goals in KPIs
• Using Buttons, Images in Power BI Canvas
• Bookmarks in Power BI Desktop – Usage Using Bookmarks for Visual Filters

Module 4.2 – Data Modeling with Power Query
Power Query Basic Operations:-

• Power Query Usage & Operation Types, QUERY Concept, Properties, Validations
• Power Query - Data Mash Up Operations
• Basic Data Types, Literals and Values, Expressions
• INVOKE Functions & Execution Results
• Power BI Canvas: Edits, Applied Steps, Frowns, Query Header Row Formatting

Power Query Usage:-

• Power Query Transformations Categories
• Query Combine & Merge Transformations, Join Options In Merge Transformation
• Truncate, Replace, Split, Reduce Rows, Manage Columns, Hide / Show Columns Grouping, Aggregations, Column Formats
• Transpose, Reverse Rows Transformations
• Power Query - Row Count And Replace, Data Type Detection - Scenarios, Use
• Data Type Conversions And Value Replace Fill Up And Fill Down,Pivot And Unpivot Transformations
• Move, Filter And Converttolist() Split, Format, Merge, Extract, Parse, Date,Time

Module 4.3 – Data Modeling with DAX
DAX Functions: -

• DAX as library of Functions, Types, Variables, Operators Dax Formula With Excel, Limitations
• DAX Architecture and Entity Sets
• Rules OF DAX, Working Options, Syntax, Functions
• ROW Context and Filter Context, DAX Structures and Syntax Options
• Creating and Measuring with DAX Creating and Using Columns with DAX

Advance DAX Functions:- 

• Data Modeling Options in DAX, Detecting & Adding Relations for DAX
• Power BI DAX Functions - Types, Usage, Cheat Sheet
• Power BI Reports - DAX Functionalities Calculated Columns, Aggregated Measures
• Quick Measures in DAX - Auto validations, DAX Performance Date and Time & Text Functions, Logical & Mathematical Functions
• Data Modeling with DAX. Creating Roles
• SELECTEDVALUE, FORMAT Functions RELATED, COUNTROWS CALCULATE, SUM, ALL
• Report Development
• Live Project Session