Bank Account Managers – May 2021 | ||||||||
EmployeeID | Name | Branch | Speciality | Account Values | Supervisor Review | Rating | Loan Commission | New Money Commission |
70677 | Zhang, Wei | Plainfield | IRA/SEP | $ 265,111.00 | 6.5 | Poor | $ | |
54319 | Trono, Maria | Plainfield | CD/Money Market | $ 292,789.00 | 6.5 | Poor | $ | |
68835 | Carey, Bruce | Brownsburg | Checking/Savings | $ 268,750.00 | 7 | Fair | $ | |
35724 | Watson, Latisha | Avon | Checking/Savings | $ 515,896.00 | 8 | Good | $ | |
17208 | Nunez, Javier | Brownsburg | IRA/SEP | $ 575,140.00 | 8 | Good | $ | |
35724 | Watson, Latisha | Avon | CD/Money Market | $ 721,340.00 | 8.5 | Good | $ | |
42687 | Hefner, Reggie | Plainfield | Checking/Savings | $ 736,894.00 | 8.5 | Good | $ | |
49103 | Quintin, Mark | Avaon | IRA/SEP | $ 750,000.00 | 9 | Excellent | $ | |
42687 | Hefner, Reggie | Plainfield | Loans | $ 760,753.00 | 7 | Fair | $ | |
18481 | Dey, Julia | Avaon | IRA/SEP | $ 850,000.00 | 10 | Excellent | $ | |
18481 | Dey, Julia | Avaon | Loans | $ 1,745,130.00 | 10 | Excellent | $ | |
68835 | Carey, Bruce | Brownsburg | CD/Money Market | $ 2,056,123.00 | 10 | Excellent | $ | |
17208 | Nunez, Javier | Brownsburg | Loans | $ 3,265,405.00 | 9.5 | Excellent | $ | |
49103 | Quintin, Mark | Avon | Loans | $ 4,429,507.00 | 9 | Excellent | $ | |
Total | ||||||||
Part 1
Course Objectives:
· CO7: Manage and analyze data efficiently by creating tables, adding calculated columns, using functions like XLOOKUP, applying conditional formatting, and organizing data with sorting, filtering, and subtotals.
Estimated time to Complete: 20 minutes
Description:
· Creating tables.
Required Resources:
· Access to Microsoft Excel.
Deliverable:
After completing the assignment, upload the spreadsheet to the Canvas module.
Setup:
Access to Microsoft Excel.
Lab Steps:
Creating Tables
1. Create a new spreadsheet with the following data:
2. Select the header row and data range to format.
3. Click the “Format as Table” button to display the Format as Table gallery.
4. Click the table style with alternating colors.
To Name the Table
1. Click anywhere in the table and then display the Table Design tab.
2. Click the Table Name text box and enter the name “Bank_Account_Managers”.
Removing Duplicates
1. Click the Remove Duplicates button to display the Remove Duplicates dialog box.
2. Click the Select All button to select all columns.
3. Click OK to remove duplicate records from the table.
4. Click OK to finish the process.
Create an Area for Lookup and Return Arrays
1. Select the Employee ID data range. Right-click the selection and then click Format Cells on the shortcut menu to display the Format Cells dialog box.
2. Click the Alignment tab and then click the Horizontal button.
3. Click “Center Across Selection.” Click OK.
4. Click the Format Painter button and then drag through the desired cells to copy the format of the selected cell to the column headings.
Using the XLOOKUP Function
1. Enter the value you wish to lookup in a cell. Use the value 8.
2. Select another empty cell. Type the XLOOKUP function.
1.
A. For the first parameter, select the cell with the value 8.
B. For the second parameter, select the data range in Supervisor Review.
C. For the third parameter, select the data range in Rating.
The function should look similar to the following: =LOOKUP(K2,Bank_Account_Managers[Supervisor Review],Bank_Account_Managers[Rating])
3. The value that should appear in the Lookup cells should be “Good”, since that matches the value of “8”.
4. Capture a screen shot of the lookup value for the cell and upload it to Canvas.
Uploading completed worksheet
1. Save the workbook to your local drive.
