Excel - PPT

Lookup Functions, Count functions and Customization of functions 

Apply the VLOOKUP Function

Task: Retrieve the department of an employee using their Emp ID.

1. Set Up:

   • Place the dataset in an Excel sheet.

   • In a separate cell (e.g., G1), type the header: Emp ID.

   • Below it (e.g., G2), input an Emp ID, such as 103.

2. Apply VLOOKUP:

   • In another cell (e.g., H1), type the header: Department.

   • In H2, input the formula:
     php
     Copy code
     =VLOOKUP(G2, A2:E6, 3, FALSE)

This searches for the Emp ID in column A and returns the corresponding department from column C. 

Select Your Table:

• Highlight the entire table, including headers (e.g., A1:E6).

Copy the Table:

• Press Ctrl + C or right-click and select Copy.

Choose a New Location:

• Click on an empty cell where you want the transposed table to start (e.g., G1).

Paste as Transposed:

• Right-click on the new location.

• Select Paste Special > Transpose (you’ll see the option in the paste menu or under Paste Special).

To retrieve the Salary for a specific Emp ID, use:

excel

Copy code

=HLOOKUP(G2, A1:F5, 4, FALSE)

Explanation:

1. G2: The cell where you enter the Emp ID to search for (e.g., 104).

2. A1:F5: The range of your transposed table.

3. 4: The row number containing the Salary data.

4. FALSE: Ensures an exact match for the Emp ID.

Why HLOOKUP Works:

• HLOOKUP searches for the Emp ID horizontally in the first row of the table range.

• It then returns the value from the specified row number (e.g., row 4 for Salary).

Retrieve the Salary using INDEX

1. Setup:

   • In cell G1, type Emp ID.

   • In cell G2, enter the Emp ID you want to search for, e.g., 104.

2. Formula:

In H2 (or any empty cell), type the following formula:
excel
Copy code
=INDEX(A2:F6, 4, MATCH(G2, A1:F1, 0))

Explanation:

1. A2:F6: The range that contains the data (excluding the header row Emp ID).

2. 4: Row number inside the range where Salary is located.

3. MATCH(G2, A1:F1, 0): Finds the column number for the Emp ID entered in G2.

4. This combination retrieves the Salary corresponding to the Emp ID.

Expected Result:

• If G2 = 104, the formula will return 55,000.

Apply COUNT Function

We will start with a simple COUNT function to calculate the total number of employees in the table.

Task: Count the total number of employees.

1. In Excel:

   • Go to a new cell (e.g., G1) and type:
     "Total Employees" as the label.

In cell G2, enter the formula:
excel
Copy code
=COUNTA(A2:A11)

2. Explanation:

   • A2:A11: Range of Emp ID values (employee IDs).

   • COUNTA: Counts all non-empty cells in the range.

Expected Result:

• 10 (The total number of employees in the dataset).

Apply COUNTIF Function

Now let’s count how many employees are in a specific Department (e.g., IT).

Task: Count the number of employees in the IT Department.

1. In a new cell (e.g., G3), type:
   "Employees in IT Department" as the label.

In cell G4, enter the formula:
excel
Copy code
=COUNTIF(C2:C11, "IT")

2. Explanation:

   • C2:C11: The range of the Department column.

   • "IT": The specific condition to count employees only in the IT department.

Expected Result:

• 3 (There are 3 employees in the IT department: Bob, Evan, and Ian).

Apply COUNTIF Function

Now let’s count how many employees are in a specific Department (e.g., IT).

Task: Count the number of employees in the IT Department.

1. In a new cell (e.g., G3), type:
   "Employees in IT Department" as the label.

In cell G4, enter the formula:
excel
Copy code
=COUNTIF(C2:C11, "IT")

2. Explanation:

   • C2:C11: The range of the Department column.

   • "IT": The specific condition to count employees only in the IT department.

Expected Result:

• 3 (There are 3 employees in the IT department: Bob, Evan, and Ian).

Create Subtotals

We will now create subtotals to calculate the total salary for each Department.

Task: Create subtotals for Salary grouped by Department.

1. Sort the Data:

   • Select the entire table (A1:E11).

   • Go to the Data tab → Click Sort.

   • Sort by Department (ascending order) so departments are grouped together.

2. Add Subtotals:

   • Go to the Data tab → Click on Subtotal in the "Outline" group.

   • In the Subtotal dialog box:

     • At each change in: Select Department.

     • Use function: Select SUM.

     • Add subtotal to: Check the box for Salary.

     • Click OK.

3. Result:

   • Excel will insert subtotals for Salary under each department group.

   • A grand total will also appear at the bottom.

Apply Data Filtering

We will now filter the table to display specific records, such as employees in the IT department with salaries greater than 70,000.

Task: Filter the table based on Department = IT and Salary > 70,000.

1. Apply Filters:

   • Select the entire table (A1:E11).

   • Go to the Data tab → Click Filter.

   • Small dropdown arrows will appear next to each column header.

2. Filter Department:

   • Click the dropdown arrow in the Department column (C1).

   • Uncheck all options except IT.

3. Filter Salary:

   • Click the dropdown arrow in the Salary column (D1).

   • Select Number Filters → Greater Than.

   • Enter 70,000 in the condition box → Click OK.

Expected Outcome:

The filtered table will only show employees in the IT Department with salaries greater than 70,000:

Insert Slicers for Dynamic Filtering

Slicers allow you to interactively filter data in an Excel table, providing a visual way to filter by fields such as Department or Joining Year.

Task: Add slicers to filter the table by Department and Joining Year.

1. Convert Data to Table:

   • Select the entire table (A1:E11).

   • Go to the Insert tab → Click Table or press Ctrl + T.

   • Ensure "My table has headers" is checked → Click OK.

2. Insert Slicers:

   • With the table selected, go to the Table Design tab (or Table Tools) → Click Insert Slicer.

   • In the dialog box, check the boxes for:

     • Department

     • Joining Year

   • Click OK.

3. Position and Use Slicers:

   • The slicers will appear on your sheet. You can drag and position them as needed.

   • Click on any department (e.g., IT) or year (e.g., 2020) in the slicers to filter the table dynamically.

Expected Outcome:

• Clicking "IT" in the Department Slicer will filter the table to show only IT employees.

• Clicking "2020" in the Joining Year Slicer will filter the table to show employees who joined in 2020.

1. Data Diversity:

   • 10 employees with varying departments, salaries, and joining years.

   • Repeated departments and years allow for filtering, removing duplicates, and creating subtotals.

2. Application in Excel:

   • COUNT: Use COUNT or COUNTIF to count employees in specific departments or joining years.

   • Custom Functions: Apply SUM, AVERAGE, or custom functions to analyze salaries.

   • Filtering: Filter employees by departments, joining years, or salaries.

   • Remove Duplicates: Check and remove duplicate entries (e.g., employees with the same department or year).

   • Subtotals: Use Subtotal to group and calculate totals/salaries by departments or years.

   • Slicers: Apply slicers in Excel to filter data dynamically by Department, Joining Year, etc.

=IF(OR(A2="", ISBLANK(A2)), "No Email", B2) 

1. Select the entire data range (e.g., A2:D6).

2. Go to the Home tab → Conditional Formatting → New Rule.

3. Choose Use a formula to determine which cells to format.

Enter this formula:
excel
Copy code
=OR(ISBLANK(A2), ISBLANK(B2), ISBLANK(C2), ISBLANK(D2))

4. 
   

   • This formula checks if any cell in a row (Name, Email, Phone, Address) is blank.

5. Click Format, choose a fill color (e.g., red), and click OK.

Formula 1: SUMIFS

Task: Calculate the total sales for "Marketing" in Q1, Q2, and Q3.

=SUMIFS(C2:C6, B2:B6, "Marketing") + SUMIFS(D2:D6, B2:B6, "Marketing") + SUMIFS(E2:E6, B2:B6, "Marketing")

• "Marketing": Criteria for summing only sales from Marketing.

import pandas as pd

data = {

    "Name": ["John", "Alice", "Bob", "Diana", "Charles"],

    "Department": ["Marketing", "Sales", "IT", "HR", "Finance"],

    "Sales (Q1)": [15000, 25000, 18000, 12000, 17000],

    "Sales (Q2)": [20000, 23000, 19000, 14000, 16000],

    "Sales (Q3)": [18000, 22000, 20000, 13000, 15000],

}

df = pd.DataFrame(data)

df

marketing_sales = df[df['Department'] == 'Marketing']

total_marketing_sales = marketing_sales[['Sales (Q1)', 'Sales (Q2)', 'Sales (Q3)']].sum().sum()

print("Total Sales for Marketing:", total_marketing_sales)

Formula: INDEX-MATCH with Multiple Criteria

Task: Find the Q2 sales for the employee in the "Marketing" department.

=INDEX(D2:D6, MATCH("Marketing", B2:B6, 0))

Explanation:

1. INDEX(D2:D6):

   • This defines the range where you want to extract the value (Q2 sales).

2. MATCH("Marketing", B2:B6, 0):

   • Finds the row number where "Marketing" appears in the "Department" column.

3. Combined Formula:

   • INDEX uses the row number from MATCH to return the corresponding value in the Q2 column.

import pandas as pd

data = {

    "Name": ["John", "Alice", "Bob", "Diana", "Charles"],

    "Department": ["Marketing", "Sales", "IT", "HR", "Finance"],

    "Sales (Q1)": [15000, 25000, 18000, 12000, 17000],

    "Sales (Q2)": [20000, 23000, 19000, 14000, 16000],

    "Sales (Q3)": [18000, 22000, 20000, 13000, 15000],

}

df = pd.DataFrame(data)

department_to_find = "Marketing"

q2_sales = df.loc[df['Department'] == department_to_find, 'Sales (Q2)'].values[0]

print(f"Q2 Sales for the {department_to_find} department: {q2_sales}")

Formula: ARRAYFORMULA for Conditional Summing Across Columns

Task: Find the total sales (Q1, Q2, and Q3) for the "Sales" department.

=SUMPRODUCT((B2:B6="Sales")*C2:E6) 

Explanation:

1. (B2:B6="Sales"):

   • Creates a logical array where the condition "Sales" is met in the "Department" column (1 for "Sales", 0 otherwise).

2. C2:E6:

   • Refers to the sales data (Q1, Q2, Q3).

3. SUMPRODUCT():

   • Multiplies the logical array with the sales data row-wise and sums the resulting values.

import pandas as pd

data = {

    "Name": ["John", "Alice", "Bob", "Diana", "Charles"],

    "Department": ["Marketing", "Sales", "IT", "HR", "Finance"],

    "Sales (Q1)": [15000, 25000, 18000, 12000, 17000],

    "Sales (Q2)": [20000, 23000, 19000, 14000, 16000],

    "Sales (Q3)": [18000, 22000, 20000, 13000, 15000],

}

df = pd.DataFrame(data)

department_to_find = "Sales"

total_sales = df.loc[df['Department'] == department_to_find, ['Sales (Q1)', 'Sales (Q2)', 'Sales (Q3)']].sum().sum()

print(f"Total Sales for the {department_to_find} department: {total_sales}")

Formula: TEXTJOIN with Conditional Logic

Task: List all employee names from the "IT" and "Finance" departments, separated by commas.

Formula:

excel

Copy code

=TEXTJOIN(", ", TRUE, IF((B2:B6="IT")+(B2:B6="Finance"), A2:A6, ""))

Explanation:

1. TEXTJOIN(", ", TRUE, ...):

   • Combines text values into a single string, separated by ", ".

   • The TRUE argument skips empty cells.

2. IF((B2:B6="IT")+(B2:B6="Finance"), A2:A6, ""):

   • Checks if the department is "IT" or "Finance".

   • If the condition is met, it includes the corresponding name; otherwise, it leaves it blank.

3. (B2:B6="IT")+(B2:B6="Finance"):

   • Adds the results of two logical conditions, acting as an OR operator.

import pandas as pd

data = {

    "Name": ["John", "Alice", "Bob", "Diana", "Charles"],

    "Department": ["Marketing", "Sales", "IT", "HR", "Finance"],

    "Sales (Q1)": [15000, 25000, 18000, 12000, 17000],

    "Sales (Q2)": [20000, 23000, 19000, 14000, 16000],

    "Sales (Q3)": [18000, 22000, 20000, 13000, 15000],

}

df = pd.DataFrame(data)

departments_to_find = ["IT", "Finance"]

filtered_names = df[df['Department'].isin(departments_to_find)]['Name']

result = ", ".join(filtered_names)

print(f"Employees in IT or Finance: {result}")

import pandas as pd

# Create the dataset directly in Colab

data = {

    "Name": ["John Doe", "Jane Smith", "John Doe", "Alex Johnson", None, "Emily Davis"],

    "Email": ["john.doe@example.com", "jane.smith@example.com", None, "alex.j@example.com", "NA", "emily.d@example.com"],

    "Phone": ["123-456-7890", "987-654-3210", "123-456-7890", "567-890-1234", "456-789-0123", None],

    "Address": ["New York", "Los Angeles", "New York", "Chicago", "NA", None]

}

# Convert to DataFrame

df = pd.DataFrame(data)

# Display the DataFrame

print(df)

from google.colab import files uploaded = files.upload() 

# This will prompt you to upload your CSV file 

import pandas as pd 

# Load the CSV file 

df = pd.read_csv("data.csv") 

# Display the DataFrame 

print(df) 

Month Sales

Jan 200

Feb 220

Mar 250

Apr 280

May 300

Jun 310

Jul 320

Aug 350

Sep 370

Oct 390

Nov 400

Dec 420

=FORECAST(13, B2:B13, ROW(A2:A13)) 

=TREND(B2:B13, ROW(A2:A13), 13) 

EMP ID =and(isnumber(B5),len(B5)=3)

name =istext(H5)

email id =isnumber(match("*@*.?*",b8,0))

aadhar no. =and(isnumber(H8),len(H8)=12)

In Excel, the formula you’ve mentioned =ISNUMBER(MATCH("*@*.?*",B8,0)) is designed for data validation using a custom formula. Here's a detailed breakdown of how it works and what it’s intended to do:

Purpose of the Formula:

The formula aims to check whether the value entered in cell B8 is a valid email format, as it contains the basic elements of an email address (an "@" symbol and a domain with a period). However, this particular formula is not entirely correct for email validation, but I'll explain what each part does and how to adjust it for better functionality.

Breakdown of the Formula:

less

Copy code

=ISNUMBER(MATCH("*@*.?*",B8,0))

• MATCH("*@*.?*", B8, 0):

  • The MATCH() function looks for a match between the pattern *@*.?* and the value in B8.

  • *@*.?* is a wildcard pattern where:

    • The first * means "any string of characters."

    • @ looks for the "@" symbol, which is mandatory in an email address.

    • The second * means "any string of characters" after the "@" symbol.

    • The . represents a period, which is used in the domain part of the email (e.g., ".com", ".org").

    • The ?* after the period expects one or more characters after the period (e.g., "com", "org", etc.).

• The MATCH() function will try to match this pattern within the contents of B8. If a match is found, MATCH() returns a numeric position, meaning the pattern was found within the string.

• ISNUMBER():

  • This function checks if the result of the MATCH() function is a number.

  • If a match is found, MATCH() returns the position of the match as a number, and ISNUMBER() will return TRUE, indicating a valid match.

  • If no match is found, MATCH() will return an error, and ISNUMBER() will return FALSE.

Potential Issue:

The pattern *@*.?* does not fully validate the structure of an email because it doesn't strictly enforce the format of an email address (e.g., it might allow inputs like "abc@.com"). A better pattern would need to be stricter, ensuring that characters appear before and after the "@" and period symbols.

Recommended Improvement:

To properly validate an email format in Excel's Data Validation with a custom formula, you can use the following formula, which is more robust:

Better Formula for Email Validation:

excel

Copy code

=AND(ISNUMBER(FIND("@",B8)), ISNUMBER(FIND(".",B8, FIND("@",B8))))

Explanation:

1. FIND("@", B8): Checks if there's an "@" symbol in the text.

   • If "@" is found, it returns its position as a number.

   • If not, it returns an error.

2. FIND(".", B8, FIND("@", B8)): This checks if there's a period (.) after the "@" symbol in the text.

   • It finds the position of the period only after the "@" symbol (so it's checking the domain part of the email).

3. AND(): Ensures that both conditions are TRUE (the presence of "@" and a period after it).

If both conditions are met, the formula returns TRUE (valid email format), otherwise, it returns FALSE (invalid format).

How to Apply This Formula in Data Validation:

1. Select the Cell (e.g., B8) where you want to apply the email validation.

2. Go to the Data tab and click Data Validation.

3. In the Data Validation window:

   • Under Allow, choose Custom.

In the Formula field, paste the improved formula:
excel
Copy code
=AND(ISNUMBER(FIND("@",B8)), ISNUMBER(FIND(".",B8, FIND("@",B8))))

3. • 
     

4. Click OK.

Now, the cell will only allow inputs that meet the basic structure of an email address.

Summary:

• The original formula =ISNUMBER(MATCH("*@*.?*",B8,0)) is a basic attempt at email validation but is not precise.

• A better formula for validating email formats in Excel is =AND(ISNUMBER(FIND("@",B8)), ISNUMBER(FIND(".",B8,FIND("@",B8)))), which checks for the presence of "@" and a period after the "@" symbol in the email address.

https://www.youtube.com/watch?v=WtC9w9j-NCE&t=15s&pp=ygUYcG93ZXIgcG9pbnQgaHlwZXJsaW5raW5n

https://www.youtube.com/watch?v=kpCEP6r3L4U&t=916s&ab_channel=DeepakEduWorld