Notice: WC_Cart::get_cart_url is deprecated since version 2.5! Use wc_get_cart_url instead. in /home/swatipaliwal/public_html/excelcrazy.com/wp-includes/functions.php on line 3839
Excel Crazy

DATA ANALYSIS ESSENTIALS FOR SUCCESS

Data Analytics course is designed to help you become a successful Data Analyst, this course is for those just starting their career in Analytics. It will teach you how to make powerful dashboards, macros, and SQL along with a strong focus on case studies to ensure hands-on learning. Once armed with the analytics, you will also learn the powerful Data visualization tool Tableau to present your analysis. We also conduct Excel Corporate training across the globe.
Course duration: 50 hours (50 hours live training + Practice and Self-study)

COURSES:

Advanced Excel, Excel Macro’s(VBA), SQL, Statistics Essentials, and Tableau

Report Visualization – Dashboard Designing

Advanced Excel

  • Explanation of data calculation in Excel
  • Use of Shortcuts
  • Formatting and Conditional Formatting
  • Working with Formulas – Logical and Text Functions
  • Understanding about Sorting, Filtering, and Data Validation
  • Data Analysis using Pivot Tables

Introduction to Charts and Functions

  • Understanding of Mathematical, Statistical Functions
  • Worksheet and Workbook Protection and Security
  • Understanding of Name Ranges
  • Introduction of Charts
  • Introduction of Form Controls
  • Understanding of Data Tools Panel
  • Basics of Macro Recording

Dashboard Designing

  • Overview of Dashboards
  • Deciding on Dashboards
  • Trends and Scenarios using charts
  • Advanced Charting Techniques using Thermometer, Doughnut, Pareto, Panel and Step Chart
  • Designing Sample Dashboard using Form Controls
  • Tips and Tricks to enhance dashboard designing

Statistics Essentials for Reporting

  • Scatter Plot
  • Quadrant Analysis
  • Histogram
  • Paret Chart
  • Normal Distribution
  • Calculation of Standard Deviation and Variance
  • Understanding of Least Square Error
  • Simple Linear Regression
  • Multi-Linear Regression
  • Logistic Regression
  • ANOVA
  • RFM Segmentation


Report Automation Using VBA and SQL

Excel Macros ( Visual Basic for Applications)

  • Working with VBE (Visual Basic Editor)
  • Introduction to Excel Object Model
  • Understanding of Sub and Function Procedures
  • Key Component of Programming Language
  • Understanding of If, Select Case, With End With Statements
  • Looping with VBA
  • User Defined Function
  • Some Commonly Used Macro Examples
  • Error Handling
  • Object and Memory Management in VBA
  • User Form Controls
  • ActiveX Controls
  • Communicating with Database MS Access through ADO – Exporting/Importing Data

DBMS – SQL

  • SQL Basics
  • Getting Started with SQL Management Studio
  • Understanding of RDBMS concept
  • Data Manipulation – Reading, Manipulating, and Functions
  • DDL,DML,DCL,DQL Commands
  • Data Aggregation & Summarization
  • Concept of Indexing
  • Partitioning, Decile creation, Reporting etc
  • Stored procedures
  • Work Optimization

Datat Visualization Using Tableau

  • What is Tableau? What does the Tableau product suite comprise of? How Does Tableau Work?
  • Tableau Architecture
  • What is My Tableau Repository?
  • Connecting to Data & Introduction to data source concepts
  • Understanding the Tableau workspace
  • Dimensions and Measures
  • Data Types & Default Properties
  • Tour of Shelves & Marks Card
  • Using Show Me!
  • Building basic views
  • Saving and Sharing your work-overview

Tableau: Building Views (Reports) – Basics

  • Date Aggregations and Date parts
  • Cross tab & Tabular charts
  • Totals & Subtotals
  • Bar Charts & Stacked Bars
  • Line Graphs with Date & Without Date
  • Tree maps
  • Scatter Plots
  • Individual Axes, Blended Axes, Dual Axes & Combination chart
  • Edit axis
  • Parts of Views
  • Sorting
  • Trend lines
  • Reference Lines
  • Forecasting
  • Filters
  • Context filters
  • Sets
  • In/Out Sets
  • Combined Sets
  • Grouping
  • Bins/Histograms
  • Drilling up/down – drill through
  • Hierarchies
  • View data
  • Actions (across sheets)

Tableau: Building Views (Reports) – Advanced Maps

  • Explain latitude and longitude
  • Default location/Edit locations
  • Symbol Map & Filled Map
  • Custom Geo Coding

Tableau: Calculated Fields

  • Working with aggregate versus disaggregate data
  • Explain – #Number of Rows
  • Basic Functions (String, Date, Numbers etc)
  • Usage of Logical conditions



Tableau: Table calculations
  • Explain scope and direction
  • Percent of Total, Running / Cumulative calculations

Tableau: Parameters

  • Create What-If analysis
  • Using Parameters in
  • Calculated fields
  • Bins
  • Reference Lines
  • Filters/Sets
  • Display Options (Dynamic Dimension/Measure Selection)

Tableau: Building Interactive Dashboards- (Building & Customizing)

  • Combining multiple visualizations into a dashboard (overview)
  • Making your worksheet interactive by using actions
  • Filter
  • URL
  • Highlight

Tableau: Formatting

  • Options in Formatting your Visualization
  • Working with Labels and Annotations
  • Effective Use of Titles and Captions

Tableau: Working with Data

  • Multiple Table Join
  • Data Blending
  • Difference between joining and blending data, and when we should do each
  • Working with the Data Engine / Extracts
  • Working with Custom SQL
  • Toggle between to Direct Connection and Extracts

Tableau: Working with Tableau Server – Accessing reports through web

  • Publishing to Tableau Server – Overview of publishing
  • Server Administration – Managing Users, Projects & Object level and Data Security as per Users
  • User Filters

Case Studies:

1: E-Commerce Sales Dashboard (Excel)
Visualize e-commerce data into an interactive dashboard for revenue, cost, quantities, and profit KPI’s, which will initiate stack holders to take actions.
2: HR Analytics Dashboard (Excel)
In this case study, you will design HR dashboard to keep the track of employee attendances, their leaves, late marks and much more.
2: Financial Statement Automation (VBA)
Tool to prepare financial data from raw files into multiple workbooks.
3: Pareto Analysis Customized Add-In (VBA)
A Customize tool to perform Pareto analysis on any data set.
4: Customer Attrition Analysis Report (VBA-SQL)
Look for the customers those are on attrition side from the huge SQL/Access database.

Tableau Case Studies

  • Online Superstore Sales Dashboard
  • Pizza Store Sales Dashboard
  • Customer Case Study
  • Pharma Case Study
January 9, 2018

14 responses on "DATA ANALYSIS ESSENTIALS FOR SUCCESS"

  1. Hi,

    I am 43, careerwise am not grown, today am facing many problems in lifer, I like to do some course which helps me to get a better job and better life style. help me with some good suggestion/advise. and also which course suits me. at present i work on excel, but i won’ t use pivot, range, etc.. and even i love programming but lack of knowledge,. help with best advise and course.. I can provide my self for 4 day in november i.e 23rd to 26th ( Holidays for me)..

    Regards
    Ravi

  2. I am very passionate about Excel and VBA but did not have much guidance that how could I live my passion. Then Lokesh helps me out living my passion through his problem-solving skills in Excel and VBA. He helped me a lot in understanding the concept of Excel VBA. He also helped me out in my project. If you seriously want to learn and Excel and VBA then do not waste your time here and there……directly go to the master of Excel VBA …….i.e Lokesh.
    Thanks, Lokesh for all your support and guidance.

  3. Hello Mr. Lokesh,
    I found your name and mail ID from Internet.
    I am seeking for career advice for my daughter who has completed her graduation – B. Com (Hon.) regular from Delhi University. She is looking for some short term job oriented course. I just wanted to know is this course suitable for a person who does not have much IT knowledge. What kind of job profile a person gets after completing this course.

    Looking forward to your reply.
    Regards,
    Neha

  4. Sonu Solanki – BTMay 9, 2016 at 2:55 pmReply

    I learned a great deal of new excel tricks for dashboards, pivot tables, and charts. Each Class was well designed. Now I will be able to turn around a decent dashboard quickly and efficiently.
    Thanks, Lokesh Paliwal!

  5. Braja Kishore Boxy – IBM Global ServicesMay 9, 2016 at 2:55 pmReply

    I love it. Each course with sample workbook examples. Kool dashboard designs and Automation examples. Each session was up to the point. Thanks Lokesh

  6. I highly recommend this course for someone attempting to move from an intermediate range to a more advanced range in Excel which includes Dashboard design. I had some questions which I emailed to the instructor and he was quite prompt and professional with his answers. This course is an exceptional value for the cost!” – Rod Buchanan

  7. Khushboo – Nokia Siemens NetworksNovember 9, 2015 at 2:53 pmReply

    I learned some good advanced excel tricks from Lokesh and it was very much useful in my work. He is a focused and provide you the good understanding in a simpler way which is easy to grap it. He is always helpful after training class and has valuable content of training material. You are good example of how commitment & hard work pay off. May your future efforts be equally successful & rewarding,”

  8. Lokesh is an excellent teacher with a kncak to make even the most complex of concepts seen simple. I joined the advance excel &vba classes with very little prior experience in excel but I was able to pick it right away , the lesson are simple and precise and ideal for layman.This course has helped me a lot in moving my career in the direction I want.

  9. Tanuj Gupta – IDP IndiaMarch 5, 2015 at 2:56 pmReply

    Great Course with tips and tricks. Awesome Dashboard designs. Thanks, Lokesh Sir

  10. Anshul Gupta – HSBCJune 9, 2014 at 2:54 pmReply

    I really love Lokesh Paliwal’s down to earth teaching style. I feel like I can relate to each examples what he’s saying and it makes me stay tuned for longer. I have learned a tremendous amount from his manner and style as well. Many Thanks Lokesh

  11. Rishi Sharma – AccentureMay 12, 2014 at 2:54 pmReply

    Classes on using charts, pivot tables, etc to create dashboard reporting. Very well presented and explain complex reporting issues in a simple way. It was clear and easy to understand and has helped me develop reporting dashboards. Excellent!

  12. Hi Lokesh,

    I live in Sector 14 gurgaon, do you provide training on weekends too?

    Regards,
    Tarachand

Leave a Message

Your email address will not be published. Required fields are marked *

top
 

© LOKESH PALIWAL 1987 - 2017

Reporting Solutions | Data Consulting | Analytics Training