Data Science Course — Zero to Hero

Chapter 1: Excel Basics

• Interface Overview: Ribbon, Quick Access Toolbar, Worksheets, Cell Rows, Columns.
• Data Entry: Typing, editing, and navigating data.
• Formatting: Font styles, borders, number formatting, conditional formatting.
• Shortcuts: Essential keyboard shortcuts for efficiency.

Chapter 2: Math Functions

• SUM: Adds numbers in a range.
• AVERAGE: Calculates the mean of numbers.
• MIN/MAX: Finds the smallest/largest value in a range.
• ROUND: Rounds numbers to specified digits.
• SUMIF/SUMIFS: Adds numbers based on single/multiple conditions.
• SUBTOTAL: Performs calculations on filtered data.

Chapter 3: Text Functions

• CONCATENATE: Combines text from multiple cells.
• LEFT/RIGHT/MID: Extracts substrings from text.
• LEN: Counts characters in a cell.
• TRIM: Removes extra spaces from text.
• UPPER/LOWER/PROPER: Changes text case.
• FIND/SEARCH: Locates a substring within text.
• REPLACE/SUBSTITUTE: Replaces text in a string.
• TEXT: Converts numbers to text with formatting.

Chapter 4: Statistical Functions

• COUNT/COUNTA: Counts numbers/nonempty cells.
• COUNTIF/COUNTIFS: Counts cells based on single/multiple conditions.
• AVERAGEIF/AVERAGEIFS: Averages cells based on conditions.
• MEDIAN: Finds the middle value in a range.
• MODE: Finds the most frequent value.

Chapter 5: Logical Functions

• IF: Performs conditional checks.
• AND/OR: Combines multiple conditions.
• IFERROR: Handles errors in formulas.
• IFS: Checks multiple conditions without nesting.
• SWITCH: Evaluates multiple conditions and returns a value.

Chapter 6: Lookup Functions

• VLOOKUP: Finds data in a table vertically.
• HLOOKUP: Finds data in a table horizontally.
• XLOOKUP: Modern replacement for VLOOKUP and HLOOKUP.
• CHOOSE: Selects a value from a list based on an index.
• INDEX/MATCH: Flexible alternative to VLOOKUP.

Chapter 7: Date & Time Functions

• TODAY/NOW: Returns the current date/time.
• DATE: Creates a date from year, month, and day.
• DATEDIF: Calculates the difference between dates.
• EOMONTH: Returns the last day of the month.
• NETWORKDAYS: Calculates working days between dates.

Chapter 8: Advanced Tools

• Pivot Tables: Summarizes and analyzes large datasets.
• Power Query: Cleans, transforms, and merges data.
• Data Validation: Restricts data entry to specific criteria.
• What-If Analysis: Scenario Manager, Goal Seek, Data Tables.
• Solver: Optimizes solutions for complex problems.
• Macros: Automates repetitive tasks.
• Dynamic Arrays: Spills results across multiple cells.

Chapter 1: Introduction to VBA

• What is VBA?: Overview of Visual Basic for Applications.
• VBA Editor: Navigating the interface, modules, and procedures.
• Macro Recording: Automating repetitive tasks using the Macro Recorder.
• Security Settings: Enabling and disabling macros.

Chapter 2: VBA Basics

• Variables and Data Types: Declaring and using variables (Integer, String, Bool, etc.).
• Operators: Arithmetic, comparison, and logical operators.
• Procedures: Subroutines and functions.
• Comments: Adding comments for code clarity.

Chapter 3: Control Structures

• If...Then...Else: Conditional statements.
• Select Case: Multiple conditional checks.
• For...Next: Looping through a range of values.
• Do While/Do Until: Looping based on conditions.
• For Each: Looping through collections (e.g., cells, worksheets).

Chapter 4: Working with Excel Objects

• Workbook and Worksheet Objects: Opening, closing, and navigating workbooks/worksheets.
• Range Objects: Selecting, formatting, and manipulating cells.
• Cell Referencing: Absolute and relative referencing.
• Collections: Working with groups of objects (e.g., all sheets in a workbook).

Chapter 5: Optimizing VBA Code

• Code Efficiency: Reducing runtime and memory usage.
• Best Practices: Writing clean, maintainable code.
• Error Prevention: Avoiding common mistakes.

Chapter 1: Introduction to Power BI

• What is Power BI?: Overview of Power BI and its applications.
• Power BI Ecosystem: Power BI Desktop, Service, and Mobile.
• Installing Power BI: Setting up Power BI Desktop.
• Interface Overview: Ribbon, report view, data view, model view.

Chapter 2: Data Import and Transformation

• Data Sources: Importing data from Excel, CSV, SQL, and web sources.
• Power Query Editor: Removing duplicates, filtering rows, splitting columns, merging and appending queries, pivoting and unpivoting data.
• Data Types: Setting correct data types (text, numbers, dates).

Chapter 3: Data Modeling

• Relationships: Creating and managing relationships between tables.
• Star Schema: Designing efficient data models.
• Calculated Columns: Adding new columns using DAX (Data Analysis Expressions).
• Measures: Creating aggregations (e.g., SUM, AVERAGE).
• Hierarchies: Building hierarchies for drilldown analysis.

Chapter 4: DAX (Data Analysis Expressions)

• Basic DAX Functions: SUM, AVERAGE, COUNT, MIN, MAX, CALCULATE, FILTER.
• Logical Functions: IF, AND, OR, SWITCH.
• Text Functions: CONCATENATE, LEFT, RIGHT, MID.
• Date and Time Functions: DATE, YEAR, MONTH, DAY, DATEDIFF.
• Time Intelligence Functions: TOTALYTD, SAMEPERIODLASTYEAR, DATEADD.

Chapter 5: Visualizations

• Basic Charts: Bar charts, line charts, pie charts, and tables.
• Advanced Visuals: Maps, treemaps, waterfalls, funnels, custom marketplace visuals.
• Filters and Slicers: Adding interactive filters to reports.
• Drillthroughs: Enabling detailed analysis on specific data points.
• Tooltips: Customizing tooltips for better insights.

Chapter 6: Report Design and Formatting

• Themes: Applying and customizing themes.
• Layouts: Organizing visuals for clarity and impact.
• Buttons and Bookmarks: Creating interactive navigation.
• Conditional Formatting: Highlighting key data points.
• Mobile Layouts: Optimizing reports for mobile devices.

Chapter 1: Introduction to Google Sheets

• What is Google Sheets?: Overview of Google Sheets and its advantages.
• Google Sheets vs. Excel: Key differences and use cases.
• Interface Overview: Menu bar, toolbar, sheets, and cells.
• Collaboration Features: Real-time editing, sharing, and commenting.

Chapter 2: Basic Functions and Formulas

• Basic Math Functions: SUM, AVERAGE, MIN, MAX, COUNT.
• Text Functions: CONCATENATE, LEFT, RIGHT, MID, LEN, TRIM.
• Logical Functions: IF, AND, OR, NOT, IFERROR.
• Lookup Functions: VLOOKUP, HLOOKUP, INDEX, MATCH.
• Date and Time Functions: TODAY, NOW, DATE, DATEDIF, NETWORKDAYS.

Chapter 3: Data Cleaning and Transformation

• Data Validation: Creating dropdown lists and restricting data entry.
• Conditional Formatting: Highlighting cells based on rules.
• Filter and Sort: Organizing data for better analysis.
• Split Text to Columns: Separating data into multiple columns.
• Remove Duplicates: Cleaning datasets by eliminating duplicates.

Chapter 4: Advanced Functions

• Array Formulas: ARRAYFORMULA, FILTER, UNIQUE, SORT.
• Query Function: Writing SQL-like queries for data analysis.
• Import Functions: IMPORTRANGE, IMPORTDATA, IMPORTXML.

Chapter 5: Data Visualization

• Charts and Graphs: Bar charts, line charts, pie charts, and scatter plots.
• Sparklines: Adding mini-charts within cells.
• Pivot Tables: Summarizing and analyzing large datasets.
• Custom Formulas in Charts: Using formulas to create dynamic visuals.

Chapter 6: Collaboration and Automation

• Sharing and Permissions: Setting view, comment, and edit access.
• Version History: Tracking changes and restoring previous versions.
• Google Apps Script: Automating tasks with JavaScript-based scripts, e.g. email notifications or data updates.

Chapter 7: Integration with Other Tools

• Google Data Studio: Connecting Google Sheets to create interactive dashboards.
• Google Forms: Automatically populating sheets with form responses.
• Google Drive: Storing and organizing sheets in the cloud.

Chapter 1: Introduction to MySQL

• What is MySQL?: Overview of MySQL and its role in data management.
• Relational Databases: Understanding tables, rows, and columns.
• MySQL Workbench: Navigating the interface and tools.
• Installing MySQL: Setting up MySQL on your system.

Chapter 2: Database Design

• Data Types: Strings, numbers, dates, and blobs.
• Primary Keys and Foreign Keys: Ensuring data integrity.
• Normalization: Reducing redundancy through 1NF, 2NF, and 3NF.
• Entity-Relationship Diagrams (ERDs): Visualizing database structure.

Chapter 3: Basic SQL Queries

• SELECT: Retrieving data from tables.
• WHERE: Filtering data based on conditions.
• ORDER BY: Sorting query results.
• LIMIT: Restricting the number of rows returned.
• DISTINCT: Removing duplicate rows.

Chapter 4: Data Manipulation

• INSERT: Adding new records to a table.
• UPDATE: Modifying existing records.
• DELETE: Removing records from a table.
• TRUNCATE: Deleting all records from a table.

Chapter 5: Joins and Relationships

• INNER JOIN: Combining rows with matching values.
• LEFT JOIN / RIGHT JOIN: Returning all rows from one table and matching rows from the other.
• FULL OUTER JOIN: Returning all rows when there is a match in either table.
• Self-Join: Joining a table to itself.

Chapter 6: Aggregation and Grouping

• GROUP BY: Grouping rows based on column values.
• HAVING: Filtering groups based on conditions.
• Aggregate Functions: COUNT, SUM, AVG, MIN, MAX.
• Subqueries: Writing nested queries for complex analysis.

Chapter 7: Advanced SQL Techniques

• Indexes: Improving query performance with indexes.
• Views: Creating virtual tables for simplified querying.
• Stored Procedures: Writing reusable SQL code blocks.
• Triggers: Automating actions based on database events.
• Transactions: Ensuring data consistency with COMMIT and ROLLBACK.

Chapter 8: Data Import and Export

• Importing Data: Loading data from CSV, Excel, or other databases.
• Exporting Data: Saving query results to files.
• Backup and Restore: Creating and restoring database backups.

Chapter 1: Introduction to Python

• What is Python?: Overview of Python and its applications in data analytics.
• Installing Python: Setting up Python and Jupyter Notebook.
• Python Basics: Variables, data types, and operators.
• Writing Your First Program: Hello, World!

Chapter 2: Python Fundamentals

• Control Structures: if, else, elif for decision-making; for and while loops for iteration.
• Functions: Defining and calling functions.
• Data Structures: Lists, tuples, sets, and dictionaries.
• File Handling: Reading from and writing to files.

Chapter 3: Python Libraries

• NumPy: Arrays and array operations, mathematical functions (e.g., np.sum, np.mean).
• Pandas: DataFrames and Series, data manipulation, handling missing data.
• Matplotlib and Seaborn: Line plots, bar charts, histograms, heatmaps.

Chapter 4: Data Cleaning and Preparation

• Handling Missing Data: Filling, dropping, or interpolating missing values.
• Data Transformation: Applying functions, merging and concatenating datasets.
• Data Normalization: Scaling data for analysis.
• Outlier Detection: Identifying and handling outliers.

Chapter 5: Data Analysis with Python

• Descriptive Statistics: Mean, median, mode, standard deviation, correlation and covariance.
• Grouping and Aggregation: groupby in Pandas, aggregating data with sum, mean, count.
• Time Series Analysis: Working with dates and times, resampling and rolling windows.

Chapter 1: Introduction to Tableau

• What is Tableau?: Overview and use cases.
• Tableau Interface: Worksheet, Dashboard, Story.
• Connecting to Data: Excel, CSV, databases.

Chapter 2: Data Preparation and Cleaning

• Data Interpreter and Pivoting.
• Splitting and grouping data.
• Renaming, changing data types.

Chapter 3: Visualizations

• Basic Charts: Bar, Line, Pie, Scatter.
• Maps: Geographic data visualization.
• Advanced Charts: Heatmaps, Tree maps, Gantt Charts, Box Plots.

Chapter 4: Calculations and Parameters

• Calculated Fields: Custom metrics and KPIs.
• Table Calculations: Running total, percent of total, rank.
• Parameters: User-driven dynamic dashboards.

Chapter 5: Filters and Interactivity

• Dimension and Measure Filters.
• Top N Filters and Context Filters.
• Actions: Filter, Highlight, URL.

Chapter 6: Dashboards and Storytelling

• Building Dashboards: Layout and design.
• Interactive Dashboards: Filters, Actions.
• Story Points: Creating data narratives.

Chapter 1: Descriptive Statistics

• Measures of Central Tendency: Mean, Median, Mode.
• Measures of Dispersion: Range, Variance, Standard Deviation.
• Data Distribution: Skewness and Kurtosis.

Chapter 2: Data Visualization

• Histograms, Boxplots, Scatterplots.
• Frequency Distribution and Charts.

Chapter 3: Inferential Statistics

• Population vs. Sample.
• Confidence Intervals.
• Central Limit Theorem.

• Events and Distributions.
• Bayes' Theorem.
• Risk Analysis.

• Regression.
• Classification.
• Clustering.
• Model Evaluation.

Chapter 2: Deep Learning Frameworks

• Introduction to TensorFlow and Keras.

Chapter 3: Convolutional Neural Networks (CNN)

• Image Classification use case.

Chapter 4: Recurrent Neural Networks (RNN)

• Time Series Forecasting.

Chapter 5: Deep Learning Projects

• Handwritten Digit Recognition.
• Stock Price Prediction.

Chapter 1: Text Preprocessing

• Tokenization, Stopwords, Stemming, Lemmatization.

Chapter 2: Text Representation

• Bag of Words, TF-IDF, Word Embeddings.

Chapter 3: Sentiment Analysis

• Using Logistic Regression or Naive Bayes.

Chapter 4: Named Entity Recognition

• Extracting names, places, organizations.

Chapter 5: NLP Projects

• Sentiment Analysis on Tweets.
• Resume Screening Tool.

Chapter 1: Introduction to GenAI

• What is Generative AI?
• Use cases: Text, Image, Code generation.

Chapter 2: LLMs and Transformers

• Understanding GPT, BERT.
• Prompt Engineering Basics.

Chapter 3: Tools & Platforms

• ChatGPT, Gemini, MidJourney and more AI tools.
• Google Colab, Hugging Face.

Chapter 4: Real-World GenAI Projects

• AI-Powered Chatbot.
• Image Caption Generator.
• Resume Builder using AI.
• Marketing Copy Generator.
• Data Summarization Tool.
• Automated Report Writer.
• Voice-to-Text Analysis App.
• Custom Code Assistant.
• Personalized Email Generator.
• Smart Interview Practice Bot.

• Communication and storytelling with data.
• Presentations for technical and non-technical audiences.
• Business use-case framing.

• Resume Building: Highlighting your Data Science skills and projects.
• Common Interview Questions: Preparing for technical and behavioral questions.
• Mock Interviews: Practicing with peers or mentors.
• LinkedIn Profile Optimization.