New Perspectives Excel 2016

/ Exams & Certification / By

New Perspectives Excel 2016 | Module 8: SAM Project 1b

Academic Support Center

Advanced Functions and Conditional Formatting

GETTING STARTED
· Open the file NP_EX16_8b_FirstLastName_1.xlsx, available for download from the SAM website.

· Save the file as NP_EX16_8b_FirstLastName_2.xlsx by changing the “1” to a “2”.

o If you do not see the .xlsx file extension in the Save As dialog box, do not type it. The program will add the file extension for you automatically.

· With the file NP_EX16_8b_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet.

o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.

PROJECT STEPS

  1. Luke Alberto is the director of the Academic Support Center at Ocean Side College. He started planning the tutoring staffing assignments for the upcoming academic year in an Excel table. Luke needs your help completing the table. First, he’d like to confirm that all the staff data was entered correctly into the worksheet.

Go to the Staff Listing worksheet and complete the following steps:

a. Apply a conditional formatting Highlight Cells Rule to the range A2:A31 that formats any duplicate values with Light Red Fill with Dark Red Text.

b. Correct the duplicate values by updating the Employee ID for Terri Ortez to 2026 and the Employee ID for Veola Huie to 2030. (Hint: When this sub-step is completed, the conditional formatting rule should no longer highlight any values in the range.)

  1. Delete the conditional formatting rule applied to the range D2:D31.
  2. In cell E2, enter a formula using the HLOOKUP function and structured references to determine a staff member’s pay rate (which is based on the number of years of service).

a. Use a structured reference to look up the value in the Service Years column. Retrieve the value in the 2nd row of the table in the range

The Correct Answer and Explanation is :

Step-by-Step Solution:

Step 1: Apply Conditional Formatting to Highlight Duplicates

  1. Select the Range A2:A31:
  • Go to the “Staff Listing” worksheet.
  • Select the range A2:A31 where the Employee IDs are located.
  1. Apply Conditional Formatting:
  • Click on the “Home” tab in the Ribbon.
  • Under the “Styles” group, click on Conditional Formatting.
  • From the drop-down menu, select Highlight Cells Rules, then Duplicate Values.
  • In the dialog box that appears, ensure that the first drop-down is set to Duplicate.
  • Choose the format Light Red Fill with Dark Red Text and click OK.
  1. Update Duplicate Employee IDs:
  • The conditional formatting should highlight the duplicate Employee IDs. The duplicates in this case are for Terri Ortez and Veola Huie.
  • Update their Employee IDs to 2026 (for Terri Ortez) and 2030 (for Veola Huie).
  • After updating, the duplicates should no longer be highlighted because the Employee IDs are now unique.

Step 2: Remove Conditional Formatting from the Range D2:D31

  1. Select the Range D2:D31:
  • Click and drag to select the range D2:D31 in the “Staff Listing” worksheet.
  1. Remove the Conditional Formatting:
  • On the “Home” tab, click on Conditional Formatting.
  • In the drop-down menu, choose Clear Rules, and select Clear Rules from Selected Cells.
  • This will remove any conditional formatting applied to the range.

Step 3: Use the HLOOKUP Function for Pay Rate

  1. Enter the HLOOKUP Formula in E2:
  • Select cell E2 to enter the formula.
  • The HLOOKUP function searches for a value in the first row of a table and returns a value from another row in the same column. The formula structure is:
   =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
  • Lookup_value: The value you want to look up. In this case, it will be the number of years of service, which is in column D2.
  • Table_array: The range that contains the pay rate information. This will be the table that includes the years of service (probably in row 1) and pay rates in row 2. Let’s assume the table is in the range B1:F2 (update as necessary based on the actual table location).
  • Row_index_num: The row number where the value you want to retrieve is located (in this case, row 2 for pay rates).
  • Range_lookup: This will be FALSE for an exact match. The formula should look something like this:
   =HLOOKUP(D2, B1:F2, 2, FALSE)

This formula will look up the number of service years in D2, search for it in the first row of the range B1:F2, and return the corresponding pay rate from the second row.

Explanation of the Solution:

  1. Conditional Formatting helps identify duplicate values in a dataset. By applying the highlight rule to Employee IDs, the duplicates stand out, making it easier to spot and correct errors. After correcting the duplicates, the data is accurate and well-formatted.
  2. Removing Conditional Formatting from a specific range ensures that unnecessary rules are not applied to columns that don’t need them, keeping the worksheet clean and reducing distractions.
  3. HLOOKUP is used when you need to look up a value in a row (instead of a column like in VLOOKUP). In this case, we use HLOOKUP to match the number of years of service with the appropriate pay rate. Structured references in Excel help simplify formula creation by referring directly to table column names, improving readability and reducing the likelihood of errors.

By completing these tasks, you ensure that the worksheet is both accurate and easy to navigate for Luke Alberto, the director, as he uses it to plan staffing assignments.

Related Posts

Scroll to Top