1
2	Use the Data worksheet through Step 14. A unique ID for each accountant using the year hired, date hired, and number. In cell D2, enter2007-1018-10 and use Flash Fill to complete the pattern to create the remaining IDs in the range D3:D26.
3	The accountants' full names are located in column E. separate the first and last names. Select the range E2:E26 and convert text to columns using the space as the delimiter.
4	Display the accountants' names with the last name and first name, separated by a comma. In cell G2, insert the TEXTJOIN function to join the last name and first name for the first accountant, using a comma and space as the delimiter. The name should display as Adams, Camille. Copy the function to the range G3:G26.
5	Column J contains the phone numbers. extract the area codes. In cell K2, use the MID function to extract the area code for the phone number for the first employee. The Start_num argument should be the position of the first digit in the area code to avoid including the opening parenthesis. Make sure the function extracts the three-digit area code. Copy the function to the range K3:K26.
6	The Location column displays the city names in all capital letters. Because this is hard to read, you will use a text function to display the cities in upper and lowercase. In cell M2, use the PROPER function to display the first city name in upper and lowercase. Copy the function to the range M3:M26.
7	Perform an advanced filter. The criteria are (1) Tax Accountants in Miami and (2) Tax Accountants in Columbus. Copy the range A1:M1 and paste it in the range A28:M28. EnterTax Accountant andMiamiin the respective cells on row 29. EnterTax AccountantandColumbus in the respective cells on row 30. Use column M for the city names.
8	Perform the advanced filter by copying the data below the criteria range. Click or select a cell within the dataset. Select A1:M26 as the list, enter the criteria range you defined in the previous step, and typeA32:M32 for the location for the copied records.
9	identify the highest salary for Tax Accountants in Miami and Columbus. In cell P2, insert the DMAX function.
10	identify the lowest salary of Tax Accountants in Miami and Columbus. In cell P3, insert the DMIN function.
11	The range O5:P6 contains a new set of criteria to identify the one Senior Accountant in Columbus. You want to obtain that person's salary. In cell P8, insert the DGET function to extract the correct salary.
12	The range O10:P13 is designed to look up a person's name to return the salary for that person and the cell containing that salary. First, you will look up the person's salary within the dataset. In cell P12, insert an INDEX function with nested XMATCH functions. TypeF2:I26 as the array. The first nested XMATCH function looks up the name entered in cell P11 and compares it to the range containing the last names in the dataset. The second XMATCH function looks up the text "Salary" in cell O12 and compares it to the range F1:I1. In the XMATCH functions, leave the 'match mode' argument blank. In cell P11, typeUniceto test that the function returns the correct salary.
13	identify the cell that contains that salary. In cell P13, insert the ADDRESS function with nested XMATCH functions. The first XMATCH function looks up the name entered in cell P11 and compares it to the range F1:F26. The second XMATCH function looks up the text "Salary" in cell O12 and compares it to the range A1:M1. In the XMATCH functions, leave the 'match mode' argument blank. Enter the argument value to display the result as a relative cell address.
14	Document three formulas on the Data worksheet. In cell P16, insert the FORMULATEXT function to display the formula that is stored in cell P8. In cell P17, insert the FORMULATEXT function to display the formula that is stored in cell P12. In cell P18, insert the FORMULATEXT function to display the formula that is stored in cell P13.
15	The Summary worksheet is designed to look up the average salary for a specific city. Each city has its own worksheet. On each city worksheet, cell F15 contains the average salary for the accountants in that city. Display the Summary worksheet. In cell B3, insert the INDIRECT function that uses the city in cell B2 (which matches a sheet tab). Insert the symbols to point to cell F15. The function should retrieve the value in cell F15. In cell B2, typeColumbusto test that the function result is correct.
16	You will use the Accountants worksheet for the remaining tasks. That worksheet contains a similar version of the dataset used on the Data worksheet. Your first task is to create a filtered list. In cell H2, insert the FILTER function to filter the data in the dataset range A2:F26. The filter should evaluate if the data below the Title column heading equalsTax Accountant.
17	In cell O2, insert a SORT function with a nested UNIQUE function for the range of cities contained in the filtered dataset.
18	Finally, you want to list the top five salaries for tax accountants. In cell O14, insert a LARGE function with a nested SEQUENCE function. Use the range of salaries from the filtered dataset and create a sequence of the top five salaries.