Data Analysis using
Advance Excel
and SQL

• Basic Excel Functions
• Conditional Formatting
• Data Cleaning
• Intermediate Excel Functions
• VLOOKUP, HLOOKUP and XLOOKUP
• Array Formulas
• How to create basic charts?

• What is Data Modelling and how to achieve it in Excel?
• Understanding and exploring Pivot Table and Pivot Charts along with their applications
• Learning Power Query basics with examples

• Power Pivot
• Excel Slicers
• Trend Analysis
• Time Analysis
• Comparison Analysis

• Microsoft Power Point Overview
• Importance of Power Point in Data Analysis for Story Telling
• Creating Presentations
• Understanding Layouts and Sizing
• Slides Basics
• Text Basics
• Applying Themes
• Applying Transitions
• Managing Slides
• Slide Show Basics
• Indent and Lince Spacing
• Adding and Formating Pictures
• Adding Videos in Slide Deck
• Adding Shapes and 3D Diagrams
• Using Slides Tempaltes
• Animating Objects in Power Point
• Insert Tables
• Insert Charts
• Shortcuts in Power Point

• What is a Database
• Different Database Management Softwares
• Microsoft SQL Server Overview
• SQL Management Studio
• Select Statement
• Execution Order of SELECT Statements
• Ordering Results/Orderby Clause
• Additional SELECT Options
• Column aliases
• Table aliases
• Where Clause
• Rows Filtering using pattern matching
• Date types and Date functions
• DML and DDL Statements
• Inserting rows (INSERT)
• Inserting rows using subquery
• Updating rows (UPDATE)
• Deleting rows (DELETE)
• Create Table
• Delete/Turncate Table
• Drop Table/Database
• Primary Key & Foreign Key Concept

• Basics of ERD Diagrams
• Group By Caluse
• Count, Distinct Count, Sum, Average, Min, Max
• Having Clause
• Difference Between Where and Having Clause
• Handling Multiple Tables Using Joins
• Inner Join
• Left Join
• Right Join
• Full Outer Join
• Cross/Product Join
• Joining Three or More Tables
• Self-join
• Alternate Syntax, Implicit Joins
• Set Operations
• Working with Multiple Set Operators
• Viewing Graphical Execution Plans
• CASE Statements in SQL
• NULL vs Blank
• = vs IS NULL
• ISNULL function
• COALESCE
• Concatenating NULL Data
• Subqueries in SQL
• Subqueries in the SELECT Clause
• Subqueries in the WHERE Clause

• Window Aggregate Functions
• Partition By Clause
• Over Clause
• Qualification Clause
• Rank, Row Number and Dense Rank Functions
• Running Sum and Running Difference
• Lag and Lead Functions
• First and Last Function
• NTILE
• Range vs Row Function
• Data Type Precedence
• String Related Data Types
• Numeric Data Types
• Date and Time Data Types
• Converting Between Data Types
• How to Find Help on Functions
• Understanding Data Type Conversion

• Interactive Dashboards
• Dashboard Reporting
• Build a KPI Dashboard
• Protecting and Sharing your Dashboard
• Linking your Dashboard to Power Point

• • Recent graduates, third year and final year students of any degree
  • Professionals from any domain who want to learn Excel tool to create effective reports and spreadsheets for work assistance
  • Executives and Department Heads who want to gain better understanding of MS Excel.

Neman Shahid

Fahad Jamshed

Duration: 8 weeks

Class Days: Saturday & Sunday

Timings: 11:00 AM to 04:00 PM

Since our instructors are industry experts so they do train the students about practical world and also recommend the shinning students in industry for relevant positions.

Yes, you can rejoin the training within the span of an year of your registration. Please note following conditions in case you’re rejoining.
1) There are only 5 seats specified for rejoiners in each iteration.
2) These seats will be served on first come first basis.
3) If you have not submitted your complete fee, you may not be able to rejoin. Your registration would be canceled.

Don’t worry! We have got you covered. You shall be shared recorded lectures after each session, in case you want to revise your concepts or miss the lecture due to some personal or professional commitment.

Since our trainings are led by Industry Experts so it is made sure that content covered in workshop is designed with hand on knowledge of more than 70-75 % along with supporting theory.

For this professional workshop, you need to have a PC with minimum 4GB RAM and ideally 8GB RAM.

Yes, you will be awarded with a course completion certificate by Dice Analytics. We also keenly conduct an annual convocation for the appreciation and recognition of our students.