Become a high paying Data Analyst by doing our Job ready program !!!

About this course....

  • It’s a 100% hands-on course

  • Data files and source links will be given to participants for hands-on

  • It covers Excel in detail (Complete Video Tutorials including all practical exercises)

  • It covers Power BI in detail (Complete Video Tutorials including complete practical case study)

  • It covers SQL in detail (Complete E-Book)

  • It covers Python in detail (Complete E-Book)

  • A Course completion certificate will be provided by the course instructor i.e. Mr. Lokesh Lalwani

Course curriculum

  • 1

    Welcome to the Course

    • Welcome message and Course Intro.

    • Data Analytics Introduction
  • 2

    Clearing the confusion

    • 3 ways to use Power BI (PBI Desktop, PIB Service, PBI Mobile app)

    • Roles in Data (Business Analyst, Data Analyst, Data Engineer, Data Scientist, Data Admin)

    • Role of Data Analyst
  • 3

    Business Intelligence (BI) Concepts

    • Introduction & importance of Business Intelligence

    • Tools and Technologies in Business Intelligence area

    • Power BI Vs. Tableau
  • 4

    About Microsoft Power BI

    • Power BI Architecture

    • Power BI introduction and Its components

    • Power BI over Excel
  • 5

    Power BI Desktop - An Introduction

    • PBI Flow – Most Important

    • Download PBI Desktop & Interface tour

    • Understanding Key backstage options (file tab)

    • Understanding Case Study 1 and Role play

    • Power BI Practice Files
  • 6

    Connecting Power BI with Different Data sources

    • Get Data from Flat Files (CSV, Text & Excel Files)

    • Get Data from OData Feed

    • Connect to local folder
  • 7

    Data Transformation through Power Query

    • Power Query & Power Query Editor Introduction

    • Difference between Transform and load

    • Merge, Add and remove columns

    • Sorting in Power Query

    • Merge and Append queries

    • Transform table tools: grouping, transpose, reverse rows, count rows, use as header

    • Transform Column tools: detecting & changing data type, Fill, Unpivot etc.

    • Text Column tools: Splitting, replace values, extracting, merging, formatting and parsing columns

    • Number Column tools: Statistical, standard, rounding and information

    • Building a complete Calendar Table

    • Conditional, Custom, Invoke Custom, Index and Duplicate Column

    • Flashfill in Power Query (Columns from example)

    • Understanding different view options

    • Connect to multiple files in a folder

    • Is Power Query smart enough?
  • 8

    Data Modelling in Power BI

    • How to build relationship and relationship cardinality

    • Understanding filter flow & Cross Filter direction

    • Building entire model

    • Snowflake & Star schema

    • Add Related tables
  • 9

    Understanding Data Analysis Expressions (DAX)

    • Understanding DAX and its Types

    • Difference between calculated columns and measures
  • 10

    Basic DAX functions - (Calculated Columns)

    • Logical DAX

    • Another example of conditional column

    • Understanding SWITCH function

    • Understanding Year and Today function

    • Playing with Text functions (Left and Search)
  • 11

    Relational Functions

    • Bringing Data from any Related table using RELATED Function

    • Opposite of related function - RELATEDTABLE Function
  • 12

    Understanding Contexts in Power BI

    • Filter & Row Context - A must watch for any Power BI user
  • 13

    Understanding Measures

    • Understanding Measures Practically

    • Creating Measure table

    • Counting Values using Count Function
  • 14

    Most Common DAX - CALCULATE & FILTER

    • Condition Based Calculation - Calculate Function

    • Building Calculated Table using Filter Function

    • Understanding Variable with Calculate, Filter and Sameperiodlastyear function
  • 15

    DAX for Iterators

    • Iterator Functions

    • Iterator Function - MAXX
  • 16

    Filtering Data using DAX

    • Crossfilter (Activate filter direction virtually)

    • Ignoring Filter Context by using ALL Function

    • Nesting of Filter and Calculate Function
  • 17

    Date & Time DAX

    • Month,Qtr.,Year to Date calculation

    • Previous Day,Month,Qtr.,Year calculation

    • Periodic Calculation
  • 18

    Report setup in Power BI Desktop

    • Understanding Report view and creating a new report

    • Page display settings in a Power BI report

    • Setting color theme aligned to project/company color theme & Theme Gallery
  • 19

    Building Report

    • Adding non-visualization object

    • Inserting Basic charts and understanding format tab

    • Creating Comparison visuals (Doughnut Chart)

    • Creating Bar chart for Top 5 Customers

    • Plotting Values on a Map Visual

    • Understanding Matrix visual along with Conditional Formatting

    • Comparing Actual vs. Target using Gauge Chart

    • Using Slicers and understanding its impact on the report
  • 20

    Advanced Visualizations

    • Showing monthly trend with line chart

    • Setting up forecasted revenue in line chart

    • Presenting data in an Area chart with awesome tooltip feature

    • Building amazing KPI's

    • Drillthrough Report Page

    • Custom visual (third party visual)

    • Story telling through Bookmarks

    • Ask questions from PBI and get visuals (NLQ)

    • Sync Slicer

    • Key Influencer P 1

    • Key Influencer P 2

    • Decompostition Tree

    • Function based visual titles
  • 21

    Advanced Report options

    • What if Parameter

    • Setting RLS (Row Level Security)

    • Tooltip Page

    • Different types of filters in report view

    • Change how visuals intereact in a report
  • 22

    Publish the Report to Cloud

    • Publish the report to Power BI service
  • 23

    Intro. to PBI Service

    • Understanding Power BI Service

    • Creating New PBI Service account

    • PBI Interface tour
  • 24

    Type of Content Containers

    • My workspace Vs. Workspaces Vs. Apps
  • 25

    Reports and Dashboard

    • Understanding various options in Report interface Part - 1

    • Understanding various options in Report interface Part - 2

    • 3 ways of creating a Dashboard

    • Understanding various options in Dashboard interface
  • 26

    Gateways, Scheduled Refresh & Incremental Refresh

    • Understanding Gateways and its types

    • Enabling scheduled refresh through Personal gateway

    • Incremental Refresh (Theory)

    • Incremental Refresh (Practical)
  • 27

    Sharing & Collaboration

    • Assigning user roles and giving permissions

    • Sharing through publishing apps

    • Implementing RLS
  • 28

    Advanced Power BI Service

    • Real time data streaming connection

    • Real time data Dashboard
  • 29

    PBI Mobile Application

    • Downloading and understanding PBI Mobile App interface
  • 30

    PL-300 Certification Exam Prep

    • Set-1

    • Set-2
  • 31

    1. EXCEL FROM SCRATCH

    • 0101 Touring the interface

    • 0102 Enter data & saving workbook

      FREE PREVIEW

    • 0103 Applying Formulas

    • 0104 Changing a worksheet's structure

    • 0105 Find, Replace & Spell Check

    • 0106 Applying fonts, background colors, and borders

    • 0107 Formatting numbers and dates

    • 0108 Making the pieces fit

    • 0109 Inserting headers & footers and Repeating Rows

    • 0110 Managing Large Workbooks

    • Quiz 1
  • 32

    2. EXCEL ESSENTIALS

  • 33

    3. SUM COUNT AVERAGE FUNCTIONALITY (WITH CONDITIONS)

    • 0301 SumIf & SumIfs

      FREE PREVIEW

    • 0301 Exercise of SumIf & SumIfs

    • 0302 Count Functions

    • 0302 Exercise of Count Functions

    • 0303 AverageIf & AverageIfs

    • 0303 Exercise of AverageIf & AverageIfs

    • Excel files for practice (Please Download)
  • 34

    4. TEXT FUNCTIONS (MANIPULATING TEXT)

  • 35

    5. LOGICAL FUNCTIONS (WORKING WITH CONDITIONS)

    • 0501 If Functionality

    • Ex 0501 IF Functionality

    • 0502 If with AND

    • Ex 0502 If with AND

    • 0503 If with OR

    • Ex 0503 If with OR

    • 0504 If with AND & OR, IF with Countif

    • Ex 0504 If with AND & OR

    • 0505 Nested If (A Genie with 3 wishes)

    • Ex 0505 Nested If

    • Excel Files for Practice(Please Download)
  • 36

    6. LOOKUP FUNCTIONALITY (BURN SIMPLE FIND TECHNIQUE)

    • 0601 Vlookup (Exact Match) # 1

    • Exercise 0601 Vlookup (Exact Match) # 1

    • 0602 Type of References (Use of $ Sign)

    • 0603 Vlookup (Exact Match) # 2

    • Exercise 0603 Vlookup (Exact Match) #2

    • 0604 Double Vlookup

    • Exercise 0604 Double Vlookup

    • 0605 Vlookup on Duplicate Values

    • Exercise 0605 Vlookup on Duplicate Values

    • 0606 Vlookup (Approximate Match)

    • Exercise 0606 Vlookup (Approximate Match)

    • 0607 Vlookup with IF (Conditional Vlookup)

    • Exercise 0607 Vlookup with IF (Conditional Vlookup)

    • 0608 Hlookup (Exact Match)

    • Exercise 0608 Hlookup (Exact Match)

    • 0609 Hlookup (Approximate Match)

    • Exercise 0609 Hlookup (Approximate Match)

    • 0610 Lookup (Don't Use This)

    • Exercise 0610 Lookup (Don't Use This)

    • 0611 Vlookup & Match (Create magical Vlookup)

    • Exercise 0611 Vlookup & Match ( Create a Magical Vlookup)

    • 0612 Match (Gives us Column & Row Number)

    • Exercise 0612 Match (Gives us Column & Row Number)

    • 0613 Index & Match (Made for each other)

    • Exercise number 1 of 0613 Index & Match (Made for each other)

    • Exercise number 2 of 0613 Index & Match (Made for each other)

    • Excel Files for Practice (Please Download)
  • 37

    7. DATE & TIME FUNCTION (A TRICKY GAME)

    • 0701 How Excel Records Date & Timer

    • 0702 Now, Today & Autofill

    • 0703 DateValue & TimeValue

    • 0704 Calculate WorkingDays

    • 0705 DatedIf

    • Excel Files for Practice (Please Download)
  • 38

    8. FINANCIAL FUNCTIONS (USEFUL FOR NON-FINANCE GUYS TOO)

    • 0801 Loan Calculation (PMT, PPMT, IPMT etc.)

    • Ex 0801 Loan Calculation (PMT, PPMT, IPMT etc.)

    • 0802 Creating a Loan Table

    • Ex 0802 Creating a Loan Table

    • 0803 Calculate Depriciation

    • Ex 0803 Calculate Depriciation

    • Excel Files for practice (Please Download)
  • 39

    9. SORT & FILTER (COMMON FOR ALL)

    • 0901 Basic Sorting & Shortcut keys

    • Ex 0901 Basic Sorting & Shortcut Keys

    • 0902 Sorting Using Custom List

    • Ex 0902 Sorting Using Custom List

    • 0903 Horizontal Sorting

    • Ex 0903 Horizontal Sorting

    • 0904 Basic Filter & Shortcut Keys

    • Ex 0904 Basic Filter & Shortcut Keys

    • 0905 Filter Problem # 1 (Copy Filtered Data without hidden rows)

    • Ex 0905 Filter Problem # 1 (Copy Filtered Data without hidden Rows)

    • 0906 Filter Problem # 2 (Pasting Values on Filtered Data)

    • Ex 0906 Filter Problem # 2 (Pasting Values on Filtered Data)

    • 0907 Filter Problem # 3 (Performing Calculation on Filtered Data)

    • Ex 0907 Filter Problem # 3 (Perform Claculation on Filtered Data)

    • 0908 Advanced Filter (Using Complex Criteria's)

    • Ex 0908 Advanced Filter (Using Complex Criteria's)

    • Excel Files for Practice (Please Download)
  • 40

    10 EXCEL CHARTS (ITS TIME FOR VISUAL PRESENTATION)

    • 1. Excel Charts Introduction

    • 2 How to Creat a Chart

    • 3 Link a Chart Title

    • 4 How to Show Axis Title

    • 5 How to Show Data Label

    • 6 Column Chart

    • 7 Bar Chart

    • 8 Line Chart 1

    • 9 Line Chart 2

    • 10 Area Chart

    • 11 Pie Chart

    • 12 Pie of Pie or Bar of Pie

    • 13 Line Chart with Log Scale

    • 14 Bubble Chart

    • 15 Selecting Chart Elements

    • 16 Use of Format Dialog box

    • 17 Modifying Chart & Plot Area

    • 18 Modifying Data Series

    • 19 How to handle Missing Data

    • 20 Modifying 3-D Charts

    • 21 Creating a Chart Templates

    • 22 Creating Picture Charts

    • 23 Creating Combination of upto 5 Different Charts

    • 24 Show data with chart in Data Table

    • 25 Play with Design & Layout of Chart

    • Excel Files for Practice (Please Download)
  • 41

    11 DATA CONSOLIDATION (MULTIPLE WORKBOOKS WORKSHEETS)

    • 1101 Consolidate Data using Excel Inbuilt Function

    • Ex 1101 Consolidate data using Excel Inbuilt Function

    • Excel Files for Practice (Please Download)
  • 42

    12 DATA VALIDATION

    • 1201 Applying Data Validation

    • Ex 1201 Applying Data validation

    • 1202 Dependent Dropdown List

    • Ex 1202 Dependent Dropdown List

    • 1203 Using Data Validation with Vlookup & Match

    • 1204 Other Data Validation Examples

    • Excel Files for Practice (Please Download)
  • 43

    13 PROTECTION OF WORKSHEET WORKBOOK

    • 1301 Worksheet Level Protection

    • Ex 1301 Worksheet Level Protection

    • 1302 Cell Level Protection

    • Ex 1302 Cell Level Protection

    • 1303 Hiding Formulas from Formula Bar

    • Ex 1303 Hiding Formulas from Formula Bar

    • 1304 Protecting Workbook Structure

    • Ex 1304 Protecting Workbook Structure

    • 1305 Workbook Level Protection

    • Ex 1305 Workbook Level Protection

    • Excel Files for Practice (Please Download)
  • 44

    14 PIVOT TABLE & TECHNIQUES

    • 1401 Understanding pivot Table

    • 1402 Using Pivot Table in Real Scenario

    • Ex 1402 Using Pivot Table in Real Scenario

    • 1403 Grouping in Pivot Table

    • Ex 1403 Grouping in Pivot Table

    • 1404 Using Slicers (A Visually attractive Filter)

    • Ex 1404 Using Slicers (A Visually attractive Filter)

    • Excel Files for Practice (Please Download)
  • 45

    15 WHAT-IF ANALYSIS & SOLVER ADD-IN

    • 1501 Goal Seek (A Reverse Approach)

    • Ex 1501 Goal Seek (A Reverse Approach)

    • 1502 One Way Data Table

    • 1503 Two Way Data Table

    • 1504 Scenario Manager

    • 1505 Solver Add-In

    • Excel Files for Practice (Please Download)
  • 46

    16 AUTOMATION IN EXCEL (MACROS)

    • 1601 Record your first Macro

    • 1602 Record Another Macro

    • 1603 Assigning Macros to a Shape, Button, Picture

    • 1604 Editing or Deleting a Macro

    • 1605 Security Settings of a Macro

    • Excel Files for Practice (Please Download)
  • 47

    17. New Chapter - Conditional Formatting

    • 1701 Highlight cells rules

    • 1702 Top Bottom Rules

    • 1703 Data Bars

    • 1704 Color Scales

    • 1705 Icon Sets

    • 1706 Function based Conditional Formatting

    • Exercise files for practice (please download)
  • 48

    18. New Excel Formals and Tricks

    • 1801 How to create a Map Chart in Excel

    • 1802 How to use Forecast sheet in Excel

    • Excel Files for Practice (Please Download)

    • 1803 Analyze Data using Excel
  • 49

    19. Bonus Videos (In Hindi)

    • Why my excel file size is huge and how to reduce Excel file size

    • Problem of unwanted names when we create a copy of the sheet in Excel

    • New chart types in Excel 2019/Office 365 || Map Chart || Funnel Chart

    • How do you change the number format in Excel?

    • 6 Types of Errors in Excel and how to resolve them

    • Excel Files for Practice (Please Download)
  • 50

    **** SQL Module ****

    • E-Book - Mastering MySQL- Essential Skills for Aspiring Data Analysts
  • 51

    **** Python Module ****

    • E-Book - Python for Data Analysts- Unlocking Insights with Code

Become an Excel expert by doing our Excel Masterclass for only Rs 3499!!!

Enroll Now

This Course includes

  • 12 + hours on-demand video

  • Full lifetime access

  • One Live QnA Session every month with Mr. Lokesh Lalwani

  • Private Forum to clear all your doubts

  • Downloadable Excel Files for Practice

  • Access on Laptop, mobile and TV

  • Certificate of Completion

What does the Customers say about our course....

''Go for it, Don't even think"

Kelvin Bose (Dallas, USA)

"The content of the training was very good and also very well delivered by Lokesh. The training is very beneficial for me as I didn't know many concepts before it."

"Recommend this to everyone."

Claire Adlay (Sydney, Australia)

"The training included all the necessary elements and fundamentals. It was an enriching experience for us. Moreover, It was really helpful receiving qualitative support from Lokesh & his team. I appreciate his efforts and recommend this to everyone."

"Easy to understand with so many examples"

Mohit Mutha (Pune, India)

"In past 5 years, I can tell this is the first time, I am understanding Excel and logics are clear"

"The course material provided here by Mr. Lokesh Lalwani is excellent."

Sandeep Rana(Delhi, India)

"The course material provided here by Mr. Lokesh Lalwani is excellent. He explained the concepts in a lucid and explicit way which became very easy to correlate and in understanding the topic."

"Excellent Course"

G Seenivasan

"Excellent Course and Excellent Teaching videos with example."

Grow your career with the course you need !!!

Continue

FAQ

  • How do i access the course after purchase?

    Once you purchase a course (Single course or Subscription), you will be able to access the courses instantly online by logging into your account. Use the user name & password that you created while signing up. Once logged in, you can go to the "My Courses" section to access your course.

  • Are these video based online self-learning courses?

    Yes. All of the courses comes with online video based lectures created by certified instructors. Instructors have crafted these courses with a blend of high quality interactive videos, lectures, quizzes & real world projects to give you an indepth knowledge about the topic.

  • Can i play & pause the course as per my convenience?

    Yes, absolutely & that's one of the advantages of self-paced courses. You can anytime pause or resume the course & come back & forth from one lecture to another lecture, play the videos multiple times & so on.

  • How do i contact the instructor for any doubts or questions?

    Most of these courses have general questions & answers already covered within the course lectures. However, if you need any further help from the instructor, you can use the inbuilt Chat with Instructor option to send a message to an instructor & they will reply you within 24 hours. You can ask as many questions as you want.

  • For how long can i access my course after the purchase?

    You will get lifetime access to this course.

Some of my Clients

Instructor - Lokesh Lalwani - Microsoft Certified Trainer & Author with 10+ yrs Experience

Lokesh Lalwani

I love to partake in outdoor activities, meeting people from around the world & having good old fashion tea with some music. I have found that nothing satisfies me more than to solve problems and contribute to the growth of individuals & businesses.   I have worked with professionals from top companies like KPMG, Dell, PWC, Pepsico etc.

  • Community

    You become a part of our learning community and get to interact with likeminded people who are also looking to grow in their professional & personal lives.

  • Social Contribution

    As this course helps people start their career or work from home as freelancer you help us reach our goal of reaching 1000 people in 2020 who are not financially capable to buy the course but want to learn and grow professionally.

  • Go Digital

    As this is a Digital course, you will help the environment save fuel by not commuting & paper by taking e-study material. And for that a little tree thanks you.

Featured Reviews

5 star rating

Excel Masterclass - Very helpful

Kunal Chhabra

I went through your course, it is very much valuable & me being a beginner was easily able to grab the things explained & understand it quickly. Than...

Read More

I went through your course, it is very much valuable & me being a beginner was easily able to grab the things explained & understand it quickly. Thank you so much for this hard work done sir. This would be really helping in my new career now.

Read Less
5 star rating

Excel Masterclass - Nice & Easy to understand

mukesh jha

Nice & Easy to understand

Nice & Easy to understand

Read Less
5 star rating

Excel Masterclass - EXREAMLY HELPFULL

Hemant Dayma

HELLO MY SELF HEMANT DAYMA BEFORE BUY THIS COURSE I KNOW ABOUT EXCEL BUT NOT THAT MUCH AFTER SEE VIDEO OR PRACTISE IT NOW I AM CAPABLE TO DO ANY EXCEL RELATE...

Read More

HELLO MY SELF HEMANT DAYMA BEFORE BUY THIS COURSE I KNOW ABOUT EXCEL BUT NOT THAT MUCH AFTER SEE VIDEO OR PRACTISE IT NOW I AM CAPABLE TO DO ANY EXCEL RELATED WORK EASILY. THANKS, LOKESH SIR

Read Less
5 star rating

Excel Masterclass - Elaborative Training

Shruti Kumari

Training is very elaborative and easy to understand.

Training is very elaborative and easy to understand.

Read Less
5 star rating

Excel Masterclass - On the way of ADVANCE in excel

Nawaz shaikh

I have watched a lot of videos to learn excel but finally, I got the best, and +point is in sequence and always replies back for needed. I appreciate your ha...

Read More

I have watched a lot of videos to learn excel but finally, I got the best, and +point is in sequence and always replies back for needed. I appreciate your hard work to share your knowledge with us. and one more thing, thanks for making the videos shortly and useful topics for a fresher. God bless you...

Read Less
5 star rating

Excel Masterclass - Nice course

Ashish Gupta

This is a good course to solidify your knowledge of excel. It has many tricks which you won't find anywhere.

This is a good course to solidify your knowledge of excel. It has many tricks which you won't find anywhere.

Read Less
5 star rating

Excel Masterclass -

Snehal Sawant

Very good content & ample valuable knowledge.

Very good content & ample valuable knowledge.

Read Less
5 star rating

Excel Masterclass- I am enjoying it.

Yatindra Kumar

Topic-wise elaboration is very nice. The core thing is I am not getting bored while learning. Thank you.

Topic-wise elaboration is very nice. The core thing is I am not getting bored while learning. Thank you.

Read Less
5 star rating

Excel Masterclass - Nice & Easy to understand

mukesh jha

Nice & Easy to understand

Nice & Easy to understand

Read Less
5 star rating

Excel Masterclass- good

Ashutosh Prasad

good

good

Read Less
5 star rating

Excel Masterclass- Effective

sumeer ahmed

Assessment helps us learn effectively.

Assessment helps us learn effectively.

Read Less
4 star rating

Excel Masterclass- Excellent

Pravin Late

I will recommend it to everyone who wants to learn excel in an easy way, Thanks Lokesh......!

I will recommend it to everyone who wants to learn excel in an easy way, Thanks Lokesh......!

Read Less