Module 6 - End of Module Project 1 #part2
Description
Topics covered in this video: 5. Padma wants to quickly identify the contractors who have been working with Veritate the longest. Sort the data in the Contractors table first in ascending order by the Start Year field and then in ascending order by the Last field. 6. Padma knows the Contractors table contains a duplicate record. In the range A5:A53, create a conditional formatting Highlight Cells Rule that identifies duplicate cell values by formatting them with Light Red Fill with Dark Red Text. Delete the second instance of the duplicate record, the one with a Start Year of 2022. 7. The conditional formatting rule in column G highlights cells that contain the value 5, the highest evaluation rating. Padma wants to change the highlighting to use colors that are associated with positive values. Edit the conditional formatting Highlight Cells Rule for the range G5:G52 to highlight the cells containing values equal to 5 with a font color of Teal, Accent 2, Darker 50% (6th column, 6th row in the Theme Colors palette) and a fill color of Teal, Accent 2, Lighter 60% (6th column, 3rd row in the Theme Colors palette). 8. Padma wants to list the number of years each contractor has been working for Veritate. Insert a column to the right of the Eval Rating column. Use Years as the column heading. In cell H5, insert a formula without a function that uses a structured reference to subtract the Start Year from 2022, the current year. If Excel does not automatically copy the formula to the other cells in column H, fill the range H6:H52 with the formula in cell H5. Clear the conditional formatting rule from the range H5:H52 if Excel applies it.
Support 💖 Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar
#sam #spreadsheet #excel Buy us a coffee: https://www.buymeacoffee.com/taandar ---
Disclaimer: This video is intended for educational purposes only. The purpose of this video is to help students understand how to solve problems in the Cengage business spreadsheet course. However, please note that any attempt to copy or replicate the solutions shown in this video may be considered a violation of academic integrity policies at your college or university. We strongly encourage you to use this video as a guide and to complete the assignments on your own, without resorting to cheating or plagiarism. We are not responsible for any consequences that may result from the use of this video in violation of academic integrity policies.
Transcript
1 00:00:00,000 --> 00:00:04,020 all right continuing from the step 5 it
2 00:00:02,340 --> 00:00:05,759 is saying partner wants to quickly
3 00:00:04,020 --> 00:00:07,040 identify the contractors who have been
4 00:00:05,759 --> 00:00:09,720 working with
5 00:00:07,040 --> 00:00:12,780 veritate the longest sort the data in
6 00:00:09,720 --> 00:00:14,780 the contractors table so this is the
7 00:00:12,780 --> 00:00:18,480 contractor table which we made earlier
8 00:00:14,780 --> 00:00:20,640 uh in a previous video first in
9 00:00:18,480 --> 00:00:23,340 ascending order by the start Year date
10 00:00:20,640 --> 00:00:25,619 so we start here reduction sort it in
11 00:00:23,340 --> 00:00:28,800 ascending order so is smallest to
12 00:00:25,619 --> 00:00:31,679 largest just click here and then it is
13 00:00:28,800 --> 00:00:33,000 saying and then in ascending order by
14 00:00:31,679 --> 00:00:36,420 last field
15 00:00:33,000 --> 00:00:38,399 so this also in ascending order a to z
16 00:00:36,420 --> 00:00:40,460 okay
17 00:00:38,399 --> 00:00:43,800 padman knows
18 00:00:40,460 --> 00:00:48,420 contains a duplicate record if the range
19 00:00:43,800 --> 00:00:50,420 A5 through a53 contents in the range so
20 00:00:48,420 --> 00:00:52,500 create a conditional formatting
21 00:00:50,420 --> 00:00:56,420 highlighting Circle that enable
22 00:00:52,500 --> 00:00:56,420 duplicate cell by the value
23 00:00:57,020 --> 00:01:03,120 with light read okay so first of all
24 00:01:00,180 --> 00:01:06,199 let's do step by step let's select A5
25 00:01:03,120 --> 00:01:06,199 through a53
26 00:01:06,900 --> 00:01:11,580 so this is done and then it is in create
27 00:01:09,720 --> 00:01:13,680 a conditional formatting so conditional
28 00:01:11,580 --> 00:01:16,439 formatting highlighting cell rules okay
29 00:01:13,680 --> 00:01:18,560 and that identified duplicate cell so
30 00:01:16,439 --> 00:01:21,960 duplicate values
31 00:01:18,560 --> 00:01:26,100 by formatting them with light red Fields
32 00:01:21,960 --> 00:01:27,780 so with text so hit OK
33 00:01:26,100 --> 00:01:30,299 delete the second instance of the
34 00:01:27,780 --> 00:01:32,220 duplicate trigger the one star with the
35 00:01:30,299 --> 00:01:36,600 start year of 2022
36 00:01:32,220 --> 00:01:39,720 so let's see scroll so here is this so
37 00:01:36,600 --> 00:01:42,299 just right click and then hit delete
38 00:01:39,720 --> 00:01:46,079 this is done
39 00:01:42,299 --> 00:01:48,840 now step 7 uh the condition qualiting
40 00:01:46,079 --> 00:01:52,200 rule in column Z highlights cell that
41 00:01:48,840 --> 00:01:54,600 contains value 5 the high highlight
42 00:01:52,200 --> 00:01:55,979 evaluation rating okay one want to
43 00:01:54,600 --> 00:01:59,159 change the highlighting to use the color
44 00:01:55,979 --> 00:02:02,180 that I associate with positive value so
45 00:01:59,159 --> 00:02:02,180 in the column Z
46 00:02:02,420 --> 00:02:11,780 okay from range G5 to g52
47 00:02:07,799 --> 00:02:15,480 so G5 to g52
48 00:02:11,780 --> 00:02:17,360 conditional formatting uh for the range
49 00:02:15,480 --> 00:02:20,400 that I'll add the same containing value
50 00:02:17,360 --> 00:02:22,379 equals to five so highlight cell Rule
51 00:02:20,400 --> 00:02:25,640 and then equals to
52 00:02:22,379 --> 00:02:25,640 and then enter 5
53 00:02:26,400 --> 00:02:33,780 with a font color of teal so font color
54 00:02:30,959 --> 00:02:36,480 so let's do custom format
55 00:02:33,780 --> 00:02:39,239 and then here is the font option so font
56 00:02:36,480 --> 00:02:44,160 color of teal accent too so here is the
57 00:02:39,239 --> 00:02:45,959 corner and then this is this is still
58 00:02:44,160 --> 00:02:49,680 just over it and it will give the
59 00:02:45,959 --> 00:02:52,500 information uh till accent to darker 50
60 00:02:49,680 --> 00:02:54,720 percent so sixth column sixth row so
61 00:02:52,500 --> 00:02:57,780 dark are 50 percent
62 00:02:54,720 --> 00:03:00,780 this is so just take it here and then
63 00:02:57,780 --> 00:03:03,780 after that end feel a call logs of field
64 00:03:00,780 --> 00:03:06,660 fill here and then
65 00:03:03,780 --> 00:03:09,599 color of teal accent to lighter sixty
66 00:03:06,660 --> 00:03:10,920 percent so sixth column sixth column in
67 00:03:09,599 --> 00:03:13,620 this case it is not giving information
68 00:03:10,920 --> 00:03:16,440 while hovering on it so just need more
69 00:03:13,620 --> 00:03:19,980 colors you know it will not help the
70 00:03:16,440 --> 00:03:22,260 sixth column and then third row this is
71 00:03:19,980 --> 00:03:26,280 faster this second row this third row
72 00:03:22,260 --> 00:03:28,640 and then heated this and then okay and
73 00:03:26,280 --> 00:03:28,640 then okay
74 00:03:28,860 --> 00:03:32,659 so after that um
75 00:03:34,620 --> 00:03:40,260 so this is done Padma wants to list the
76 00:03:37,860 --> 00:03:43,140 number of year uh each contractor has
77 00:03:40,260 --> 00:03:44,879 been working for veritate insular column
78 00:03:43,140 --> 00:03:46,440 to the right of the evil rating column
79 00:03:44,879 --> 00:03:49,379 so it's
80 00:03:46,440 --> 00:03:50,819 click here and then right click and then
81 00:03:49,379 --> 00:03:54,680 insert
82 00:03:50,819 --> 00:03:54,680 and then do the right
83 00:03:55,860 --> 00:04:01,200 uh here yeah here to The Right Use ears
84 00:03:59,340 --> 00:04:06,200 as the column heading so just double
85 00:04:01,200 --> 00:04:06,200 click double click and then enter ears
86 00:04:07,980 --> 00:04:13,439 here's initial adds five okay that's
87 00:04:11,280 --> 00:04:16,680 five insert a formula without a function
88 00:04:13,439 --> 00:04:19,680 that uses a so insert a formula without
89 00:04:16,680 --> 00:04:22,800 a function that so this circle gamma
90 00:04:19,680 --> 00:04:24,720 this will be a this will be a comma here
91 00:04:22,800 --> 00:04:27,600 and here so
92 00:04:24,720 --> 00:04:30,240 here which will visual insert a formula
93 00:04:27,600 --> 00:04:32,940 without a function but that formula
94 00:04:30,240 --> 00:04:36,900 should use a structural reference to
95 00:04:32,940 --> 00:04:39,960 subtract the start Year from 2022 so you
96 00:04:36,900 --> 00:04:41,060 start here from 2022
97 00:04:39,960 --> 00:04:45,479 so
98 00:04:41,060 --> 00:04:48,900 2022 any negative subtract it from start
99 00:04:45,479 --> 00:04:51,479 here so to reference the Stacked start
100 00:04:48,900 --> 00:04:53,460 here we can use the structured reference
101 00:04:51,479 --> 00:04:57,360 so structure reference by just to square
102 00:04:53,460 --> 00:04:58,510 bracket and then enter start here
103 00:04:57,360 --> 00:05:01,720 foreign
104 00:04:58,510 --> 00:05:01,720 [Music]
105 00:05:09,440 --> 00:05:14,040 formatting rule from S5 through class 52
106 00:05:12,540 --> 00:05:17,160 so as 5
107 00:05:14,040 --> 00:05:19,259 through as 52
108 00:05:17,160 --> 00:05:22,259 click here and then here is the
109 00:05:19,259 --> 00:05:26,180 conditional formatting clear rules clear
110 00:05:22,259 --> 00:05:26,180 rules from the selected sale bucket
111 00:05:27,380 --> 00:05:33,060 asked you to make a filter based on the
112 00:05:30,900 --> 00:05:34,979 starting year insert to slicer so this
113 00:05:33,060 --> 00:05:38,960 slicers part will we will be doing in
114 00:05:34,979 --> 00:05:38,960 next video so that's it for now