Best Data Analytics training in Chennai

ADVANCED EXCEL

MODULE 1: EXCEL INTRODUCTION & SETTING

• Excel menu button

• Excel settings

• Shortcut Keys

• Excel Tricks

MODULE 2: CELL REFERENCING & NUMBER FORMATTING

• Absolute, Relative, Mixed Reference

• Format as Currency, Comma Separator

• Format as Date

• Custom Format – Currency, Date & Other

MODULE 3: NAMED RANGES, TABLES & STYLES

• Naming Cells and Ranges

• Using named ranges in formulas

• Creating tables in excel

• Applying new style to tables

• Cell styles for titles & headings

MODULE 4: PROTECTION

• Cell Protection

• Worksheet Protection

• Workbook Protection

MODULE 5: CONDITIONAL FORMATTING

• Highlighting the cells

• Includes icon sets, Data Bar

• Creating new rule

• Appling formula in new rule

• Highlight Cells – Single, Multiple Conditions

• Condition based on selection Referencing

MODULE 6: PIVOT TABLES AND PIVOT CHARTS

• Creating Pivot Table

• Filter Date Values as Month, Quarter, Years

• Classic pivot table

• Recommended pivot table

• Report Filter on Pages.

• Pivot Chart with table

• Include Slicer & Creating Dashboard

MODULE 7: ADVANCED CHARTING

• Creating Multiple Chart Type

• Primary, Secondary Chart, Sparkline charts

• Smart arts & Illustration

MODULE 8: WORKING WITH DATA

• Sorting – Custom Sort, Multilevel

• Subtotals – Formula, Function

• Filtering – Advanced Filter

MODULE 9: WORKING WITH EXTERNAL DATA

• Grouping

• Import data from web

• Import from other sources like text file, access, Power Query

MODULE 10: DATA TOOLS & FORECASTING

• Setting Criteria - Text, Values and Dates

• Drop-Down List – Dependent

• Consolidation

• Remove duplicates

• What IF Analysis with Scenario, Goal Seek, data table

• Forecast Sheet Solver

MODULE 11: LOGICAL FUNCTIONS

• IF, Nested IF, AND, OR

• IF Condition with Text Contains

• Compound IF Function

MODULE 12: LOOKUP FUNCTIONS

• VLOOKUP, HLOOKUP

• MATCH, INDEX, INDIRECT

MODULE 13: STATISTICAL FUNCTIONS

• COUNTIF, SUMIF

• COUNTIFS, SUMIFS

• COUNTA, COUNT, COUNTBLANK

MODULE 14: EXCEL ADVANCED FORMULA’S

• LEFT, RIGHT, MID, UPPER, PROPER, LOWER

• EOMONTH, EDATE, NETWORKDAYS, WORKDAY

• DATE, TIME, TODAY, NOW, TEXT, SUBSTITUTE,

• SEARCH, FIND, LEN, TRIM, SUBTOTAL, AVERAGE

• SUM, MIN, MAX, PMT, FORECAST, IFERROR, EXACT

MODULE 15: RECORD MACRO

• Record macros for automating tasks

• Creating Button to run a Macro

• Macro with Keyboard & its benefit

• Run / Delete a macro

MODULE 16: VBA MACRO

• Introduction about VBA Scripts

• Advantages

• First Macro Creation

POWER BI

MODULE 1: INTRODUCTION TO POWER BI
• Business Intelligence
• Self Service Business Intelligence (SSBI)
• SSBI Tools
• Power BI vs Tableau vs QlikView
• What is Power BI
• Why Power BI?
• Key Benefits of Power BI
• Flow of Power BI
• Components of Power BI
• Architecture of Power BI
• Building Blocks of Power BI

MODULE 2: POWER BI DESKTOP
• Overview of Power BI Desktop
• Data Sources in Power BI Desktop
• Connecting to a data Sources
• Query Editor in Power BI
• Query Ribbon
• Clean and transform your data with Query Editor
• Combining Data – Merging and Appending
• Cleaning irregularly formatted data
• Views in Power BI Desktop
• Modelling Data
• Manage Data Relationship
• Automatic Relationship Updates
• Template Appstc.com 936 145 3182 Level Up STC
• Cross Filter Direction
• Create calculated tables and measures
• Optimizing Data Models
• PBIDS Files

MODULE 3: DATA VISUALIZATION
• Introduction to visuals in Power BI
• Charts in Power BI
• Matrixes and tables
• Slicers
• Map Visualizations
• Gauges and Single Number Cards
• Create scatter, waterfall, and funnel charts
• Modifying colours in charts and visuals
• Shapes, text boxes, and images
• What Are Custom Visuals?
• Page layout and formatting
• KPI Visuals
• Z-Order
• Explore time-based data
• AppSource

MODULE 4: DATA ANALYSIS EXPRESSIONS (DAX)
• Essential concepts in DAX
• Why is DAX important?
• DAX Syntax
• Data Types in DAX
• Ranking and rank over groups
• Filter and evaluation context
• Context interactions
• Calculation Types
• DAX Functions
• Measures in DAX
• DAX Operators
• DAX tables and filtering
• DAX queries
• Create simple and compound measures
• Schema relations
• Star schema design
• DAX Parameter Naming

MODULE 5: QUERYING DATA
• Opening the Query Editor
• Adding Conditional
• Columns Relationships
• Viewing Data Relationships
• Creating Relationships
• The Query Editor Applying Queries
• Naming Applied
• Changes Undoing
• Applied Steps
• Sorting Rows
• Adding an Index Column
• Filtering Rows

MODULE 6: SLICERS
• Adding and Using Slicers
• Formatting Slicers
• Searching Slicers
• Cascading Slicers
• Horizontal Slicers

MODULE 7: POWER BI ONLINE SERVICES
• Introduction to Power BI Online Services
• Sharing and Collaborating in Power BI
• Creating Dashboards in Power BI Online
• Power BI Online Data Refresh and Scheduling

SQL

Module 1: Introduction

• Understanding SQL Server

• SQL Server Version History

• Advantage of SQL Server

• DBMS and RDBMS

Module 2: Types of SQL Commands

• Creating Database

• Altering Database

• Deleting Database

• Creating Table

• Altering Table

• Deleting Table

Module 3: SQL Keys, Commands and Clauses

• Types of SQL Keys

• Using SQL Commands

• SQL Operators

• SQL Clauses

• DDL & DML

• TCL & TQL

• Querying Database

• Delete vs. Truncate

Module 4: SQL Joins

• Types of Joins

• Inner Join

• Outer Join

• Cross Join

• Self-Join

Module 5: Views

• Understanding Views

• Need of Views

• Creating, Altering and Dropping Views

• Simple and Complex Views

Module 6: Functions

• Built-In Functions

• Scalar Functions

• Aggregate Functions

• Creating, Altering and Dropping Fun

• Single row function

• Multi row function

Module 7: Stored Procedures

• Creating, Altering and Dropping Procedure

• Optional Parameters

• Input and Output Parameters

Module 8: Triggers

• Understanding Triggers

• Stored Procedures VS Functions VS Triggers

• Types of Triggers

• Creating, Altering and Dropping Triggers

Module 9: Normalization

• Normalization of Database Concept

• First Normal Form (1NF)

• Second Normal Form (2NF)

• Third Normal Form (3NF)

• Boyce-Codd Normal Form (BCNF)

• Fourth Normal Form (4NF)

• Fifth Normal Form (5NF)