Module 6b - End of Module Project 1 #part1
Description
Topics covered in this video:
- Kaito and Naomi Sato started collecting musical instruments as a hobby a few years ago and are now organizing their collection in an Excel workbook. Naomi asks for your help in updating and analyzing the data. Switch to the Brass worksheet. Unfreeze the top row of the worksheet.
- Sort the data in the Brass table first in descending order by the Purchase Date field and then in descending order by the Est. Value field.
- Insert a Total Row in the Brass table, and then use the Total row to calculate the total of the values in the Price and Est. Value fields.
- Naomi wants to highlight the brass instruments she and Kaito bought on the same date. Apply a conditional formatting rule to the range E3:E10 that uses Green Fill with Dark Green Text for duplicate values.
- Switch to the Percussion worksheet. Freeze the first column of the worksheet.
- Format the range B2:G10 as an Excel table with headers using the Gold, Table Style Medium 12 table style. Enter Percussion as the name of the table.
- Naomi and Kaito bought a new snare drum last week, and Naomi wants to include it in the Percussion table. Add the record shown in Table 1 to the end of the Percussion table. Table 1: New Record for the Percussion Table _ B C D E F G 11 Snare drum Sousa Good 2/16/22 100 150
Support 💖 Buy me a coffee: https://www.buymeacoffee.com/taandar Join Patreon: https://www.patreon.com/Taandar
#sam #spreadsheet #excel ---
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,120 --> 00:00:05,640 so this is module 6B so let's start uh
2 00:00:03,419 --> 00:00:08,040 it is saying K2 and Miami started a
3 00:00:05,640 --> 00:00:11,400 musical instruments these two brass
4 00:00:08,040 --> 00:00:13,440 worksheet and unfreeze the top row of
5 00:00:11,400 --> 00:00:15,900 the worksheet so if we scroll down so it
6 00:00:13,440 --> 00:00:19,859 will stay there so we need to unfreeze
7 00:00:15,900 --> 00:00:22,980 it so let's click on here and then go to
8 00:00:19,859 --> 00:00:24,779 view Tab and here is a free space click
9 00:00:22,980 --> 00:00:27,500 on our face now if we scroll down till
10 00:00:24,779 --> 00:00:27,500 that's still here
11 00:00:29,900 --> 00:00:35,460 stable first in descending order by
12 00:00:32,520 --> 00:00:38,640 purchase purchased it so it is saying in
13 00:00:35,460 --> 00:00:42,120 descending order so new waste is more
14 00:00:38,640 --> 00:00:44,879 than all this so this is good and then
15 00:00:42,120 --> 00:00:46,980 and then entertaining order by estimated
16 00:00:44,879 --> 00:00:51,379 value
17 00:00:46,980 --> 00:00:51,379 so largest to smallest okay
18 00:00:51,480 --> 00:00:54,960 now insert our total Row in the brush
19 00:00:54,180 --> 00:00:57,300 table
20 00:00:54,960 --> 00:00:59,760 and then use tutorial to calculate the
21 00:00:57,300 --> 00:01:01,620 total of the value in the Flies and
22 00:00:59,760 --> 00:01:04,260 establish value so price and Status
23 00:01:01,620 --> 00:01:05,939 value so to add total rule just click on
24 00:01:04,260 --> 00:01:09,360 any cell inside the table and table
25 00:01:05,939 --> 00:01:11,640 design tab will appear click in here and
26 00:01:09,360 --> 00:01:14,760 then click on this tutorial check mark
27 00:01:11,640 --> 00:01:17,280 so it will add this and now it didn't
28 00:01:14,760 --> 00:01:20,580 add the total total value of the price
29 00:01:17,280 --> 00:01:24,540 so we can add it by your thousand just
30 00:01:20,580 --> 00:01:27,600 do some function and then click here
31 00:01:24,540 --> 00:01:30,900 and then select all of them
32 00:01:27,600 --> 00:01:32,520 and then close it with close parenthesis
33 00:01:30,900 --> 00:01:36,299 and hit enter
34 00:01:32,520 --> 00:01:38,820 so this is done now
35 00:01:36,299 --> 00:01:40,079 uh yeah this is done and then we want to
36 00:01:38,820 --> 00:01:42,840 highlight the Highlight the brass
37 00:01:40,079 --> 00:01:45,720 instrument C and K to go out on the same
38 00:01:42,840 --> 00:01:48,900 date apply a condition from getting rule
39 00:01:45,720 --> 00:01:51,060 in E3 through E10 so click on E3 hold
40 00:01:48,900 --> 00:01:53,460 shift and then click then enter the
41 00:01:51,060 --> 00:01:55,439 selector here you can just drag it
42 00:01:53,460 --> 00:01:58,500 at the same conditional formatting
43 00:01:55,439 --> 00:02:01,920 highlight so for duplicate value okay
44 00:01:58,500 --> 00:02:04,320 and then if you want green green field
45 00:02:01,920 --> 00:02:06,500 with the text so just click here and
46 00:02:04,320 --> 00:02:09,539 then hit OK
47 00:02:06,500 --> 00:02:12,060 now switch to okay
48 00:02:09,539 --> 00:02:15,920 uh before switching let's check the if
49 00:02:12,060 --> 00:02:15,920 result is character not
50 00:02:16,260 --> 00:02:18,860 so
51 00:02:19,140 --> 00:02:22,980 yeah everything is valid but we messed
52 00:02:21,660 --> 00:02:27,360 up with the order
53 00:02:22,980 --> 00:02:30,599 it was saying descending for process did
54 00:02:27,360 --> 00:02:35,340 so descending means newest is more than
55 00:02:30,599 --> 00:02:36,720 one list so this will be like this
56 00:02:35,340 --> 00:02:40,220 yeah
57 00:02:36,720 --> 00:02:40,220 yeah now we are good
58 00:02:41,120 --> 00:02:48,660 so switch to the application worksheet
59 00:02:46,340 --> 00:02:51,660 freeze the First Column of the worksheet
60 00:02:48,660 --> 00:02:54,900 so we need to just click on this row and
61 00:02:51,660 --> 00:02:56,819 then go to BU and here is freeze pins
62 00:02:54,900 --> 00:02:58,440 and just click free space now if we
63 00:02:56,819 --> 00:03:01,099 scroll down
64 00:02:58,440 --> 00:03:01,099 oops
65 00:03:05,700 --> 00:03:10,980 first of all let's pick this
66 00:03:08,160 --> 00:03:13,800 I did Ctrl Z so it redo the previous
67 00:03:10,980 --> 00:03:16,019 section sending was 12 days so this week
68 00:03:13,800 --> 00:03:17,819 let's go to application
69 00:03:16,019 --> 00:03:21,900 and then
70 00:03:17,819 --> 00:03:24,900 click here view freeze paints
71 00:03:21,900 --> 00:03:26,879 uh free stop row oops
72 00:03:24,900 --> 00:03:29,940 first I will do unfreeze pins because we
73 00:03:26,879 --> 00:03:31,019 did that before so and you do freeze top
74 00:03:29,940 --> 00:03:33,200 row
75 00:03:31,019 --> 00:03:36,980 now it will do like this okay
76 00:03:33,200 --> 00:03:36,980 now we are good
77 00:03:37,140 --> 00:03:42,299 press the First Column of the worksheet
78 00:03:39,360 --> 00:03:45,120 tools we did first row
79 00:03:42,299 --> 00:03:49,500 you'd want to freeze the first column
80 00:03:45,120 --> 00:03:51,900 so again go to here and then on response
81 00:03:49,500 --> 00:03:54,799 and then click here and click freeze
82 00:03:51,900 --> 00:03:54,799 fast column
83 00:03:56,280 --> 00:04:02,280 okay uh for me the range b 2 3 through
84 00:04:01,019 --> 00:04:08,599 Z10
85 00:04:02,280 --> 00:04:08,599 so P two World shift and then click G10
86 00:04:09,140 --> 00:04:16,079 as an actual table with header using
87 00:04:12,000 --> 00:04:18,840 gold table style medium 12. so go to
88 00:04:16,079 --> 00:04:24,079 home go to format as table
89 00:04:18,840 --> 00:04:24,079 and it is saying gold medium style 12.
90 00:04:24,419 --> 00:04:28,820 so this is 20
91 00:04:26,280 --> 00:04:32,220 this is called
92 00:04:28,820 --> 00:04:35,780 so this is what we need and multiples
93 00:04:32,220 --> 00:04:35,780 are together and hit okay
94 00:04:36,300 --> 00:04:43,259 now so this is done and now I may have
95 00:04:39,840 --> 00:04:46,440 got a new snare drop last week wanted to
96 00:04:43,259 --> 00:04:49,500 get it at this so
97 00:04:46,440 --> 00:04:53,160 just copy this copy this and
98 00:04:49,500 --> 00:04:55,320 and then select here
99 00:04:53,160 --> 00:04:59,699 through here
100 00:04:55,320 --> 00:05:01,860 and then right click and then paste
101 00:04:59,699 --> 00:05:04,940 matching this Dimension format so we'll
102 00:05:01,860 --> 00:05:04,940 just do like this
103 00:05:06,479 --> 00:05:10,080 so yeah that's it for this video we'll
104 00:05:08,100 --> 00:05:12,800 continue from the question number eight
105 00:05:10,080 --> 00:05:12,800 in next video
