Data visualisation only helps to streamline data and information for a better understanding. Data visualisation is a technique through which the users can improve the visual aspects of their data. The visual representation is constructive for a growing organisation. Many software related to data analytics uses data visuals for better outcomes and relational studies. Softwares like Excel and VBA use data presentation for an impactful approach. 

Excel has risen as the application that helps users to store, record and analyse any form of data. Data management and allocation are the USP for this application. MS Excel has many tools with different functionality based on numerous requirements. Excel holds the key to data visualisation.

Graphical representation of information and data is known as Data visualisation. This segment has many enhanced tools that would help the user to represent the allocated data for better outcomes and bigger purposes. Those enhanced elements mainly are the following:

  1. Charts
  2. Maps
  3. Graphs

These tools of data visualization helps the audience to understand the trends, outliers and patterns in data by providing an accessible way to approach them.

Once the world of Big Data is considered, then tools of data visualisation and related technology are very essential for simplify and analyse the massive amount of data and information and eventually it will assist in data-driven decisions.

Data visualisation is a form through which the tools enhance the visual experience of the users and help them to receive the message completely. There are many tools available for these purposes but effectively charts, graphs and maps will disseminate the trends and outliers to the users. For example, the storytelling format but with a purpose, because once users see something with visual graphics, it comes easier for them internalise it quickly.

  • Call us +91 99907 48956 | 96503 08956

  • info@analyticstraininghub.com

Enroll Now
  • Course Fees:- 30,000/- 27000/- (INR)

  • Duration :- 60 Hours | 30 Classes + Assigments Hours

  • Tools:- Advanced Excel, VBA, Excel Dashboard, PowerBI/Tableau

  • Learning Mode:- Classroom & Online Training

  • Batch Starting:- Saturday, 31st August’2019

Course Outline

Getting Started with Excel

  • Introduction to Excel 2013/2016/2019/Office 365
  • Application Interface and Key Components of Excel
  • Navigating Through Excel Ribbon Tabs
  • Exploring Important Excel Options*
  • Live Session Exercise
  • Splitting data of Single Column into multiple
  • 10 Examples to use Auto-fill and Flash Fill
  • Magics of Go-To Special
  • Merge/Unmerge Cells & Wrap Text
  • Extracting Unique Values & Important Ribbon, General and Data Entry Keyboard Shortcuts

Formatting Essentials

  • Formatting Essentials Introduction
  • Custom Cell Number Formats
  • Custom Date/Time Formats
  • Working with Comments / Notes
  • Format Painter – A Quick way to copy ‘Formatting Attribute’
  • Paste Special
  • Table, Table Styles & Formatting
  • Freeze Panes

Functions & Formulas

  • Introduction to Excel Functions and Formulas
  • Basics of Functions & Formulas
  • Working with Cell References Types
  • Most Used Basics & Advanced Functions & Formulas
  • Working with Array Formulas
  • Creating Customize Formulas Step-by-Step with Live examples
  • Creating and Working with Dynamic Ranges using Function and Excel Table features
  • Formulas Debugging / Formulas Auditing
  • Types of Formula Errors / Error Handling Tricks
  • Text Functions: – CLEAN, CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, SEARCH, SUBSTITUTE, and TEXT etc.
  • Date & Time Functions: – DATE, DAYS, TIME, NOW, WEEKNUM, WORKDAY, and WORKDAY.INTL etc.
  • Math & Trig Functions: – INT, MOD, ROUND, ROUNDDOWN, SUMIF, SUMIFS, SUMPRODUCT etc.
  • Statistical Functions: – AVERAGE, COUNT, COUNTA, COUNTBLANK, MAX, MIN, LARGE etc.
  • Logical Functions: – IF, IFS, AND, OR, and IFERROR.
  • Lookup & Reference Functions: – FORMULATEXT, VLOOKUP, HLOOKUP, INDEX, MATCH, INDIRECT, and OFFSET
  • Newly Introduced Functions in Recent Version of Excel*: – CONCAT, TEXTJOIN, IFS, SWITCH, DGET, UNIQUE, FILTER etc.
  • Nested Conditions/Customize Formulas*

Data Analysis

  • Data Sorting
  • Data Filtering
  • Named Ranges
  • 10 different ways to use Conditional Formatting
  • 10 different use of Data Validation
  • What-If Analysis

Excel Charts

  • Introduction to Excel Charts
  • Exploring most commonly used Charts and Templates
  • Basics of Charts
  • Selecting Requirement based Charts
  • Working with Basic Charts:
  • Creating Customized / Advanced Charts
  • Creating Dynamic Chart
  • Working with Dynamic Interactive Charts in Excel using Drop Down
  • Working with Chart Elements, Formatting, Chart Styles, Properties etc.

Pivot Tables

  • Introduction to Pivot Table
  • Creating a Pivot Table
  • Use of Calculated Fields/Items
  • Pivot Table Formatting
  • Grouping Items & Summarizing data in Pivot Tables
  • Grouping and Bucketing data in Pivot Table
  • Changing/Modifying Data Sources
  • Working with Pivot Table Designs & Layouts
  • Exploring Important Pivot Table Options & Field Settings
  • Pivot Table Filters
  • Changing Pivot Table Summary Calculation
  • Use of Slicers in Pivot Table
  • Using Source Data to Convert into Infographic Summary
  • Introduction to Pivot Charts

Bonus Module

Getting Started with VBA

  • Quick Recap of Microsoft Excel
  • Introduction to Developer Ribbon Tab
  • Introduction to VBA
  • What is VBA / Macro in Excel?
  • Introduction to Visual Basic Editor Window
  • Getting Familiar with Visual Basic Editor (VBE)
  • Customizing the VBE Environment
  • The Macro Recorder
  • Executing / Modifying Recorded Macros
  • Saving Macro Workbook

VBA Nuts & Bolts 

  • Introduction to Sub and Function Procedures
  • Introducing the Excel VBA Object Model (A must know thing to become Pro in VBA)
  • Properties and Methods
  • Important and Useful Properties of Application Object
  • Working with Ranges Objects
  • Working with Comments
  • Variable, Data Types and Constant
  • Finding Last or Next available Row/Column
  • Selecting Rows/Columns/Table (Normally & Dynamically)

Working with Loops, Conditional Statements & Arrays

  • Introduction to VBA Loops & Conditional Statements
  • Working with Conditional Statements
  • Working with Loops
  • Working with Message & Inbox Box
  • With-End with Constructs
  • Controlling Program Flow
  • Introduction to Arrays

Formula Writing in VBA

  • Introduction to Excel VBA Functions and Worksheet Functions
  • Difference between R1C1 and A1 style referencing
  • Difference between Worksheet Functions Vs Active.cell Formula
  • Getting started with VBA Excel Functions
  • Getting started with WorksheetFunction
  • Working with Function Procedures
  • Getting started with User Defined Functions (UDF)
  • Fixing arguments of Formulas in VBA
  • Working and Creating Customize Functions
  • Error Handling & Debugging Techniques

UserForm & GUI

  • User Form Introduction
  • Exploring Form Controls & ActiveX Controls
  • Exploring UserForm Controls
  • Cycle/Sequence to follow to create UserForm
  • Validating User Inputs
  • Testing a UserForm
  • User Form Tricks
  • Understanding User Form Events

Working with Excel Features

  • Working with Pivot Tables
  • Charts
  • Sorting & Filtering

Interaction with Other Applications (Bonus)

  • Add-Inns
  • Type of different Excel events
  • Starting an application from Excel
  • Interacting with Microsoft Office Apps
  • Controlling Excel from another application
  • Sending Personalized emails via Outlook
  • Sending e-mail attachments from Excel
  • Working with Files & Folders
  • Converting / Saving Excel File into PDF

Getting Started with Dashboard Introduction

  • Introduction to different type of Excel Dashboards
  • How to deal with raw data – Data Cleaning , Data Formatting etc.
  • What is KPI’s and why it is important to create dashboard
  • Understand management requirement
  • Purpose of Dashboard creation
  • Session Study Material

Customize Formulas – 50+ Formulas

Custom Formatting               

  • Conditional Formatting
  • Custom Formatting Date, Numbers, Currency etc.
  • Alignments, Height & Width, Uses of Colours, Insert Dashboard Related Pictures

10 different ways to use of Data Validation            

Customize Charts & Pivot Tables

  • How & where we should use charts in Dashboard?
  • 10+ Customize Chart such as Combo Chart, Pareto Analysis Charts, Thermometer Chart, Waterfall Chart, Gauge Chart etc.
  • Interactive Chart with Form Control                                                  

Dashboards Creation

  • Financial Dashboard / HR Dashboard / Operation Dashboard / Sales Dashboard

Getting started with Power BI Service          

  • Get Power BI Tools
  • Introduction to Tools and Terminology
  • Dashboard in Minutes
  • Refreshing Power BI Service Data
  • Interacting with your Dashboards
  • Sharing Dashboards and Reports
  • Recap and What’s Next

Getting and Transforming Data with Power BI Desktop       

  • Introduction to Power BI Desktop
  • Getting Data: Excel vs Power BI Desktop & Service
  • Data Structure for Q&A
  • DirectQuery vs Import Data
  • Recap and What’s Next

Modeling with Power BI        

  • Introduction to Modeling
  • Setup and Manage Relationships
  • Cardinality and Cross Filtering
  • Default Summarization & Sort by
  • Creating Calculated Columns
  • Creating Measures & Quick Measures
  • Recap and What’s Next

Power BI Desktop Visualisations       

  • Creating Visualisations
  • Color Formatting
  • Setting Sort Order
  • Scatter & Bubble Charts & Play Axis
  • Tooltips
  • Slicers, Timeline Slicers & Sync Slicers
  • Cross Filtering and Highlighting
  • Visual, Page and Report Level Filters
  • Drill Down/Up
  • Hierarchies
  • Reference/Constant Lines
  • Tables, Matrices & Conditional Formatting
  • KPI’s, Cards & Gauges
  • Map Visualizations
  • Custom Visuals
  • Managing and Arranging
  • Drillthrough
  • Custom Report Themes
  • Grouping and Binning
  • Selection Pane, Bookmarks & Buttons
  • Recap and What’s Next

Power BI Service Visualisation Tools 

  • Introduction
  • Standalone Tiles
  • Data Driven Alerts
  • Quick and Related Insights
  • Custom Q&A questions

Publishing and Sharing          

  • Introduction
  • Sharing Options Overview
  • Publish from Power BI Desktop
  • Publish to Web
  • Share Dashboard with Power BI Service
  • Workspaces and Apps (Power BI Pro)
  • Content Packs (Power BI Pro)
  • Print or Save as PDF
  • Row Level Security (Power BI Pro)
  • Export Data from a Visualization
  • Publishing for Mobile Apps
  • Export to PowerPoint
  • Sharing Options Summary

Refreshing Datasets   

  • Understanding Data Refresh
  • Personal Gateway (Power BI Pro and 64-bit Windows)
  • Replacing a Dataset
  • Troubleshooting Refreshing

Power BI and Excel Together 

  • Options for Publishing from Excel
  • Pin Excel Elements to Power BI
  • Connect to Data using Power BI Publisher/Analyze in Excel
  • Excel 2016 Publish: Upload and Export to Power BI
  • Sharing Published Excel Dashboards

Getting Started with Tableau

  • Getting Started with Overview
  • The Tableau Interface
  • Distributing and Publishing

Tableau Prep  

  • Getting Started with Tableau Prep
  • The Tableau Prep Interface
  • The Input Setup
  • The Cleaning Step
  • Group & Replace
  • The Profile Pane
  • The Pivot Step
  • The Aggregate Step
  • The Join Step
  • The Union Step
  • The Output Step

Connecting to Data    

  • Getting Started with Data
  • Managing Metadata
  • Managing Extracts
  • Saving & Publishing Data Source
  • Data Prep with Text and Excel Files
  • Join Types & Union
  • Data Blending
  • Additional Data Blending
  • Connecting to PDF’s
  • Connecting to Cubes

Visual Analytics       

  • Getting Started with Visual Analytics
  • Drill Down & Hierarchies
  • Sorting
  • Grouping
  • Additional Ways to Group
  • Creating Sets
  • Working with Sets
  • Set Actions
  • Ways to Filter
  • Using the Filter Shelf
  • Interactive Filters
  • Where Tableau Filters
  • Additional Filtering Topics
  • Parameters
  • Formatting
  • The Formatting Pane
  • Basic Tooltips
  • Viz in Tooltip
  • Trend Lines
  • Reference Lines
  • Forecasting
  • Clustering
  • Analysis with Cubes and MDX

Dashboards and Stories  

  • Getting Started with Dashboards & Stories
  • Building a Dashboard
  • Dashboard Objects
  • Dashboard Formatting
  • Dashoard Interactivity Using Actions
  • Dashboard Extensions
  • Device Designer
  • Story Points

Mapping        

  • Getting Started with Mapping
  • Maps in Tableau
  • Editing Unrecognized Locations
  • Spatial Files
  • The Density Mark Type (Heat Maps)
  • Expanding Tableau’s Mapping Capabilities
  • Custom Geocoding
  • Polygon Maps
  • Mapbox Integration
  • WMS: Web Mapping Services
  • Background Images

Calculations   

  • Getting Started with Calculations
  • Calculation Syntax
  • Introduction to LOD Expressions
  • Intro to Table Calculations
  • Aggregate Calculations
  • Date Calculations
  • Logic Calculations
  • String Calculations
  • Number Calculations
  • Type Calculations
  • Conceptual Topics with LOD Expressions
  • Aggregation and Replication with LOD Expressions
  • Nested LOD Expressions
  • How to Integrate R and Tableau
  • Using R within Tableau

Why is Tableau doing that?  

  • Understanding Pill Types
  • Measure Names and Measure Values
  • Aggregation, Granularity and Ratio Calculations
  • When to Blend and When to Join
  • Filtering for Top Across Panes

How to

  • Using a Parameter to Change Fields
  • Finding the Second Purchase Date with LOD Expressions
  • Cleaning Data by Bulk Re-aliasing
  • Bollinger Bands
  • Bump Charts
  • Control Charts
  • Funnel Charts
  • Step and Jump Lines
  • Pareto Charts
  • Waterfall Charts

Publish to Tableau Online    

  • Publish to Tableau Online
  • Device Designer
  • Introduction to the Tableau Javascript API

Publish to Tableau Server    

  • Publishing to Tableau Server
  • Device Designer
  • Introduction to the Tableau Javascript API