Logo

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

Recommended Books

Reading books is a great way to learn. Here are some of the books we recommend.