Advance Excel Mastery Course 2024

You will learn in this lesson (In Hindi):-

1. How to open Excel on PC/Laptop

2. Understanding the Interface of Excel

3. Understand Rows, Columns, Cells, and More…

1. Data Entry in Excel

2. Understanding basic formulas in Excel

3. Working with MAX, MIN, COUNT, AVERAGE formulas etc.

4. Adjust Column size & More....

1. How to use sort Data in Excel

2. Sort data quickly and easily

3. Sort data by specifying criteria & More....

You will learn in this lesson (In Hindi) :-

1. What is Filter in Excel?

2. How to use Filter in Excel

3. Using Filter with shortcut keys

4. Multiple filtering in Excel

5. Adding Filter in Quick access tool & more.…

You will learn in this lesson (In Hindi) :-

1. Advance Filter with macro in Excel

2. How to Enable Developer Tab?

3. Using macro code for advance Filter

4. Enable Macro from Trust Center Setting & more.…

1. You will learn in this lesson (In Hindi) :-

2. Advance Filter with macro in Excel

3. How to Enable the Developer Tab?

4. Using macro code for advanced Filter

5. Enable Macro from Trust Center Setting & more...

You will learn in this lesson (In Hindi) :-

1. Applies formatting based on specified conditions.

2. Highlights data meeting certain criteria.

3. Helps identify trends and outliers easily.

4. Makes data analysis and visualization more effective.

You will learn in this lesson (In Hindi) :-

Text to Columns:

1. Splits text into separate columns.

2. Uses specified delimiter to split data.

3. Useful for separating data in a single cell.

Flash Fill:

4. Automatically fills data based on patterns.

5. Requires a few examples to predict the pattern.

6. Quick data completion tool.

You will learn in this lesson (In Hindi) :-

Remove Duplicates:

1. Eliminates duplicate entries from a data range.

2. Retains only unique values in the selected column.

3. Helps clean and streamline datasets.

Unique Formula:

4. Extracts unique values from a range of data.

5. Automatically creates a list of distinct items.

6. Useful for data analysis and unique data extraction.

You will learn in this lesson (In Hindi) :-

1. Paste selected data with specific options.

2. Copy formats, values, or transpose data.

3. Useful for customizing data pasting in Excel.

4. Preserves source formatting when pasting in Excel.

You will learn in this lesson (In Hindi) :-

· It Performs conditional checks on data.

· Returns specific values based on the condition.

· Helps automate decision-making in spreadsheets.

· Simple syntax: =IF(logical_test, value_if_true, value_if_false).

You will learn in this lesson (In Hindi) :-

· Checks multiple conditions using "AND" logical operator.

· Returns a value if all conditions are true.

· Used for complex conditional calculations.

· Syntax: =IF(AND(condition1, condition2, ...), value_if_true, value_if_false).

You will learn in this lesson (In Hindi) :-

· Evaluates multiple conditions with OR logic.

· Returns TRUE if any condition is TRUE.

· Used in IF statements for flexible decision-making.

· Simplifies complex conditional expressions.

You will learn in this lesson (In Hindi) :-

Sumif:

· Adds values based on a specific condition.

· Calculates sum of cells meeting given criteria.

· Sum values that meet a specified condition.

Countif:

· Counts cells based on a given condition.

· Calculates the number of cells meeting a criteria.

· Counts cells that satisfy a specified condition.

You will learn in this lesson (In Hindi) :-

· Combines multiple IF functions in one formula.

· Tests multiple conditions based on hierarchy.

· Helps make complex decisions in Excel.

· Requires careful nesting to avoid errors.

· Provides flexibility for handling various scenarios.

You will learn in this lesson (In Hindi) :-

SumIFS Formula:

· Adds values based on multiple criteria.

· Calculates sum with specified conditions.

· Flexible and powerful for complex calculations.

CountIFS Formula:

· Counts cells that meet multiple criteria.

· Provides count with specified conditions.

· Useful for advanced data analysis.

You will learn in this lesson (In Hindi) :-

Match Formula:

· Finds the position of a value in a range.

· Useful for searching data in a sorted list.

· Returns the relative position of the matched value.

Index Formula:

· Retrieves a value from a specified row and column.

· Used to extract data from a table or range.

Requires row and column references as inputs.

You will learn in this lesson (In Hindi) :-

V-lookup:

· Searches for a value vertically in the leftmost column.

· Returns a corresponding value from the same row in another column.

· Needs a unique identifier in the leftmost column.

H-lookup:

· Searches for a value horizontally in the topmost row.

· Retrieves a matching value from the same column in another row.

· Requires a unique identifier in the topmost row.

You will learn in this lesson (In Hindi) :-

· UPPER: Converts text to uppercase.

· LOWER: Converts text to lowercase.

· PROPER: Converts text to proper case (first letter capitalized).

· LEFT: Extracts leftmost characters from a cell.

· RIGHT: Extracts rightmost characters from a cell.

· MID: Extracts characters from the middle of a cell.

· FIND: Finds the position of a substring within a cell.

· LEN: Returns the length of the text in a cell.

· ISTEXT: Checks if the value is text.

· ISNUMBER: Checks if the value is a number.

You will learn in this lesson (In Hindi) :-

· Day: Extracts the day from a date.

· Month: Extracts the month from a date.

· Year: Extracts the year from a date.

· Date: Creates a date from day, month, and year values.

· EOMONTH: Returns the last day of the month for a given date.

· DATEDIF: Calculates the difference between two dates in days, months, or years.

· TODAY: Returns the current date.

· NOW: Returns the current date and time.

You will learn in this lesson (In Hindi) :-

· Summarizes and analyzes data from a table or range.

· Offers a dynamic way to organize and calculate data.

· Creates charts and tables for insights.

· Easily rearranges data to gain different perspectives.

You will learn in this lesson (In Hindi) :-

· Graphical representation of PivotTable data.

· Visualizes trends and patterns in data.

· Dynamically updates with changes in the PivotTable.

· Easily customizable with different chart types and styles.

You will learn in this lesson (In Hindi) :-

· Interactive filtering tool for pivot table data.

· Allows easy data analysis by selecting slicer items.

· Filters multiple pivot tables simultaneously.

· Enhances data visualization and dashboard creation.

You will learn in this lesson (In Hindi) :-

Timeline:

· Visual representation of date-based data.

· Helps filter and navigate data in a date range.

· Useful for tracking progress over time.

Pivot Table Options:

· Customizes pivot table behavior and appearance.

· Controls calculations, sorting, and data display.

· Enhances data analysis and reporting flexibility.

You will learn in this lesson (In Hindi) :-

Sort Formula:

· Arranges data in ascending or descending order.

· Uses SORT function in Excel.

· Rearranges rows based on selected criteria.

Filter Formula:

· Displays specific data based on defined conditions.

· Uses FILTER function in Excel.

· Shows only relevant rows that meet the criteria.

You will learn in this lesson (In Hindi) :-

· Calculate interest rates, present values, and future values.

· Perform cash flow analysis and loan payments calculations.

· Evaluate investment returns and net present value.

· Conduct financial modeling and data analysis efficiently.

You will learn in this lesson (In Hindi) :-

· Powerful search and retrieval function.

· Searches for a value and returns corresponding data.

· Replaces VLOOKUP and INDEX-MATCH for enhanced functionality.

· Supports searching in both rows and columns.

You will learn in this lesson (In Hindi) :-

· Adjust page layout for better printing.

· Set the print area to print specific range only.

· Use Page Break Preview to manage page breaks.

· Check print preview before printing to avoid errors.

You will learn in this lesson (In Hindi) :-

Macros:

· Automated tasks with recorded actions.

· Saves time by executing repetitive actions.

· Customizable and programmable sequences.

Add-ins:

· Extend Excel functionality with third-party tools.

· Enhance productivity and provide specialized features.

· Easily installable and integrable into Excel.

You will learn in this lesson (In Hindi) :-

· ChatGPT automates data entry and analysis.

· Provides instant answers and insights in Excel.

· Helps with complex calculations and modeling.

· Improves data visualization and report generation.

· Enhances Excel's functionality with natural language commands.