Excel Crazy

Data Analytics Training

  • 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, Access 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.
    Course duration: 50 hours (50 hours live training + Practice and Self-study)

Who Should do this course?
Candidates from various quantitative backgrounds, like Engineering, Finance, Maths, Statistics, Business Management who want to head start their career in Data Visualization and Analytics.

Course Outline

Visualization and Automation Using Excel VBA

  • Explanation about 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

  • Understaing 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 Dahsboards
  • 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

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
  • Understaing 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

Introduction to MS Access

  • What are Databases
  • Create and Modify Tables
  • Develop and Use of Queries
  • Working with Tables
  • Form Designing
  • Report Designing

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

Data 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

 

 Data Science Using Python

  • Anaconda Installation & Setup
  • Jupyter Notebook Overview
  • Python Basics
  • NumPy, Pandas, MatPlotLib
  • Data Visualization: Seaborn, Plotly, Cufflinks
  • Machine Learning Introduction – SciKit
  • Preprocessing Data
  • Classification: Naive Bayes, Random Forest, Logistic Regression, KNN, SVM
  • Regression: Linear Regression, SVR, Random Forest, Decision Tree
  • Clustering: K-Means, Hierarchical, Dendrograms
  • Natural Language Processing ( NLP )

 

top
 

© LOKESH PALIWAL 1987 - 2018

LOKESH@EXCELCRAZY[DOT]COM | 8826547882

Reporting Solutions | Data Consulting | Analytics Training

CONTACT US
close slider

Your Name (required)

Your Email (required)

Your Phone no. (required)

Your Message for us...

Skip to toolbar