Title :Excel Practice
Problem 1-Join the name :First ,last and middle names are provided for a person .Your task is to join these to create a full name.
There are many ways to join the names in Excel
There are several ways to join or concatenate names in Excel, depending on your preference and the format of the data. Here are four common methods:
1.CONCATENATE function: The CONCATENATE function is a built-in function in Excel that allows you to join or concatenate two or more strings together. To join two cells that contain first and last names, for example, you can use the following formula:
=CONCATENATE(A1," ",B1," ",C1)
This formula concatenates the values in cell A1 (the first name) ,cell B1(middle name) and cell C1 (the last name) with a space between them.
2."&" operator: The "&" operator is another way to concatenate strings in Excel. It works in a similar way to the CONCATENATE function, but is more concise. To use the "&" operator to join two cells that contain first and last names, you can use the following formula:
=A1&" "&B1&" "C1
This formula concatenates the values in cell A1 (the first name) , cell B1(the middle name) and C1 (the last name) with a space between them.
3.Text Join function: The TEXTJOIN function is a newer function available in Excel that allows you to join strings together with a specified delimiter. To join two cells that contain first and last names with a comma between them, for example, you can use the following formula:
=TEXTJOIN(",",TRUE,A1:B1:C1)
This formula concatenates the values in cell A1 (the first name) ,cell B1(middle name) and cell C1(the last name) with a comma between them.
4.Ampersand symbol: You can also join strings together using the ampersand symbol (&) in a formula. For example, to join two cells that contain first and last names with a comma between them, you can use the following formula:
=A1&", "&B1,"&C1
This formula concatenates the values in cell A1 (the first name) ,cell B1 (the middle name) and cell C1(last name)with a comma and space between them.
Problem 2 - Separate the Name: In this problem ,the full name is given. Then ,use functions to separate the name into two parts
You can divide the name into two parts using the Flash Fill function in Excel by following these steps:
1.Enter the full name in one column (for example, in column A).
2.In the adjacent column (for example, column B), enter the first part of the name (for example, the first name).
3.In the row below the first name, enter the second part of the name (for example, the last name).
4.Excel will automatically detect the pattern and suggest a Flash Fill solution.
5.Press Enter to accept the suggestion or click on the Flash Fill button (located in the Data Tools group on the Data tab) to apply the suggestion.
6.The name will now be divided into two parts, and you can use the same Flash Fill feature to divide other names in the column.
Problem 3 - Create Email Address :In this problem first name and last name of a person is given and we have to create a email-id
You can also use the CONCATENATE function in Excel to create an email address by using the first and last name of the person. Here's how:
1.In a new column next to the column containing the names, type the formula =CONCATENATE(LOWER(B2), ".", LOWER(C2), "@gmail.com"), where B2 is the cell containing the first name, C2 is the cell containing the last name, and "@gmail.com" is the domain name of the email address.
2.Press Enter to apply the formula to the first cell, then copy the formula down to the other cells in the column.
3.The new column will now contain the email addresses created by concatenating the first and last names with the domain name.
Problem 4-Pick a Lottery Winner :Find the lottery winner randomly from the full name list.
To find a Random name from the list we use the following function:
The RANDBETWEEN function is combined with the CHOOSE function, you can randomly select a value from a list of values. Here's an example:
Suppose you have a list of names in cells A1:A5, and you want to randomly select one of them. You can use the following formula:
=CHOOSE(RANDBETWEEN(1,5), A1, A2, A3, A4, A5)
The RANDBETWEEN function generates a random number between 1 and 5, and the CHOOSE function selects the corresponding name from the list of names. Note that the list of values in the CHOOSE function must be separated by commas, and can contain up to 254 items.
Overall, the CHOOSE and RANDBETWEEN functions in Excel can be a powerful tool for randomly selecting a value from a list of values.
Problem 5-Change cases :In this problem full name of a person is given and we have to change the full name into proper, upper and lowercase
In Excel, you can change the case of letters in a cell or range of cells using several built-in functions. Here are some of the functions you can use:
1.UPPER function: Converts all the text to uppercase letters.
Example: =UPPER(A1) will convert the text in cell A1 to uppercase letters.
2.LOWER function: Converts all the text to lowercase letters.
Example: =LOWER(A1) will convert the text in cell A1 to lowercase letters.
3.PROPER function: Converts the first letter of each word to uppercase, and all other letters to lowercase.
Example: =PROPER(A1) will convert the text in cell A1 to proper case.
4.TEXT function: Allows you to apply a custom text format to the cell contents, including changing the case of letters.
Example: =TEXT(A1,"UPPER") will convert the text in cell A1 to uppercase letters.
1.To change the case of letters in a cell or range of cells, follow these steps:
2.Select the cell or range of cells that contain the text you want to change the case of.
3.Enter one of the above functions into the formula bar at the top of the Excel window, using the cell reference of the first cell in the range.
4.Press Enter to apply the function to the selected cells.
5.The text in the selected cells should now be in the case you specified.
Problem 6-Highlight Same Name Using Conditional Formatting: In this Problem we have to Highlight the duplicates names in the three parts separately. The background color should be different.
To highlight duplicate names in Excel, you can use the "Conditional Formatting" feature. Here's how to highlight the duplicate names in each part separately:
1.Select the range of cells that contains the names you want to check for duplicates.
2.Click on the "Conditional Formatting" button in the "Styles" group on the "Home" tab of the ribbon.
3.Choose "Highlight Cell Rules" > "Duplicate Values" from the dropdown menu.
4.In the "Duplicate Values" dialog box, choose "Duplicate" from the first dropdown menu.
5.In the second dropdown menu, choose the color you want to use to highlight the duplicate cells.
6.In the third dropdown menu, choose "All" to highlight all duplicates.
7.Click "OK" to close the "Duplicate Values" dialog box.
8.Repeat the above steps for each of the three parts separately.
Note : if you want to highlight duplicates in each part separately, you will need to select the range of cells for each part separately before applying the "Conditional Formatting" feature.
Problem 7-Find the Largest and Smallest Names: Using two Formulas return the largest and smallest names from the name list.
To return the largest and smallest names from a name list in Excel, you can use the 'MAX' and 'MIN' functions in combination with the LEN function. Here's how:
1.In an empty cell, enter the following formula to find the largest name:
=INDEX(A:A,MATCH(MAX(LEN(A:A)),LEN(A:A),0))
2.Replace A:A with the range of cells that contain the names you want to search.
3.Press Enter to apply the formula. The largest name in the range should be displayed in the cell.
4.In another empty cell, enter the following formula to find the smallest name:
=INDEX(A:A,MATCH(MIN(LEN(A:A)),LEN(A:A),0))
5.Replace A:A with the range of cells that contain the names you want to search.
6.Enter to apply the formula. The smallest name in the range should be displayed in the cell.
The LEN function is used to find the length of each name in the range. The MAX and MIN functions then find the maximum and minimum length values, respectively. Finally, the INDEX and MATCH functions are used to return the names that correspond to the maximum and minimum length values.
Problem 8-Count Unique Name: In this problem find the Unique names .Then , count the instances of those names.
1.To find the unique names and count their instances in Excel, you can use a combination of the UNIQUE and COUNTIF functions. Here's how:
2.Select the range of cells that contains the names you want to count.
3.In an empty cell, enter the following formula to find the unique names:
=UNIQUE(A:A)
4.Replace A:A with the range of cells that contain the names you want to search.
5.Press Enter to apply the formula. The unique names in the range should be displayed in a column.
6.In the next empty column, enter the following formula to count the instances of each unique name:
=COUNTIF(A:A,D2)
7.Replace A:A with the range of cells that contain the names you want to search, and D2 with the first cell in the column containing the unique names.
8.Press Enter to apply the formula. The instances of each unique name should be displayed in the column next to the unique names.
The UNIQUE function returns a list of unique values from the specified range. The COUNTIF function counts the number of instances of a specific value in a range. By using the COUNTIF function with each unique name, you can count the instances of each unique name in the original range.
Problem 9-Sort Name: The task is to sort the Provided names in ascending and in descending order using the Sort function.
To arrange the names in ascending and descending order in Excel, you can use the "Sort" feature. Here's how: 1. the range of cells that contains the names you want to sort. 2.Click on the "Sort & Filter" button in the "Editing" group on the "Home" tab of the ribbon. 3.Choose "Sort A to Z" to sort the names in ascending order, or choose "Sort Z to A" to sort the names in descending order. 4.If you have header rows, make sure the "My data has headers" checkbox is checked. 5.Click "OK" to close the "Sort" dialog box. 6.The names should now be sorted in the order you choose.
Problem 10-Dependent Dropdown List: Three parts of the name are given. Your objective is to create a dependent dropdown list. There will be a dropdown list with three parts of names. Then, in another cell the values from that list will be shown. For example, in the first dropdown list, you will select “first name” and in the second list the values will be from the first name column.
To create a dependent dropdown list with three parts of names in Excel, follow these steps:
1.Enter the three parts of names (e.g. "First Name", "Middle Name", "Last Name") in cells A1, B1, and C1.
2.Enter the corresponding name data in columns A, B, and C, starting from row 2.
3.Select cells A2:C2 and go to "Data" > "Data Validation" > "Data Validation" from the ribbon.
4.In the "Data Validation" dialog box, select "List" from the "Allow" dropdown.
5.In the "Source" field, enter the range of cells containing the three parts of names (A1:C1).
6.Click "OK" to close the dialog box.
7.In another cell (e.g. D2), create another data validation dropdown list.
8.Select cell D2 and go to "Data" > "Data Validation" > "Data Validation" from the ribbon.
9.In the "Data Validation" dialog box, select "List" from the "Allow" dropdown.
10.In the "Source" field, enter the following formula:
=INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(D1,A1:C1,0),4),"1","")&"2:"&SUBSTITUTE(ADDRESS(1,MATCH(D1,A1:C1,0),4),"1","")&COUNTA(INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(D1,A1:C1,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH(D1,A1:C1,0),4),"1",""))))
This formula uses the INDIRECT function to create a dynamic reference to the corresponding column based on the value selected in the first dropdown list. The MATCH function is used to find the position of the selected name part (e.g. "First Name") in the header row. The ADDRESS function is used to convert the column position to a cell reference, and the SUBSTITUTE function is used to replace the row number with an empty string. Finally, the COUNTA function is used to count the number of non-empty cells in the selected column, and the & operator is used to concatenate the cell references into a range.
11.Click "OK" to close the dialog box.
12.Now, when you select a value from the first dropdown list, the second dropdown list will show the corresponding values from the selected column.
Note: If you have a lot of data, this method may slow down your worksheet. In that case, you can use named ranges and the INDEX function to create a similar dependent dropdown list.
