📜 Advanced Excel Syllabus

🔤 Text Functions

• 🔢 IsNumber – Check if a value is a number

• 📝 IsText – Check if a value is text

• ✂️ Trim – Remove extra spaces from text

• ⬅️ Left – Extract a substring from the left

• ➡️ Right – Extract a substring from the right

• 🔍 Find – Find a substring (case-sensitive)

• 🔎 Search – Find a substring (case-insensitive)

• 📏 Mid – Extract text from the middle

• 🔢 Len – Count characters in a text

• 🔠 Upper – Convert text to uppercase

• 🔡 Lower – Convert text to lowercase

• 🔤 Char – Get the character from an ASCII code

• 🔢 Code – Get the ASCII code of a character

• 🔗 Concatenate – Combine multiple text strings

• 💲 Dollar – Format a number as currency

• 🔢 Fixed – Round a number with fixed decimals

• 🆕 Proper – Capitalize the first letter of each word

• 🔄 Substitute – Replace specific text in a string

• 🔁 Replace – Replace text based on position

• 🔢 Rept – Repeat a text multiple times

• 🧹 Clean – Remove non-printable characters

• 🔗 Join Strings – Merge multiple text values

• 🔢 Counting Words – Count words in a cell

• 🔍 Number of Instances – Count occurrences of a substring

• 📊 Text to Columns – Split text into columns

• ⚖️ Compare Text – Check if two text values match

• ✅ Exact – Compare two strings exactly

📌 Reference Functions

• 🔢 Row – Get the row number of a cell

• 📊 Row in Array Formula – Use row function inside arrays

• 🔢 Column – Get the column number of a cell

• 📊 Column in Array Formula – Use column function inside arrays

• 📍 Address – Get the address of a cell

• 📏 Offset – Reference a cell by offsetting rows/columns

• 🔄 Transpose – Convert rows to columns and vice versa

• 📌 Indirect – Create a reference from text

• 🔗 Hyperlink – Create a clickable link

• 📊 GetPivotData – Extract data from a Pivot Table

🔍 Lookup Functions

• 🎯 Choose – Select a value from a list

• 🔎 Vlookup – Find a value in a vertical range

• 🔎 Hlookup – Find a value in a horizontal range

• 📋 Index – Get a value from a range

• 📋 Index (Two-Dimensional) – Retrieve values from a table

• 📋 Index (Three-Dimensional) – Retrieve values across multiple sheets

• 🎯 Match – Find the position of a value

• 🔄 Lookups with Match – Combine lookup functions

⚠️ Error Functions

• ❌ IsErr – Check for any error except #N/A

• ⚠️ IsError – Check for any error

• 🚫 IsNA – Check if the error is #N/A

✅ Logical Functions

• 🤔 If – Perform conditional logic

• 🏗 Nested Ifs – Use multiple If conditions

• ✅ True – Returns TRUE

• ❌ False – Returns FALSE

• 🛑 And – Check if multiple conditions are TRUE

• 🔄 Or – Check if at least one condition is TRUE

• 🚫 Not – Reverse a logical condition

📅 Date Functions

• 📆 Date – Create a date value

• 📅 Today – Get today’s date

• ⏰ Now – Get current date and time

• 🔢 Day – Extract the day from a date

• 🔢 Month – Extract the month from a date

• 🔢 Year – Extract the year from a date

• 🏢 Networkdays – Count working days between two dates

• 📆 Weekday – Get the day of the week

• 📆 Workday – Get a future/past working day

• 🔢 DATEDIF – Calculate difference between dates

🔢 Array and Summarizing Functions

• 🔄 Entering Array Formulas – Work with multiple cell formulas

• ✅ Benefits of using Array Formulas – Speed and efficiency

• ➕ Sumproduct for Multiple Condition Summarization

• 🔀 Sumproduct with And & Or Conditions

• ➕ SumIf – Summarize with one condition

• 🔢 Countif – Count based on one condition

• ➕ Sumifs – Summarize with multiple conditions

• 🔢 Countifs – Count with multiple conditions

• ⚠️ Resolving Version Conflicts

• 📊 Subtotal – Calculate subtotals

• 🃏 Using Wildcards – Handle partial matches

💰 Financial Functions

• 💳 PMT – Calculate loan payments

• 💰 FV – Future value of an investment

• 💲 PV – Present value of an investment

• 📈 RATE – Calculate interest rate

🎲 Random Functions and Concepts

• 🎲 Rand Between – Generate random numbers

• 🎰 Rand – Generate a random decimal

• 🔢 Random Numbers – Create randomized lists

• 📆 Random Dates – Generate random dates

• ⏰ Random Time – Generate random time

• 🔤 Random Characters – Generate random text

• 🔗 Generating Dependent Random Texts

🎛 Tools

• 📊 Text to Columns Using Form Controls

• 🎚 Option Button – Create form controls

• ⬇️ Drop Down – Create selection lists

• 🔄 Scroll Bars – Use scroll controls

• 🎨 Conditional Formatting – Format cells dynamically

• 🌡 Creating Heat Chart – Visualize data with colors

• 📊 Using Data Bars – Create in-cell bar charts

📜 Using Macro Recorder

• 🎥 Recording Macro – Automate tasks

• 🎛 Assigning Macro to Buttons – Run macros with buttons

• ⌨️ Assigning Shortcut Keys to Macro – Quick macro execution

• ⚠️ Points to be Noted While Using Macro Recorder

📊 AutoFilter and Sorting

• 🔽 Filtering Data – Show only what’s needed

• 📈 Sorting Data – Arrange data in order

• 📋 Sorting by Custom List – Custom sorting rules

• 🔄 Sorting by Rows – Sort horizontally

📊 Advanced Filter

• 📌 Getting Unique List – Remove duplicates

• 🔗 Getting Unique Combinations – Filter complex lists

• 🎯 Getting Only Desired Columns – Extract specific data

• 🎯 And / Or Criteria – Advanced filtering

📊 Pivot Tables and Charts

• 📊 Creating Pivot Table – Summarize large data

• 🎭 Summarizing Data into Years/Qtrs/Months

• 📊 Creating Pivot Charts – Visualize pivot data

• 🔢 Using Calculated Fields – Custom calculations

🔗 Formula Auditing

• 🔄 Trace Precedent – Identify cell dependencies

• 🔄 Trace Dependent – Find which cells use a formula

• ❌ Clear Arrows – Remove trace visuals

🕒 Hour 1: Introduction to Tableau & Data Connections

• ❓ What is Tableau? – An overview of Tableau's capabilities

• 🖥️ Tableau Products Overview (Public, Desktop, Server, Cloud) – Understand the various Tableau offerings

• 🔗 Connecting to Different Data Sources (Excel, CSV, Databases) – Learn how to connect to multiple data sources

• 🔄 Understanding Live vs. Extract Connections – Compare real-time connections with data extracts

🕒 Hour 2: Data Preparation & Exploration

• 📂 Data Pane Overview (Dimensions vs. Measures) – Distinguish between dimensions and measures

• 🔍 Filtering & Sorting Data – Master the art of data organization

• 🏗️ Creating Hierarchies & Groups – Organize data into meaningful groups and hierarchies

• ⚙️ Working with Data Types & Changing Field Properties – Customize your data for analysis

🕒 Hour 3: Basic Chart Types & Visualizations

• 📊 Creating Bar Charts, Line Charts, and Pie Charts – Build foundational visualizations

• 🔥 Working with Heatmaps & Highlight Tables – Visualize data intensity and comparisons

• 🔀 Using Dual-Axis Charts & Combined Charts – Learn to overlay multiple data sets effectively

🕒 Hour 4: Calculations & Aggregations

• ➕ Understanding Aggregations (SUM, AVG, COUNT) – Grasp the basics of summarizing data

• 🧮 Basic Calculated Fields – Create custom calculations to enrich your analysis

• 📈 Introduction to Table Calculations (Running Total, Percent of Total) – Dive into advanced data computations

🕒 Hour 5: Formatting & Enhancing Visuals

• 🎨 Customizing Colors & Labels – Enhance visuals with tailored aesthetics

• 💬 Using Annotations & Tooltips – Add context and insights directly on your visualizations

• ⚡ Conditional Formatting – Dynamically format visuals based on data criteria

🕒 Hour 6: Filters, Parameters & Sorting

• 🔍 Applying Quick Filters & Context Filters – Quickly narrow down your data view

• ⚙️ Creating Parameters for Dynamic Interactions – Empower users with interactive controls

• 🔀 Sorting Data Dynamically – Arrange data in real-time based on user input

🕒 Hour 7: Building Interactive Dashboards

• 🖥️ Creating a Basic Dashboard Layout – Assemble your visualizations into a coherent dashboard

• 🕹️ Adding Filters & Actions for Interactivity – Enable user-driven exploration within your dashboard

• 📦 Using Containers & Floating Elements – Optimize dashboard design with layout tools

🕒 Hour 8: Hands-on Project & Best Practices

• 🚀 Mini Project: Create a Dashboard with Sample Data – Apply your skills in a practical project

• 📤 Publishing & Sharing Options – Learn how to distribute your dashboards effectively

• ⚡ Best Practices for Performance Optimization – Ensure your dashboards run smoothly and efficiently

🐬 Introduction to MySQL

• 📖 Introduction to Databases

• 🗄 Introduction to RDBMS

• 🔄 Explain RDBMS through Normalization

• 🔍 Different Types of RDBMS

• 💾 Software Installation (MySQL Workbench)

📝 SQL Commands and Data Types

• ⚙️ Types of SQL Commands (DDL, DML, DQL, DCL, TCL) and Their Applications

• 🔢 Data Types in SQL (Numeric, Char, Datetime)

🔎 DQL & Operators

• 🔍 SELECT

• ⏩ LIMIT

• 🌟 DISTINCT

• 🔍 WHERE AND

• 🔀 OR

• 📥 IN

• 🚫 NOT IN

• ↔️ BETWEEN

• ✅ EXIST

• ❓ ISNULL

• ❌ IS NOT NULL

• 🔤 Wild Cards

• 🔄 ORDER BY

🔄 Case When Then and Handling NULL Values

• 🔀 Usage of Case When Then to Solve Logical Problems

• ❓ Handling NULL Values (IFNULL, COALESCE)

📊 Group Operations & Aggregate Functions

• 🔢 Group By

• 🔍 Having Clause

• 🔢 COUNT

• ➕ SUM

• 📈 AVG

• 🔻 MIN

• 🔺 MAX

• 🔤 COUNT String Functions

• ⏰ Date & Time Functions

🚧 Constraints

• 🚫 NOT NULL

• 🔒 UNIQUE

• ✔️ CHECK

• 🆕 DEFAULT

• 🔑 Primary Key

• 🔗 Foreign Key (Both at Column Level and Table Level)

🔀 Joins

• 🤝 Inner Join

• ⬅️ Left Join

• ➡️ Right Join

• 🔀 Cross Join

• 🔄 Self Join

• 🌐 Full Outer Join

📐 DDL (Data Definition Language)

• 🆕 Create

• ❌ Drop

• 🔄 Alter

• 🔀 Rename

• 🚮 Truncate

• 🔧 Modify

• 💬 Comment

🔄 DML & TCL Commands

• ✍️ DML Commands: Insert, Update & Delete

• 🔄 TCL Commands: Commit, Rollback, Savepoint

• 📂 Data Partitioning

📑 Indexes and Views

• 🔍 Indexes (Different Types of Indexes)

• 👁 Views in SQL

• ⚙️ Stored Procedures

🛠 Stored Procedures with Parameters

• 🔧 Procedure with IN Parameter

• 🔄 Procedure with OUT Parameter

• 🔀 Procedure with INOUT Parameter

🔢 Functions and Constructs

• 👤 User-Defined Functions

• 📊 Window Functions

  • 🏆 Rank

  • 🎖 Dense Rank

  • ⬆️ Lead

  • ⬇️ Lag

  • 📝 Row_Number

🔗 Union, Intersect, Sub-Query

• 🔗 Union, Union All

• 🔄 Intersect

• 🔍 Sub-Queries, Multiple Query

⚠️ Exception Handling

• 🚨 Handling Exceptions in a Query

• 🔄 CONTINUE Handler

• 🚪 EXIT Handler

🔥 Triggers

• ⏰ Triggers - Before | After DML Statement