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

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		$                        53.02
54319	Trono, Maria	Plainfield	CD/Money Market	$       292,789.00	6.5	Poor		$                        58.56
68835	Carey, Bruce	Brownsburg	Checking/Savings	$       268,750.00	7	Fair		$                        93.75
35724	Watson, Latisha	Avon	Checking/Savings	$       515,896.00	8	Good		$                     103.18
17208	Nunez, Javier	Brownsburg	IRA/SEP	$       575,140.00	8	Good		$                     115.03
35724	Watson, Latisha	Avon	CD/Money Market	$       721,340.00	8.5	Good		$                     144.27
42687	Hefner, Reggie	Plainfield	Checking/Savings	$       736,894.00	8.5	Good		$                     147.38
49103	Quintin, Mark	Avaon	IRA/SEP	$       750,000.00	9	Excellent		$                     150.00
42687	Hefner, Reggie	Plainfield	Loans	$       760,753.00	7	Fair	$             1,901.88
18481	Dey, Julia	Avaon	IRA/SEP	$       850,000.00	10	Excellent		$                     170.00
18481	Dey, Julia	Avaon	Loans	$    1,745,130.00	10	Excellent	$             4,362.83
68835	Carey, Bruce	Brownsburg	CD/Money Market	$    2,056,123.00	10	Excellent		$                     411.22
17208	Nunez, Javier	Brownsburg	Loans	$    3,265,405.00	9.5	Excellent	$             8,163.51
49103	Quintin, Mark	Avon	Loans	$    4,429,507.00	9	Excellent	$           11,073.77
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:

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.

2. Upload the saved spreadsheet to Canvas.

 

Part 2

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:

Students will learn about:

·        Using icon sets with conditional formatting.

·        Inserting row and sorting table data.

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:

Adding Conditional Formatting Rule with an Icon Set

1.      Select the Supervisor Review range to contain the conditional formatting​.

2.      Click the Conditional Formatting button to display the Conditional Formatting gallery​.

3.      Click New Rule in the Conditional Formatting gallery to display the New Formatting Rule dialog box​.

4.      Click the Format Style button to display the Format Style list​.

5.      Click Icon Sets in the Format Style list to display the Icon Style area​.

6.      Click the Icon Style arrow to display the Icon Style list and then click the round icon style​.

7.      Enter the desired values for each icon in the Value box​. Green for value greater than 8.5. Red for values less than 6.5.

8.      Click OK to display icons in each row of the table

Inserting a Total Row​

1.      Click anywhere in the table and then display the Table Design tab​.

2.      Click the Total Row check box to display the total row and display the sum in the last column of the table​.

3.      Click the arrow on the right side of the cell to display a list of available functions. Select the Sum function for the selected cell in the total row.

Sorting a Table​

1.      Click the cell containing the name of the Bank Account Manager in the column to be sorted, and then click the Sort & Filter button to display the Sort & Filter menu​.

2.      Click “Sort A to Z” to sort the table in ascending order by the selected field​

3.      Click the cell containing the Account Value in the column to be sorted and display the Data tab.

4.      Click the “Sort Largest to Smallest” button to sort the table in descending sequence by the selected field.

Custom Sorting a Table​

1.      With a cell in the table active, click the “Sort & Filter” button to display the Sort & Filter menu.​

2.      Click Custom Sort on the Sort & Filter menu to display the Sort dialog box​.

3.      Click the “Column Sort by” button to display the field names in the table​.

4.      Select the “Branch” as the first field on which to sort to select the first sort level​.

5.      Select the options for Sort On “Cell Values” and Order “A to Z”​.

6.      Click the Add Level button to add a second sort level.

7.      Select the “Specialty” as the first field on which to sort to select the first sort level​.

8.      Select the options for Sort On “Cell Values” and Order “A to Z”​.

9.      Click OK to sort the table.

10.  Capture a screen shot of the spreadsheet and upload it to Canvas.

Sort a Table Using AutoFilter​

1.      Click the filter button in the Supervisor Review column to display the filter menu​.

2.      Click “Sort Smallest to Largest” on the filter menu to sort the ​table in ascending sequence by the selected field. ​

To Query a Table Using AutoFilter​

1.      Click the filter button to display the filter menu for the Supervisor Review column.​

2.      Remove the check marks next to the fields that you wish to hide (values less than 8).

3.      Click OK to apply the AutoFilter criterion.

4.      Capture a screen shot of the spreadsheet and upload it to Canvas.

Removing Filters​

1.      Display the Data tab​.

2.      Click the Clear button to display all of the records in the table​.

Searching a Table Using AutoFilter​

1.      Click the filter button in the Rating column to display the filter menu​.

2.      Click the Search box, and then type the search string​ “Good”.

3.      Click OK to perform the search.

4.      Capture a screen shot of the spreadsheet and upload it to Canvas.

Uploading completed worksheet

1.      Save the workbook to your local drive.

2.      Upload the saved spreadsheet to Canvas.

 

Part 3

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:

Students will learn about:

·        Creating criteria and extract ranges.

·        Displaying automatic subtotals.

·        Using outline features to group, hide, and unhide data.

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:

Querying Using a Criteria Range​

1.      Enter the criteria data in two new cells, one for “Branch” the other for “Plainfield”.

Branch

Plainfield

 

2.      Click the table to make it active​.

3.      Click the Advanced button to display the Advanced Filter dialog box​. Select all the cells in the table as the List range.

4.      Select the Criteria range. Then select the criteria in the data cells for filtering.

5.      Click OK to hide all records that do not meet the comparison criteria. Only the rows in the table with a branch name “Plainfield” will be displayed.

6.      Capture a screen shot of the spreadsheet and upload it to Canvas.

Extracting Records​

1.      Click the table to make it active​.

2.      Click the Advanced button to display the Advanced Filter dialog box​.

3.      Click “Copy to another location” in the Action area to cause the records that meet the criteria to be copied to a different location on the worksheet​.

4.      In the “Copy to” field, enter a cell below the table such as $A20. The records will be copied starting from this cell.

5.      Click OK to copy any records that meet the comparison criteria in the criteria range from the table to the extract range.

6.      Capture a screen shot of the spreadsheet and upload it to Canvas.

7.      Clear the filtering by clicking the Clear button.

Using the DAVERAGE and DCOUNT Database Functions​

1.      With the desired cell selected, type the DAVERAGE function or DCOUNT function​.

Ex: =DAVERAGE(A2:I16,”Supervisor Review”,F3:F16)

Ex: =DCOUNT(A2:I16,”Supervisor Review”,F3:F16)

2.      Capture a screen shot of the spreadsheet and upload it to Canvas.

Converting a Table to a Range​

1.      Right-click anywhere in the table and point to Table on the shortcut menu to display the Table submenu​.

2.      Click “Convert to Range” to display a Microsoft Excel dialog box​.

3.      Click the Yes button to convert a table to a range.

Displaying Subtotals​

1.      Sort the entire range by Branch name.

2.      Click the Subtotal button to display the Subtotal dialog box from the Outline menu​.

3.      Click the “At each change in” button and then select the Branch control field​.

4.      Click the “Use function” and select Sum.

5.      In the “Add subtotal to” list, click the “Account Values” to subtotal​.

6.      Click OK to add subtotals to the range​.

7.      Capture a screen shot of the spreadsheet and upload it to Canvas.

Uploading completed worksheet

1.      Save the workbook to your local drive.