Excel Full Course for Data Analysis with Projects [ 2 Hours] | Excel Tutorial 2025 - WsCube Tech

1. Summary

This comprehensive 2-hour Excel tutorial by WsCube Tech aims to equip viewers with essential data analysis skills using Microsoft Excel. It covers a wide range of topics, from basic Excel functionalities like formatting, sorting, and filtering, to advanced concepts such as formulas, text functions, logical functions (IF, AND, OR), date & time functions, lookup functions (VLOOKUP, HLOOKUP, XLOOKUP), INDEX+MATCH, conditional formatting, and macros. The course also delves into powerful data analysis tools like Pivot Tables, Slicers, Power Query, and Power Pivot, culminating in data visualization through charts and dashboard creation. The tutorial emphasizes practical application through projects, making it suitable for both beginners and those looking to enhance their Excel proficiency for data-driven decision-making.

2. Key Takeaways

* **Foundational Excel Skills:** Master basic operations like data entry, formatting, sorting, and filtering for efficient data management.

* **Essential Formulas & Functions:** Understand and utilize a variety of functions, including text, logical (IF, AND, OR), date/time, counting/summing based on criteria (COUNTIF, SUMIF), and lookup functions (VLOOKUP, HLOOKUP, XLOOKUP, INDEX+MATCH).

* **Advanced Data Analysis Tools:** Learn to leverage Pivot Tables and Slicers for summarizing and interacting with data.

* **Data Transformation & Modeling:** Explore Power Query for data import and transformation, and Power Pivot for advanced data modeling and analysis.

* **Data Visualization:** Create impactful charts and build interactive dashboards to communicate insights effectively.

* **Automation:** Gain an introduction to Macros for automating repetitive tasks.

* **Practical Application:** The course emphasizes hands-on projects to solidify learning.

3. Detailed Notes

I. Introduction to MS Excel & Basics

* **00:00:00 - Excel Introduction**

* **00:01:28 - Introduction to MS Excel and Beginner's Guide**

* What is MS Excel and its role in data analysis.

* Navigating the Excel interface (Ribbon, Cells, Rows, Columns, Sheets).

* Basic data entry and manipulation.

* **00:12:01 - Basic Functions of Excel**

* Understanding cells, ranges, and worksheets.

* Copy, Paste, Cut operations.

* Inserting and deleting rows/columns.

* **00:16:24 - Basics of Formatting in Excel**

* Font styles, sizes, colors.

* Cell alignment and merging.

* Number formatting (currency, percentage, dates).

* Borders and fill colors.

* **00:21:11 - Sorting Data in Excel - Different Methods to Sort**

* Sorting alphabetically (A-Z, Z-A).

* Sorting numerically (smallest to largest, largest to smallest).

* Multi-level sorting.

* Custom sorting.

* **00:27:57 - Filtering Data in Excel**

* AutoFilter for basic filtering.

* Filtering by text, numbers, and dates.

* Advanced filtering options.

* Clearing filters.

II. Formulas & Functions

* **00:33:56 - Basics of Formula/Functions**

* Understanding formulas (e.g., `=A1+B1`).

* The concept of functions and their syntax (e.g., `=SUM(A1:A10)`).

* AutoSum feature.

* Order of operations (PEMDAS/BODMAS).

* **00:39:22 - Text Functions in Excel**

* `LEN()`: Returns the number of characters in a text string.

* `LEFT()`, `RIGHT()`, `MID()`: Extract characters from a text string.

* `CONCATENATE()` / `&`: Joins multiple text strings.

* `UPPER()`, `LOWER()`, `PROPER()`: Changes the case of text.

* `TRIM()`: Removes extra spaces from text.

* `FIND()`, `SEARCH()`: Locates a text string within another.

* `SUBSTITUTE()`, `REPLACE()`: Replaces text within a string.

* **00:49:43 - IF, AND and OR Functions in Excel**

* **`IF()`**: Performs a logical test and returns one value if TRUE, another if FALSE.

* `=IF(logical_test, value_if_true, value_if_false)`

* **`AND()`**: Returns TRUE if all arguments are TRUE.

* `=AND(logical1, [logical2], ...)`

* **`OR()`**: Returns TRUE if any argument is TRUE.

* `=OR(logical1, [logical2], ...)`

* Using these functions for conditional calculations and decision-making.

* **00:57:08 - Date & Time Functions in Excel**

* `TODAY()`: Returns the current date.

* `NOW()`: Returns the current date and time.

* `DAY()`, `MONTH()`, `YEAR()`: Extracts parts of a date.

* `DATE()`: Creates a date from year, month, and day values.

* `DATEDIF()`: Calculates the difference between two dates.

* Working with time values.

* **01:04:01 - COUNTIF, COUNTIFS, SUMIF, SUMIFS Functions in Excel**

* **`COUNTIF()`**: Counts cells within a range that meet a single criterion.

* `=COUNTIF(range, criteria)`

* **`COUNTIFS()`**: Counts cells within a range that meet multiple criteria.

* `=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

* **`SUMIF()`**: Sums cells within a range that meet a single criterion.

* `=SUMIF(range, criteria, [sum_range])`

* **`SUMIFS()`**: Sums cells within a range that meet multiple criteria.

* `=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)`

III. Lookup & Reference Functions

* **01:10:38 - Data Validation in Excel**

* Restricting data entry to specific types (e.g., whole numbers, dates, lists).

* Creating dropdown lists.

* Setting input messages and error alerts.

* **01:18:42 - Vlookup in Excel**

* **`VLOOKUP()`**: Searches for a value in the first column of a table and returns a value in the same row from a specified column.

* `=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])`

* Understanding `lookup_value`, `table_array`, `col_index_num`, and `range_lookup` (TRUE for approximate match, FALSE for exact match).

* Common use cases and limitations.

* **01:28:43 - Hlookup in Excel**

* **`HLOOKUP()`**: Searches for a value in the top row of a table and returns a value in the same column from a specified row.

* `=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])`

* Similar parameters to VLOOKUP but works horizontally.

* **01:30:51 - Lookup in Excel**

* General discussion on lookup functionalities.

* **01:37:07 - Xlookup in Excel**

* **`XLOOKUP()`**: A modern and more flexible lookup function that replaces VLOOKUP and HLOOKUP.

* `=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])`

* Key advantages: searches left or right, default exact match, built-in `if_not_found` argument, easier syntax.

* **01:44:25 - Index+Match in Excel**

* **`INDEX()`**: Returns a value or reference of the cell at the intersection of a particular row and column in a given range.

* `=INDEX(array, row_num, [column_num])`

* **`MATCH()`**: Returns the relative position of an item in a range that matches a specified value.

* `=MATCH(lookup_value, lookup_array, [match_type])`

* Combining `INDEX` and `MATCH` to create powerful, flexible lookups, especially for scenarios where VLOOKUP is insufficient.

IV. Advanced Data Analysis & Visualization

* **01:49:14 - Using Conditional Formatting**

* Highlighting cells based on rules (e.g., values greater than X, top 10 items).

* Data bars, color scales, icon sets.

* Creating custom formatting rules.

* **01:56:32 - Macros in Excel**

* Introduction to macros for automating repetitive tasks.

* Recording a macro.

* Basic understanding of VBA (Visual Basic for Applications).

* **02:01:08 - Summarizing Data with Pivot Tables**

* What are Pivot Tables and why are they useful?

* Creating a Pivot Table from raw data.

* Arranging fields (Rows, Columns, Values, Filters).

* Summarizing data (Sum, Count, Average, etc.).

* Grouping data by date, text, etc.

* Calculated fields and items.

* **02:07:33 - Working with Slicers**

* Adding Slicers to Pivot Tables for interactive filtering.

* Connecting multiple Pivot Tables to a single Slicer.

* **02:11:52 - Power Query in Excel**

* Getting & Transforming Data.

* Connecting to various data sources (Excel files, CSV, databases, web).

* Cleaning and shaping data (removing columns, changing data types, filtering, unpivoting, splitting columns).

* Understanding the Power Query Editor interface.

* **02:21:50 - Power Pivot in Excel**

* Building a data model.

* Creating relationships between tables.

* DAX (Data Analysis Expressions) for creating measures and calculated columns.

* Advanced analysis with larger datasets.

* **02:33:14 - Visualizing Data with Charts**

* Creating various chart types (Column, Bar, Line, Pie, Scatter).

* Choosing the right chart for the data.

* Customizing chart elements (titles, axes, legends, data labels).

* **02:39:31 - Excel Dashboard**

* Combining charts, Pivot Tables, and Slicers to create interactive dashboards.

* Principles of effective dashboard design.

* Making dashboards dynamic and user-friendly.

---

**Related Resources:**

* **Data Analytics Mentorship Program:** [https://www.wscubetech.com/data-analy...](https://www.wscubetech.com/data-analy...)

* **Course Module:** [https://drive.google.com/file/d/1TcZG...](https://drive.google.com/file/d/1TcZG...)

* **Datasets:** [https://github.com/Ayushi0214/PowerBI...](https://github.com/Ayushi0214/PowerBI...)

* **Power BI Full Course (FREE):** [https://www.youtube.com/watch?v=27-h0...](https://www.youtube.com/watch?v=27-h0...)

* **WsCube Tech Website:** [https://www.wscubetech.com/](https://www.wscubetech.com/)

* **WsCube Tech Courses:** [https://courses.wscubetech.com/s/store](https://courses.wscubetech.com/s/store)

* **WsCube Tech English Channel:** [https://bit.ly/2M3oYOs](https://bit.ly/2M3oYOs)

**Contact Information:**

* **Phone:** +91-7878985501, +91-9269698122

**Connect with the Founder (Mr. Kushagra Bhatia):**

* **Instagram:** [https://www.instagram.com/kushagrabhatiaofficial/](https://www.instagram.com/kushagrabhatiaofficial/)

* **LinkedIn:** [https://www.linkedin.com/in/kushagra-bhatia-](https://www.linkedin.com/in/kushagra-bhatia-)

* **Facebook:** [https://www.facebook.com/kushagrawscubetech](https://www.facebook.com/kushagrawscubetech)

**Connect with WsCube Tech:**

* **Facebook:** [https://www.facebook.com/wscubetech.india](https://www.facebook.com/wscubetech.india)

* **Twitter:** [https://twitter.com/wscubetechindia](https://twitter.com/wscubetechindia)

* **Instagram:** [https://www.instagram.com/wscubetechindia/](https://www.instagram.com/wscubetechindia/)

* **LinkedIn:** [https://www.linkedin.com/company/wscubetechindia/](https://www.linkedin.com/company/wscubetechindia/)

* **YouTube:** [http://bit.ly/wscubechannel](http://bit.ly/wscubechannel)

* **Website:** [http://wscubetech.com](http://wscubetech.com)