{"id":188323,"date":"2025-02-06T17:56:01","date_gmt":"2025-02-06T17:56:01","guid":{"rendered":"https:\/\/learnexams.com\/blog\/?p=188323"},"modified":"2025-02-06T17:56:03","modified_gmt":"2025-02-06T17:56:03","slug":"new-perspectives-excel-2016","status":"publish","type":"post","link":"https:\/\/www.learnexams.com\/blog\/2025\/02\/06\/new-perspectives-excel-2016\/","title":{"rendered":"New Perspectives Excel 2016"},"content":{"rendered":"\n<p>New Perspectives Excel 2016 | Module 8: SAM Project 1b<\/p>\n\n\n\n<p>Academic Support Center<\/p>\n\n\n\n<p>Advanced Functions and Conditional Formatting<\/p>\n\n\n\n<p>GETTING STARTED<br>\u00b7 Open the file NP_EX16_8b_FirstLastName_1.xlsx, available for download from the SAM website.<\/p>\n\n\n\n<p>\u00b7 Save the file as NP_EX16_8b_FirstLastName_2.xlsx by changing the \u201c1\u201d to a \u201c2\u201d.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>\u00b7 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.<\/p>\n\n\n\n<p>o If cell B6 does not display your name, delete the file and download a new copy from the SAM website.<\/p>\n\n\n\n<p>PROJECT STEPS<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>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\u2019d like to confirm that all the staff data was entered correctly into the worksheet.<\/li>\n<\/ol>\n\n\n\n<p>Go to the Staff Listing worksheet and complete the following steps:<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>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.)<\/p>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Delete the conditional formatting rule applied to the range D2:D31.<\/li>\n\n\n\n<li>In cell E2, enter a formula using the HLOOKUP function and structured references to determine a staff member\u2019s pay rate (which is based on the number of years of service).<\/li>\n<\/ol>\n\n\n\n<p>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<\/p>\n\n\n\n<p><mark style=\"background-color:rgba(0, 0, 0, 0)\" class=\"has-inline-color has-ast-global-color-6-color\"><strong>The Correct Answer and Explanation is :<\/strong><\/mark><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Step-by-Step Solution:<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Step 1: Apply Conditional Formatting to Highlight Duplicates<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Select the Range A2:A31:<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Go to the &#8220;Staff Listing&#8221; worksheet.<\/li>\n\n\n\n<li>Select the range <strong>A2:A31<\/strong> where the Employee IDs are located.<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Apply Conditional Formatting:<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Click on the &#8220;Home&#8221; tab in the Ribbon.<\/li>\n\n\n\n<li>Under the &#8220;Styles&#8221; group, click on <strong>Conditional Formatting<\/strong>.<\/li>\n\n\n\n<li>From the drop-down menu, select <strong>Highlight Cells Rules<\/strong>, then <strong>Duplicate Values<\/strong>.<\/li>\n\n\n\n<li>In the dialog box that appears, ensure that the first drop-down is set to <strong>Duplicate<\/strong>.<\/li>\n\n\n\n<li>Choose the format <strong>Light Red Fill with Dark Red Text<\/strong> and click <strong>OK<\/strong>.<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Update Duplicate Employee IDs:<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>The conditional formatting should highlight the duplicate Employee IDs. The duplicates in this case are for <strong>Terri Ortez<\/strong> and <strong>Veola Huie<\/strong>.<\/li>\n\n\n\n<li>Update their Employee IDs to <strong>2026<\/strong> (for Terri Ortez) and <strong>2030<\/strong> (for Veola Huie).<\/li>\n\n\n\n<li>After updating, the duplicates should no longer be highlighted because the Employee IDs are now unique.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Step 2: Remove Conditional Formatting from the Range D2:D31<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Select the Range D2:D31:<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Click and drag to select the range <strong>D2:D31<\/strong> in the &#8220;Staff Listing&#8221; worksheet.<\/li>\n<\/ul>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Remove the Conditional Formatting:<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>On the &#8220;Home&#8221; tab, click on <strong>Conditional Formatting<\/strong>.<\/li>\n\n\n\n<li>In the drop-down menu, choose <strong>Clear Rules<\/strong>, and select <strong>Clear Rules from Selected Cells<\/strong>.<\/li>\n\n\n\n<li>This will remove any conditional formatting applied to the range.<\/li>\n<\/ul>\n\n\n\n<h4 class=\"wp-block-heading\">Step 3: Use the HLOOKUP Function for Pay Rate<\/h4>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Enter the HLOOKUP Formula in E2:<\/strong><\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Select cell <strong>E2<\/strong> to enter the formula.<\/li>\n\n\n\n<li>The <strong>HLOOKUP<\/strong> 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:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>   =HLOOKUP(lookup_value, table_array, row_index_num, &#91;range_lookup])<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Lookup_value<\/strong>: The value you want to look up. In this case, it will be the number of years of service, which is in column <strong>D2<\/strong>.<\/li>\n\n\n\n<li><strong>Table_array<\/strong>: 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&#8217;s assume the table is in the range <strong>B1:F2<\/strong> (update as necessary based on the actual table location).<\/li>\n\n\n\n<li><strong>Row_index_num<\/strong>: The row number where the value you want to retrieve is located (in this case, row 2 for pay rates).<\/li>\n\n\n\n<li><strong>Range_lookup<\/strong>: This will be <strong>FALSE<\/strong> for an exact match. The formula should look something like this:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>   =HLOOKUP(D2, B1:F2, 2, FALSE)<\/code><\/pre>\n\n\n\n<p>This formula will look up the number of service years in <strong>D2<\/strong>, search for it in the first row of the range <strong>B1:F2<\/strong>, and return the corresponding pay rate from the second row.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Explanation of the Solution:<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Conditional Formatting<\/strong> 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.<\/li>\n\n\n\n<li><strong>Removing Conditional Formatting<\/strong> from a specific range ensures that unnecessary rules are not applied to columns that don\u2019t need them, keeping the worksheet clean and reducing distractions.<\/li>\n\n\n\n<li><strong>HLOOKUP<\/strong> 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 <strong>HLOOKUP<\/strong> 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.<\/li>\n<\/ol>\n\n\n\n<p>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.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>New Perspectives Excel 2016 | Module 8: SAM Project 1b Academic Support Center Advanced Functions and Conditional Formatting GETTING STARTED\u00b7 Open the file NP_EX16_8b_FirstLastName_1.xlsx, available for download from the SAM website. \u00b7 Save the file as NP_EX16_8b_FirstLastName_2.xlsx by changing the \u201c1\u201d to a \u201c2\u201d. o If you do not see the .xlsx file extension in [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[25],"tags":[],"class_list":["post-188323","post","type-post","status-publish","format-standard","hentry","category-exams-certification"],"_links":{"self":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/188323","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/comments?post=188323"}],"version-history":[{"count":0,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/posts\/188323\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/media?parent=188323"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/categories?post=188323"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.learnexams.com\/blog\/wp-json\/wp\/v2\/tags?post=188323"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}