1. Introduction

• MS Office versions: similarities and differences
• Interface of the latest available version
• Rows and columns
• Keyboard shortcuts for easy navigation
• Data entry using Fill Series
• Find and Select
• Clear options
• Ctrl + Enter
• Formatting options
  
  • Font
  • Alignment
  • Clipboard
  • Copy
  • Paste Special

2. Referencing, Named Ranges, Uses, and Arithmetic Functions

• Mathematical calculations with cell referencing
  
  • Absolute reference
  • Relative reference
  • Mixed reference
• Functions with named ranges

3. Logical and Lookup Functions

• LOOKUP
• VLOOKUP
• Nested VLOOKUP
• HLOOKUP
• INDEX
• Arithmetic functions
  
  • SUM
  • SUMIF
  • SUMIFS
  • COUNT
  • COUNTA
  • COUNTIFS
  • AVERAGE
  • AVERAGEIFS
  • MAX
  • MAXIFS
  • MIN
  • MINIFS
• Logical functions
  
  • IF
  • AND
  • OR
  • Nested IFs
  • NOT
  • IFERROR
• Usage of mathematical and logical functions nested together

4. Referring Data from Different Tables

• Various types of Lookup
• Nested IF
• INDEX with MATCH function
• INDIRECT
• OFFSET

5. Advanced Functions

• Combination of arithmetic functions
• Combination of logical functions
• Combination of lookup functions
• Data validation with dependent dropdown

6. Date and Text Functions

Date Functions

• DATE
• DAY
• MONTH
• YEAR
• YEARFRAC
• DATEDIF
• EOMONTH

Text Functions

• TEXT
• UPPER
• LOWER
• PROPER
• LEFT
• RIGHT
• SEARCH
• FIND
• MID
• TTC
• Flash Fill

7. Data Handling

• Data cleaning
• Data type identification
• Remove duplicates
• Formatting and filtering
• Number formatting with shortcuts
• Ctrl + T: converting data into an Excel table
• Formatting table
• Remove duplicates
• SORT
• Advanced Sort
• FILTER
• Advanced Filter

8. Data Visualization

Conditional Formatting

• Icon sets
• Highlighted colour sets
• Data bars
• Custom formatting

Charts

• Bar chart
• Column chart
• Line chart
• Scatter chart
• Combo chart
• Gantt chart
• Waterfall chart
• Pie chart

9. Data Summarization: Pivot Reports and Charts

• Pivot reports
  
  • Insert Pivot Table
  • Pivot interface
  • Crosstable reports
  • Filters
  • Pivot charts
• Slicers
  
  • Add slicers
  • Connect slicers to multiple reports and charts
• Calculated field
• Calculated item

10. Data Summarization: Dashboard Creation, Tips, and Tricks

• Dashboard types
• Getting reports and charts together
• Use of slicers
• Power Query interface
• Power Query tabs
• Data cleaning
• Data transformation
• Design and placement
  
  • Formatting tables
  • Formatting charts
  • Formatting sheets
  • Proper use of colours and shapes

11. Connecting to Data: Power Query, Pivot, and Power Pivot

• Connecting data from other Excel files
• Connecting data from text files
• Connecting data from other sources
• Loading data into Excel Query
• Using loaded queries
• Merge and Append
• Insert Power Pivot
• Similarities and differences between Pivot and Power Pivot reporting
• Getting data from databases
• Getting data from workbooks
• Getting data from webpages

12. VBA and Macros

• View tab
• Add Developer tab
• Record Macro
  
  • Macro name
  • Macro storage
• Record macro to format table using absolute reference
• Format table of any size using relative reference
• Play macro by button
• Play macro by shape
• Play macro as command in a new tab
• Editing macros

13. VBA Basics

• Introduction to the basics of working with VBA for Excel
• Subs
• Ranges
• Sheets
• Comparing values and conditions
• IF statements
• Select Case
• Repeat processes using loops
  
  • For loops
  • Do While loops
  • Do Until loops
• Communicating with end-users
  
  • Message boxes
  • Input boxes
  • User Forms

1. Introduction to MySQL

• Introduction to databases
• Introduction to RDBMS
• Explanation of RDBMS through normalization
• Different types of RDBMS
• Software installation
  
  • MySQL Workbench

2. SQL Commands and Data Types

• Types of SQL commands and their applications
  
  • DDL
  • DML
  • DQL
  • DCL
  • TCL
• Data types in SQL
  
  • Numeric
  • Character
  • Date and time

3. DQL and Operators

• SELECT
• LIMIT
• DISTINCT
• WHERE
• AND
• OR
• IN
• NOT IN
• BETWEEN
• EXISTS
• IS NULL
• IS NOT NULL
• Wildcards
• ORDER BY

4. CASE WHEN THEN and Handling NULL Values

• Usage of CASE WHEN THEN to solve logical problems
• Handling NULL values
  
  • IFNULL
  • COALESCE

5. Group Operations and Aggregate Functions

• GROUP BY
• HAVING clause
• COUNT
• SUM
• AVG
• MIN
• MAX
• String functions
• Date and time functions

6. Constraints

• NOT NULL
• UNIQUE
• CHECK
• DEFAULT
• Primary Key
• Foreign Key
  
  • Column-level foreign key
  • Table-level foreign key

7. Joins

• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• CROSS JOIN
• SELF JOIN
• FULL OUTER JOIN

8. DDL Commands

• CREATE
• DROP
• ALTER
• RENAME
• TRUNCATE
• MODIFY
• COMMENT

9. DML and TCL Commands

DML Commands

• INSERT
• UPDATE
• DELETE

TCL Commands

• COMMIT
• ROLLBACK
• SAVEPOINT
• Data partitioning

10. Indexes and Views

• Indexes
  
  • Different types of indexes
• Views in SQL

11. Stored Procedures

• Procedure with IN parameter
• Procedure with OUT parameter
• Procedure with INOUT parameter

12. Functions and Constructs

• User-defined functions
• Window functions
• RANK
• DENSE_RANK
• LEAD
• LAG
• ROW_NUMBER

13. Union, Intersect, and Sub-query

• UNION
• UNION ALL
• INTERSECT
• Subqueries
• Multiple queries

14. Exception Handling

• Handling exceptions in a query
• CONTINUE handler
• EXIT handler

15. Triggers

• Triggers before DML statement
• Triggers after DML statement

1. Introduction to Tableau

• What is Tableau?
• What is Data Visualization?
• Tableau products
• Tableau Desktop variations
• Tableau file extensions
• Data types
• Dimensions
• Measures
• Aggregation concept
• Tableau Desktop installation
• Data source overview
• Live vs Extract

2. Basic Charts and Formatting

• Overview of worksheet sections
• Shelves
• Bar Chart
• Stacked Bar Chart
• Discrete Line Chart
• Continuous Line Chart
• Symbol Map
• Filled Map
• Text Table
• Highlight Table
• Formatting options
  
  • Remove grid lines
  • Hide axes
  • Convert numbers to thousands and millions
  • Shading
  • Row divider
  • Column divider
• Marks Card

3. Filters

• What are filters?
• Types of filters
  
  • Extract filter
  • Data source filter
  • Context filter
  • Dimension filter
  • Measure filter
  • Quick filter
• Order of operation of filters
• Cascading filters
• Apply filters to worksheets

4. Calculations

• Need for calculations
• Types of calculations
  
  • Basic calculations
  • LOD calculations
  • Table calculations
• Examples of basic calculations
  
  • Aggregate functions
  • Logical functions
  • String functions
  • Tableau calculation functions
  • Numerical functions
  • Date functions
• LOD calculations with examples
• Table calculations with examples

5. Data Combining Techniques

• What are data combining techniques?
• Types of data combining techniques
  
  • Joins
  • Relationships
  • Blending
  • Union

6. Custom Charts

• Dual Axis
• Combined Axis
• Donut Chart
• Lollipop Chart
• KPI Cards
  
  • Simple KPI Cards
  • KPI Cards with Shape

7. Groups, Bins, Hierarchies, Sets, and Parameters

• Groups
  
  • Purpose of groups
• Bins
  
  • Purpose of bins
• Hierarchies
  
  • Purpose of hierarchies
• Sets
  
  • Purpose of sets
• Parameters
  
  • Purpose of parameters
  • Examples of parameters

8. Analytics and Dashboard

• Reference Lines
• Trend Line
• Overview of Dashboard
  
  • Tiled layout
  • Floating layout
• Overview of all dashboard objects
• Layout overview
• Dashboard creation with formatting

9. Dashboard Actions and Tableau Public

• Dashboard actions
  
  • Filter action
  • Highlight action
  • URL action
  • Sheet action
  • Parameter action
  • Set action
• How to save the workbook to Tableau Public website?