Data Analytics 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.
• INDEX/MATCH: Flexible alternative to VLOOKUP.
• XLOOKUP: Modern replacement for VLOOKUP and HLOOKUP.
• CHOOSE: Selects a value from a list based on an index.

Chapter 7: Financial Functions

• PMT: Calculates loan payments.
• FV: Calculates the future value of an investment.
• NPV: Calculates the net present value of cash flows.
• IRR: Calculates the internal rate of return.
• RATE: Calculates the interest rate for a loan or investment.
• PV: Calculates the present value of an investment.

Chapter 8: 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 9: 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: User-Defined Functions (UDFs)

• Creating Custom Functions: Writing reusable formulas.
• Passing Arguments: Using parameters in functions.
• Returning Values: Setting function outputs.
• Calling Functions: Using UDFs in Excel sheets.

Chapter 6: Error Handling

• On Error Statements: On Error Resume Next, On Error GoTo.
• Debugging Tools: Breakpoints, Immediate Window, Watch Window.
• Error Messages: Customizing error prompts.

Chapter 7: User Forms and Controls

• Designing User Forms: Adding text boxes, buttons, and labels.
• Event Handling: Writing code for button clicks, form loads, etc.
• Data Validation: Ensuring user input meets criteria.
• Linking Forms to Worksheets: Transferring data between forms and Excel.

Chapter 8: 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: Cleaning and transforming data — 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, and custom visuals from the marketplace.
• 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 7: Power BI Service

• Publishing Reports: Uploading reports to Power BI Service.
• Dashboards: Creating and managing dashboards.
• Sharing and Collaboration: Sharing reports with stakeholders.
• Data Refresh: Setting up scheduled refreshes for live data.

Chapter 8: Advanced Power BI Features

• Power BI Gateway: Connecting to on-premises data sources.
• Power BI Embedded: Integrating Power BI reports into applications.

Chapter 9: Optimizing Power BI Reports

• Performance Tuning: Reducing report load times.
• Best Practices: Designing efficient data models and visuals.

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, e.g. =QUERY(A1:D100, "SELECT A, B WHERE C > 100").
• 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.

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 1: 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: Creating visualizations — line plots, bar charts, histograms, heatmaps.

Chapter 2: 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 3: 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 4: Data Visualization

• Matplotlib: Line plots, scatter plots, bar charts, histograms.
• Seaborn: Heatmaps, pair plots, violin plots, statistical plotting.
• Plotly: Interactive visualizations — 3D plots, animations, dashboards.

Chapter 5: Advanced Python Techniques

• List Comprehensions: Writing concise loops.
• Lambda Functions: Creating anonymous functions.
• Error Handling: Using try, except, and finally.
• Working with APIs: Fetching and parsing data from web APIs.

Chapter 1: Introduction to Trello

• What is Trello?: Overview of Trello and its applications in project management.
• Agile Workflows: Understanding Agile and Scrum methodologies.
• Trello Interface: Boards, lists, and cards.

Chapter 2: Setting Up Trello

• Creating Boards: Organizing projects into boards.
• Adding Lists and Cards: Breaking down tasks into manageable units.
• Labels and Tags: Categorizing tasks for better organization.

Chapter 3: Collaboration and Workflow Management

• Team Collaboration: Adding members, assigning tasks, and setting deadlines.
• Checklists and Attachments: Adding subtasks and files to cards.

Chapter 4: Advanced Features

• Trello Calendar: Tracking deadlines and milestones.
• Trello Dashboards: Visualizing project progress with charts and metrics.

Chapter 5: Real-World Applications

• Project Management: Managing software development, marketing campaigns, or event planning.
• Task Tracking: Organizing personal or team tasks.

Chapter 1: Communication Skills

• Data Storytelling: Presenting insights in a clear and compelling way.
• Visual Aids: Using charts, graphs, and dashboards to support your narrative.
• Tailoring Messages: Adapting your communication style for different audiences.

Chapter 2: Collaboration and Teamwork

• Working in Teams: Building trust and resolving conflicts.
• Cross-Functional Collaboration: Working with technical and non-technical teams.
• Feedback: Giving and receiving constructive feedback.

• Resume Building: Highlighting your data analytics skills and projects.
• Common Interview Questions: Preparing for technical and behavioral questions.
• Mock Interviews: Practicing with peers or mentors.