MS Advance Excel Course

Excel Logical Functions (IF, AND, OR, NOT) Logical functions help Excel think like a human. They check conditions and then give results based on those conditions. In simple words, Logical Functions are used to make decisions in Excel. For example: If marks are greater than 40 → Show “Pass” If salary is above 50,000 → Show “High Income” If stock is less than 10 → Show “Order Now” Excel checks the condition and gives the result automatically.   IF Function in Excel What is IF Function? The IF function in MS Excel checks a condition and gives different results based on whether the condition is TRUE or FALSE. It has 3 parts: Condition (logical test) What to do if condition is TRUE What to do if condition is FALSE   Syntax of IF Function =IF(logical_test, value_if_true, value_if_false) Arguments Explanation logical_test → The condition you want to check value_if_true → Result if condition is TRUE value_if_false → Result if condition is FALSE   Example 1: Pass or Fail Student Marks Ravi 75 Aman 32   Condition: If Marks ≥ 40 → Pass Otherwise → Fail   Formula: =IF(B2>=40,”Pass”,”Fail”)   Explanation: B2>=40 → This checks if marks are greater than or equal to 40 If TRUE → “Pass” If FALSE → “Fail”   Result: Ravi → Pass Aman → Fail   Example 2: Bonus Calculation Employee Sales Raj 60000 Mohan 30000   Condition: If Sales > 50000 → Bonus 5000 Else → Bonus 2000 Formula: =IF(B2>50000,5000,2000)   AND Function in Excel What is AND Function? AND function checks multiple conditions at the same time. We use AND Function in ms excel when we need to apply more than one condition at the a time. AND Function returns the result in True or False.   Important Rule: AND gives TRUE only when ALL conditions are TRUE. If even one condition is FALSE → Result will be FALSE.   Syntax of AND Function =AND(logical1, [logical2], [logical3]…) Arguments: logical1 → First condition logical2 → Second condition logical3 → Third condition (optional) Example 1: Eligibility Check Condition: Age ≥ 18 Marks ≥ 50 Both conditions must be true. Name Age Marks Rohan 20 60 Amit 17 80   Formula: =AND(B2>=18,C2>=50) Result: Rohan → TRUE (both conditions true) Amit → FALSE (age condition false)   Using AND with IF (Very Important) In real Excel work, AND is mostly used inside IF.   Example: =IF(AND(B2>=18,C2>=50),”Eligible”,”Not Eligible”) This means: If Age ≥ 18 AND Marks ≥ 50 → Eligible Otherwise → Not Eligible   OR Function in Excel What is OR Function? OR function also checks multiple conditions. Important Rule: OR gives TRUE if ANY ONE condition is TRUE. Even if one condition is true → Result will be TRUE.   Syntax of OR Function =OR(logical1, [logical2], [logical3]…)   Example: Scholarship Condition Condition: Marks ≥ 90 OR Sports Certificate = Yes   Name Marks Sports Rahul 85 Yes Neeraj 92 No Formula: =OR(B2>=90,C2=”Yes”) Result: Rahul → TRUE (sports = Yes) Neeraj → TRUE (marks ≥ 90)   OR with IF Function =IF(OR(B2>=90,C2=”Yes”),”Scholarship”,”No Scholarship”) Meaning: If any one condition is true → Scholarship   NOT Function in Excel What is NOT Function? NOT function reverses the result. TRUE becomes FALSE FALSE becomes TRUE   Syntax of NOT Function =NOT(logical)   Example If A1 contains TRUE Formula: =NOT(A1) Result → FALSE   Example with Condition If Marks < 40 means Fail But we use NOT =NOT(B2<40) If Marks = 35 B2<40 → TRUE NOT(TRUE) → FALSE   Practical Combined Example (Advanced Level) Let’s combine IF + AND + OR Condition: Age ≥ 18 Marks ≥ 60 OR Sports = Yes Formula: =IF(AND(B2>=18,OR(C2>=60,D2=”Yes”)),”Selected”,”Rejected”) Logic: Age must be 18+ And either: Marks ≥ 60 OR Sports = Yes   This is real-life Excel logic.   Comparison Table Function Use Rule IF Make decision Based on TRUE/FALSE AND Multiple conditions All must be TRUE OR Multiple conditions Any one TRUE NOT Reverse logic TRUE → FALSE   Important Operators Used in Logical Functions Operator Meaning > Greater than < Less than >= Greater than equal <= Less than equal = Equal to <> Not equal to Example: =A1<>10 Means A1 is not equal to 10. Logical functions are the foundation of: Salary Sheet Result Sheet Attendance Sheet GST Calculation Sales Reports Inventory Management  

You can remove duplicate values in Excel using the built-in “Remove Duplicates” feature. Here’s a step-by-step guide on how to do it:   1.  Open your Excel spreadsheet containing the data with duplicate values. 2. Select the range of cells from which you want to remove duplicates. You can select a single column or multiple columns as needed. 3. Go to the “Data” tab on the Excel ribbon. This tab is usually located at the top of the Excel window. 4. In the “Data Tools” group, you will find the “Remove Duplicates” button. Click on it. 5. A “Remove Duplicates” dialog box will appear. In this dialog box:   a. By default, all columns in your selected range will be checked for duplicates. If you want to remove duplicates based on specific columns, uncheck the columns you don’t want to consider. b. Optionally, you can choose to expand the selection to include other cells in the workbook if your data extends beyond the selected range.   c. Click the “OK” button when you are ready to proceed.   6. Excel will now remove duplicate values based on the criteria you specified. You’ll receive a message telling you how many duplicate values were removed and how many unique values remain. 7. Click “OK” to close the message box, and the duplicate values will be removed from the selected range.   Please note that Excel’s “Remove Duplicates” feature keeps the first occurrence of each unique value and removes subsequent duplicates. Make sure to create a backup of your data before removing duplicates in case you need to preserve the original data.   Step 1 Select sheet data where you want to remove duplicate value from your data.   Step 2 Press on Data tab from menu   Step 3 After pressed data tab in that table you can see remove duplicate button press on that   Step 4 After this you will see a message box appear on screen like this   Now your Duplicates value was removed.

Data Validation in Excel – Learn how to use Data Validation tools in Excel Hindi to improve the accuracy of the data in your spreadsheets. ignore invalid data using data validation. Data validation refers to the process of ensuring the accuracy and quality of data. Data Validation controls user to enter input in a cell. This plays an important role when there is huge amount of data in excel. Drop-down using a particular data range. You can allow a list or input values directly as well. You can also include a dynamic drop down using conditions. Step 1 Select a cell in your spreadsheet where you want to make drop down list / data validation   Step 2 Select data tab for apply data validation in your sheet   Step 3 Now Under Data Tools group, click on ‘drop down’ to select Data Validation. After it a new window will Pop-up with three tab options. Select Data Validation as shown in image bellow.     Step 4 Select Your Data Cell Which Data you want to show in a list. Under Allow, select List. Under Allow, select List.  Do a direct cell reference to the column headers (=$B$2:$B$21) as the Source. Do a direct cell reference to the column headers (=$B$2:$B$21) as the Source.   Now Successfully Added Drop Down List in you select cell.

Conditional Formatting: This button allows you to apply formatting rules to cells based on their values, which can help highlight important data and trends. The “Conditional Formatting” option in the “Home” tab of Microsoft Excel allows you to apply formatting rules to cells based on their values, which can help highlight important data and trends in your spreadsheet.   Conditional Formatting 1. Highlight Cells Rules: This drop-down menu allows you to select a rule for highlighting cells based on their values. You can choose from options such as Greater Than, Less Than, Between, Equal To, and Text Contains. 2. Top/Bottom Rules: This drop-down menu allows you to select a rule for highlighting the top or bottom values in a range of cells. You can choose from options such as Top 10 Items, Bottom 10 Items, Above Average, and Below Average. 3. Data Bars, Color Scales, and Icon Sets: These buttons allow you to apply predefined formatting styles to your cells based on their values. For example, you can use data bars to create a bar chart within a cell, or use color scales to apply a gradient of colors to your cells. 4. New Rule: This button opens the New Formatting Rule dialog box, which allows you to create a custom formatting rule based on a formula or function. You can specify the formatting options such as font, background color, and border. 5. Clear Rules: This button allows you to clear any conditional formatting rules that have been applied to the selected cells.   Highlight Cells Rules Conditional Formatting in home tab in MS excel The “Highlight Cells Rules” option in the “Conditional Formatting” group of the “Home” tab in Microsoft Excel allows you to apply formatting rules to cells based on their values. Here are the steps to use the “Highlight Cells Rules” option: 1. Select the range of cells that you want to apply the rule to. 2. Click on the “Conditional Formatting” button in the “Home” tab, and select “Highlight Cells Rules” from the drop-down menu. 3. Select the rule that you want to apply. For example, you can choose “Greater Than” to highlight cells that are greater than a specific value. 4. Enter the value or formula that you want to use for the rule, and select the formatting options that you want to apply. For example, you can choose a red background color to highlight the cells. 5. Click “OK” to apply the rule to the selected cells.   Some examples of “Highlight Cells Rules” that you can apply are: Greater Than: highlights cells that have values greater than a specified number Less Than: highlights cells that have values less than a specified number Between: highlights cells that have values between two specified numbers Equal To: highlights cells that have values equal to a specified number Text that Contains: highlights cells that contain specific text   Overall, the “Highlight Cells Rules” option in the Conditional Formatting group provides a quick and easy way to highlight important data in your spreadsheet, which can help make your data more understandable and useful.

Transpose in Excel used to switch the orientation of a data array.  It means you can Use the Transpose option to switch rows data into columns or columns data into rows. You can convert the vertical range to a horizontal range or Horizontal to vertical using transpose. Special Paste Transpose Step 1: Select the range that you want to transpose in excel sheet. Step 2: Copy the selected range data by using right click or CTRL + C.   Step 3: Select a new location (cell) in your sheet where you want to paste the transposed data. Step 4: Press Right click of mouse or use shortcut key CTRL + ALT + V, after it the Paste Special dialog box appear on screen. Now, check the Transpose option on the bottom right.   You can find this option in Home tab → Clipboard section → Paste → Paste Special. Step 6: Click on OK.     Transpose excel data using transpose function,  You also can use the transpose function to transpose data in MS excel. The syntax : =transpose(array) Step 1. First, select the new range of cells.   Step 2. Type in =TRANSPOSE( Step 3. Select the range A1:C1 and close with a parenthesis.   Step 4. Finish by pressing CTRL + SHIFT + ENTER.

The Data tab in Microsoft Excel provides a wide range of tools and features for managing and manipulating data in spreadsheets. Some of the key features of the Data tab include:   1. Get & Transform Data: This feature allows you to import, transform, and reshape data from various sources, including databases, web pages, and text files. You can use it to perform advanced data cleansing, merging, and aggregation tasks. 2. Sort & Filter: This feature allows you to sort data in ascending or descending order based on one or more columns. You can also filter data based on specific criteria, such as dates, text, or numbers. 3. Data Validation: This feature allows you to set rules and restrictions on the data that users can enter into specific cells. For example, you can prevent users from entering invalid dates or text values. 4. What-If Analysis: This feature allows you to explore different scenarios by changing input values and seeing how they affect the results of formulas or functions. 5. Consolidate: This feature allows you to combine data from multiple sheets or workbooks into a single table. 6. Data Tools: This feature includes a variety of tools for managing data, such as removing duplicates, converting text to columns, and splitting data into separate cells. Overall, the Data tab in Excel is a powerful set of tools that can help you manage and manipulate data efficiently and effectively.