Module 4: End of Module Project 1

1 00:00:02,340 --> 00:00:06,720 hello what's going on everybody so today

2 00:00:04,620 --> 00:00:07,919 we are doing module 4 in the module

3 00:00:06,720 --> 00:00:10,320 project one

4 00:00:07,919 --> 00:00:11,940 so you have the instructions so in step

5 00:00:10,320 --> 00:00:13,920 one it is saying

6 00:00:11,940 --> 00:00:15,839 um switch to the sales by product works

7 00:00:13,920 --> 00:00:20,160 here so we are on the same worksheet and

8 00:00:15,839 --> 00:00:23,340 in range G7 through G18 so G7 through

9 00:00:20,160 --> 00:00:26,820 G18 add column sparkline based on the

10 00:00:23,340 --> 00:00:29,279 data in the range B7 through F18 okay so

11 00:00:26,820 --> 00:00:31,080 for that go to insert Tab and then

12 00:00:29,279 --> 00:00:33,300 sparkline group and then here is the

13 00:00:31,080 --> 00:00:34,920 columns pad line and for the data

14 00:00:33,300 --> 00:00:35,600 arrange it is saying

15 00:00:34,920 --> 00:00:41,100 um

16 00:00:35,600 --> 00:00:44,300 G7 oh now yeah B7 3F 18 so

17 00:00:41,100 --> 00:00:48,120 and the B7 through

18 00:00:44,300 --> 00:00:50,340 F18 and then hit OK

19 00:00:48,120 --> 00:00:53,640 now after that it is saying

20 00:00:50,340 --> 00:00:57,600 and then apply green accent 6 darker 25

21 00:00:53,640 --> 00:00:59,699 percent Spike line color so for that

22 00:00:57,600 --> 00:01:01,620 your sparkling tab will be appeared once

23 00:00:59,699 --> 00:01:04,019 you edit this and then go to this

24 00:01:01,620 --> 00:01:07,140 backline color and then it was saying

25 00:01:04,019 --> 00:01:10,140 green darker 25 so this is it

26 00:01:07,140 --> 00:01:15,119 okay now step two it is saying create a

27 00:01:10,140 --> 00:01:17,520 python based on the Range F7 through f17

28 00:01:15,119 --> 00:01:20,580 through app 17

29 00:01:17,520 --> 00:01:23,220 specify the range A7 through a17 as the

30 00:01:20,580 --> 00:01:25,619 horizontal axis level so make sure you

31 00:01:23,220 --> 00:01:27,420 also select this once people just select

32 00:01:25,619 --> 00:01:29,340 this and then create a pyzer and then it

33 00:01:27,420 --> 00:01:31,140 will get wrong so for this you need to

34 00:01:29,340 --> 00:01:34,080 read up to here and then make sure you

35 00:01:31,140 --> 00:01:37,380 select the strength and then also hold

36 00:01:34,080 --> 00:01:38,700 Ctrl key and then drag it until it is

37 00:01:37,380 --> 00:01:40,200 17.

38 00:01:38,700 --> 00:01:42,799 oops

39 00:01:40,200 --> 00:01:46,860 so firstly let's select

40 00:01:42,799 --> 00:01:49,259 F7 through f17 and then hold Ctrl key

41 00:01:46,860 --> 00:01:52,560 and then keep pressing Ctrl key and then

42 00:01:49,259 --> 00:01:54,360 drag until A7 through a17 so this way we

43 00:01:52,560 --> 00:01:56,820 can select both Reds

44 00:01:54,360 --> 00:01:58,259 so after that go here and then it is

45 00:01:56,820 --> 00:02:01,259 saying create a pie chart

46 00:01:58,259 --> 00:02:04,079 so here's the pie chart

47 00:02:01,259 --> 00:02:05,700 all right now it is saying this item

48 00:02:04,079 --> 00:02:10,560 reposition the chart so that I call it

49 00:02:05,700 --> 00:02:10,560 Corner S6 and then q30 so

50 00:02:11,160 --> 00:02:17,420 Max Prime this

51 00:02:13,200 --> 00:02:17,420 this thing at six

52 00:02:19,680 --> 00:02:23,280 okay q30

53 00:02:28,260 --> 00:02:34,340 so

54 00:02:30,360 --> 00:02:34,340 is the q30 okay

55 00:02:34,379 --> 00:02:39,780 and then it is saying enter this as a

56 00:02:37,080 --> 00:02:41,340 chart title and apply chart Style six

57 00:02:39,780 --> 00:02:45,080 okay

58 00:02:41,340 --> 00:02:45,080 so just copy it

59 00:02:45,900 --> 00:02:51,060 and the next one this and click on chart

60 00:02:48,180 --> 00:02:53,519 title and click on this bar and paste it

61 00:02:51,060 --> 00:02:55,680 and then hit enter and it starts saying

62 00:02:53,519 --> 00:02:58,019 sorry style three

63 00:02:55,680 --> 00:02:59,760 okay so go to chart design and the next

64 00:02:58,019 --> 00:03:03,060 part this

65 00:02:59,760 --> 00:03:05,459 it wasn't still three or six

66 00:03:03,060 --> 00:03:08,120 I guess it's six

67 00:03:05,459 --> 00:03:11,519 yeah six

68 00:03:08,120 --> 00:03:14,220 okay so just overrun it and it will so

69 00:03:11,519 --> 00:03:15,540 yeah this is style six

70 00:03:14,220 --> 00:03:17,760 okay

71 00:03:15,540 --> 00:03:19,739 so step three is down now step 4 edition

72 00:03:17,760 --> 00:03:25,140 create a 2d line chart based on the

73 00:03:19,739 --> 00:03:28,140 Range B18 through F18 so B18

74 00:03:25,140 --> 00:03:30,780 be 18 through F18

75 00:03:28,140 --> 00:03:34,319 modifies that by changing the horizontal

76 00:03:30,780 --> 00:03:36,239 axis level uh to use the range b63 F6 so

77 00:03:34,319 --> 00:03:41,480 in this also we should select both range

78 00:03:36,239 --> 00:03:45,120 so hold Ctrl so B6 oops

79 00:03:41,480 --> 00:03:46,920 B6 through F6

80 00:03:45,120 --> 00:03:49,920 okay

81 00:03:46,920 --> 00:03:52,620 so it is saying 2D lines at so for that

82 00:03:49,920 --> 00:03:55,500 go to insert Tab and then

83 00:03:52,620 --> 00:03:58,340 here is the line chart and this is 2D

84 00:03:55,500 --> 00:03:58,340 Lancer okay

85 00:03:58,980 --> 00:04:03,180 so it is saying

86 00:04:00,840 --> 00:04:04,739 and this return enter this as the third

87 00:04:03,180 --> 00:04:07,319 title okay

88 00:04:04,739 --> 00:04:08,760 so just copy these

89 00:04:07,319 --> 00:04:11,280 oops

90 00:04:08,760 --> 00:04:13,560 copy this

91 00:04:11,280 --> 00:04:15,299 and then click on title and then go to

92 00:04:13,560 --> 00:04:17,400 status bar and paste it

93 00:04:15,299 --> 00:04:19,859 so then just double click on it and

94 00:04:17,400 --> 00:04:22,620 paste it because it might add this bold

95 00:04:19,859 --> 00:04:24,900 and change the font color so just to be

96 00:04:22,620 --> 00:04:26,880 on Save side click on this and then go

97 00:04:24,900 --> 00:04:28,199 to status bar and just paste it and it

98 00:04:26,880 --> 00:04:30,540 will replace

99 00:04:28,199 --> 00:04:34,080 okay so it is saying resize and

100 00:04:30,540 --> 00:04:36,860 reposition 820 through G37

101 00:04:34,080 --> 00:04:36,860 so

102 00:04:36,960 --> 00:04:42,900 here's the 820

103 00:04:39,180 --> 00:04:45,440 and then G37

104 00:04:42,900 --> 00:04:49,139 so Z

105 00:04:45,440 --> 00:04:52,560 37 okay

106 00:04:49,139 --> 00:04:54,540 now it is saying Apple it's artist style

107 00:04:52,560 --> 00:04:55,320 13 through the lines that you just

108 00:04:54,540 --> 00:04:57,720 created

109 00:04:55,320 --> 00:04:58,979 okay so just click here and then go to

110 00:04:57,720 --> 00:05:01,440 chart design

111 00:04:58,979 --> 00:05:04,800 and then let's expand this

112 00:05:01,440 --> 00:05:06,720 and then this 15 this 14 and this must

113 00:05:04,800 --> 00:05:09,060 be 13 okay

114 00:05:06,720 --> 00:05:10,680 and then this thing yeah this is done

115 00:05:09,060 --> 00:05:12,720 dinosaur 13.

116 00:05:10,680 --> 00:05:16,500 ferment the vertical axis to use the

117 00:05:12,720 --> 00:05:18,660 maximum value of 8 000. so for that what

118 00:05:16,500 --> 00:05:20,160 you need to do is just double click on

119 00:05:18,660 --> 00:05:22,860 this chart and then this section will

120 00:05:20,160 --> 00:05:25,320 appear or you can just click plus sign

121 00:05:22,860 --> 00:05:27,600 and then axis are just anything and just

122 00:05:25,320 --> 00:05:29,639 click on more option and this tab will

123 00:05:27,600 --> 00:05:32,280 appear and then it was saying vertical

124 00:05:29,639 --> 00:05:34,259 axis so click on this drop down and then

125 00:05:32,280 --> 00:05:37,259 select vertical axis

126 00:05:34,259 --> 00:05:41,160 and then go to this icon axis option

127 00:05:37,259 --> 00:05:43,940 icon and then expand access options and

128 00:05:41,160 --> 00:05:43,940 then it was saying

129 00:05:44,660 --> 00:05:51,000 8000 for maximum value okay so change

130 00:05:48,240 --> 00:05:53,460 this 9000 to a thousand

131 00:05:51,000 --> 00:05:56,820 and then it enter and then which is

132 00:05:53,460 --> 00:05:58,440 saying since display units 2000 so you

133 00:05:56,820 --> 00:06:01,380 will display in it click on this drop

134 00:05:58,440 --> 00:06:04,259 down in the thousands and then but don't

135 00:06:01,380 --> 00:06:07,020 show the unit on the side Okay so

136 00:06:04,259 --> 00:06:08,940 uncheck that check mark and then it is

137 00:06:07,020 --> 00:06:10,979 saying show zero decimal places in the

138 00:06:08,940 --> 00:06:12,479 axis level So currently there are two

139 00:06:10,979 --> 00:06:15,539 decimal places

140 00:06:12,479 --> 00:06:18,479 so click on this number drop down and

141 00:06:15,539 --> 00:06:22,020 then change this decimal places to zero

142 00:06:18,479 --> 00:06:23,340 and then hit enter so step 5 is down now

143 00:06:22,020 --> 00:06:25,500 step six

144 00:06:23,340 --> 00:06:27,960 let me cancel this okay

145 00:06:25,500 --> 00:06:30,060 so step six it is saying create a

146 00:06:27,960 --> 00:06:32,699 stacked column chart based on the Range

147 00:06:30,060 --> 00:06:37,919 A6 through f17

148 00:06:32,699 --> 00:06:40,139 okay so let's select a 6 through f17

149 00:06:37,919 --> 00:06:43,080 and then it is saying

150 00:06:40,139 --> 00:06:45,060 stacked column chart so go to insert

151 00:06:43,080 --> 00:06:48,360 and then here

152 00:06:45,060 --> 00:06:50,580 so stacked columns are okay so this is

153 00:06:48,360 --> 00:06:53,160 three requires are

154 00:06:50,580 --> 00:06:57,000 okay if if it does not specify anything

155 00:06:53,160 --> 00:06:58,199 then it is 2D so today is default one

156 00:06:57,000 --> 00:07:01,139 okay

157 00:06:58,199 --> 00:07:03,180 so it said it's that columns are and

158 00:07:01,139 --> 00:07:04,680 then modify the chart by switching the

159 00:07:03,180 --> 00:07:06,419 row and column so that horizontal

160 00:07:04,680 --> 00:07:10,319 relaxes so ears and the Stacked

161 00:07:06,419 --> 00:07:11,880 component of each bar are products so

162 00:07:10,319 --> 00:07:15,240 let's

163 00:07:11,880 --> 00:07:17,699 so it is so for that click on the chart

164 00:07:15,240 --> 00:07:20,400 and then go to chart design and then

165 00:07:17,699 --> 00:07:23,479 here is the Swiss row column okay so now

166 00:07:20,400 --> 00:07:25,740 this way so you're sensoring um

167 00:07:23,479 --> 00:07:28,919 horizontal axis

168 00:07:25,740 --> 00:07:32,220 now it is saying we're here

169 00:07:28,919 --> 00:07:35,220 enter this as a chart title okay

170 00:07:32,220 --> 00:07:37,919 so copy it and then click on start title

171 00:07:35,220 --> 00:07:41,060 and let me close this and then go to the

172 00:07:37,919 --> 00:07:44,819 status bar paste it and hit enter

173 00:07:41,060 --> 00:07:47,400 noticing resize and reposition so a38 to

174 00:07:44,819 --> 00:07:49,560 G63

175 00:07:47,400 --> 00:07:52,560 Okay so

176 00:07:49,560 --> 00:07:52,560 838

177 00:07:53,759 --> 00:07:59,400 so a38

178 00:07:56,460 --> 00:08:03,240 2z63

179 00:07:59,400 --> 00:08:05,460 okay so here is the 63.

180 00:08:03,240 --> 00:08:08,340 all right

181 00:08:05,460 --> 00:08:11,099 now it is saying app lights are Title 9

182 00:08:08,340 --> 00:08:13,440 to the stack column so click here and

183 00:08:11,099 --> 00:08:16,380 then start design and then export this

184 00:08:13,440 --> 00:08:18,120 and then this must be the nine okay and

185 00:08:16,380 --> 00:08:19,919 click here

186 00:08:18,120 --> 00:08:22,139 okay and it is saying format the

187 00:08:19,919 --> 00:08:24,000 vertical axis to use the maximum value

188 00:08:22,139 --> 00:08:26,400 of your thousand so same as previous

189 00:08:24,000 --> 00:08:29,400 double click on it and then from this

190 00:08:26,400 --> 00:08:31,860 drop down select vertical axis and then

191 00:08:29,400 --> 00:08:34,740 click on this icon axis option and then

192 00:08:31,860 --> 00:08:38,159 click on axis of some drop down and then

193 00:08:34,740 --> 00:08:41,039 change this 9000 to 8000

194 00:08:38,159 --> 00:08:44,279 and then change display unit 2000 okay

195 00:08:41,039 --> 00:08:46,580 so same as above so thousands I can

196 00:08:44,279 --> 00:08:49,800 uncheck this stigma and this thing

197 00:08:46,580 --> 00:08:52,500 seriously small classes so same thing

198 00:08:49,800 --> 00:08:54,540 so remove these two and then enter zero

199 00:08:52,500 --> 00:08:57,180 okay

200 00:08:54,540 --> 00:08:59,459 so now step 8 it is saying create 3D

201 00:08:57,180 --> 00:09:02,160 clustered column chart

202 00:08:59,459 --> 00:09:04,200 so I said as I said before if it needs

203 00:09:02,160 --> 00:09:06,779 3D then it will mention 3D if it doesn't

204 00:09:04,200 --> 00:09:08,820 mention anything then it's 2D so greater

205 00:09:06,779 --> 00:09:11,519 than 3D clustered columns are based on

206 00:09:08,820 --> 00:09:13,920 the Range A6 through f17

207 00:09:11,519 --> 00:09:17,160 okay so let me close this

208 00:09:13,920 --> 00:09:20,700 oops it didn't change this to

209 00:09:17,160 --> 00:09:23,339 zero decimal places let's make sure

210 00:09:20,700 --> 00:09:26,100 so go here and then

211 00:09:23,339 --> 00:09:27,360 is there thousand thousand and then go

212 00:09:26,100 --> 00:09:29,459 to number

213 00:09:27,360 --> 00:09:32,339 and then zero

214 00:09:29,459 --> 00:09:35,399 okay let me close and reopen this uh

215 00:09:32,339 --> 00:09:37,620 Excel file let's see

216 00:09:35,399 --> 00:09:41,220 okay so I saved that project and reopen

217 00:09:37,620 --> 00:09:43,320 it and it is fixed so when you have same

218 00:09:41,220 --> 00:09:47,000 problem make sure you save the project

219 00:09:43,320 --> 00:09:49,380 and then reopen sometime it shows Glitz

220 00:09:47,000 --> 00:09:51,300 now stay where did you saying create a

221 00:09:49,380 --> 00:09:58,320 3D clustered column chart based on the

222 00:09:51,300 --> 00:10:00,060 Range A6 to f17 so A6 through at 17 and

223 00:09:58,320 --> 00:10:03,540 then click on insert and then it is

224 00:10:00,060 --> 00:10:06,240 saying 3D clustered columns are so 3D or

225 00:10:03,540 --> 00:10:07,700 anything so 3D clustered column okay so

226 00:10:06,240 --> 00:10:09,779 click here

227 00:10:07,700 --> 00:10:14,339 and then it is saying resize and

228 00:10:09,779 --> 00:10:18,240 reposition as 32 to q52

229 00:10:14,339 --> 00:10:20,940 so as 30 so let's drag it

230 00:10:18,240 --> 00:10:23,160 to as 32

231 00:10:20,940 --> 00:10:27,480 okay this is 31

232 00:10:23,160 --> 00:10:30,480 as 32. okay as 32 through

233 00:10:27,480 --> 00:10:30,480 q52

234 00:10:32,880 --> 00:10:36,860 okay let's make sure so this is 50

235 00:10:37,500 --> 00:10:42,180 50.

236 00:10:38,940 --> 00:10:44,540 okay this is 52. all right

237 00:10:42,180 --> 00:10:47,459 now it is saying a remove

238 00:10:44,540 --> 00:10:50,519 2021.22 and 2023 series from The Legend

239 00:10:47,459 --> 00:10:52,500 and sad area okay

240 00:10:50,519 --> 00:10:55,860 so further click on the chart go to

241 00:10:52,500 --> 00:10:59,420 chart design and then select data and

242 00:10:55,860 --> 00:11:01,019 then it is in 2021 remove 2021

243 00:10:59,420 --> 00:11:03,720 2022

244 00:11:01,019 --> 00:11:04,680 and then point twenty three and then hit

245 00:11:03,720 --> 00:11:07,380 OK

246 00:11:04,680 --> 00:11:09,899 so this way those are removed from the

247 00:11:07,380 --> 00:11:11,880 Legend and also from chart area

248 00:11:09,899 --> 00:11:14,040 okay

249 00:11:11,880 --> 00:11:15,360 noticing enter this here's the third

250 00:11:14,040 --> 00:11:17,640 title

251 00:11:15,360 --> 00:11:20,100 okay and then firmware the access title

252 00:11:17,640 --> 00:11:23,160 and 16 point

253 00:11:20,100 --> 00:11:25,980 bold text okay

254 00:11:23,160 --> 00:11:28,140 so click on start title and then paste

255 00:11:25,980 --> 00:11:30,300 it on the status bar and then it is

256 00:11:28,140 --> 00:11:32,459 saying so select all these text

257 00:11:30,300 --> 00:11:35,940 and then

258 00:11:32,459 --> 00:11:38,279 16 point and then bold text oops bold

259 00:11:35,940 --> 00:11:39,779 okay okay so step three it is saying

260 00:11:38,279 --> 00:11:42,300 change the background color of the plot

261 00:11:39,779 --> 00:11:44,040 area to white background one and then

262 00:11:42,300 --> 00:11:47,399 change the pattern color of the chart

263 00:11:44,040 --> 00:11:49,920 area to green accent six so let's

264 00:11:47,399 --> 00:11:52,500 overrun it so this is chart area and

265 00:11:49,920 --> 00:11:55,680 then it is saying green accent six

266 00:11:52,500 --> 00:11:58,620 okay so background color and lighter 80

267 00:11:55,680 --> 00:12:01,320 percent so go to format

268 00:11:58,620 --> 00:12:03,660 and then here is the save fill and then

269 00:12:01,320 --> 00:12:07,260 you see green accent ZX slider 80

270 00:12:03,660 --> 00:12:09,839 percent okay now for this plot area

271 00:12:07,260 --> 00:12:13,320 select on it and then it is saying white

272 00:12:09,839 --> 00:12:17,519 background one so for that save fill and

273 00:12:13,320 --> 00:12:22,100 then white background one okay

274 00:12:17,519 --> 00:12:22,100 so this is done so let's make sure

275 00:12:22,200 --> 00:12:28,380 okay this is good

276 00:12:24,540 --> 00:12:31,200 and then yes this is also good and then

277 00:12:28,380 --> 00:12:33,540 okay Digital support

278 00:12:31,200 --> 00:12:37,200 and then let's check here

279 00:12:33,540 --> 00:12:41,100 okay good and then this is also good

280 00:12:37,200 --> 00:12:43,139 oh we forgot to add the axis title

281 00:12:41,100 --> 00:12:45,300 okay

282 00:12:43,139 --> 00:12:49,579 let's make sure

283 00:12:45,300 --> 00:12:49,579 enter this as the vertical axis title

284 00:12:50,279 --> 00:12:55,320 okay so copy on it

285 00:12:52,320 --> 00:12:58,200 and then click here and then plus sign

286 00:12:55,320 --> 00:12:59,820 and then access title and then primary

287 00:12:58,200 --> 00:13:02,180 vertical

288 00:12:59,820 --> 00:13:02,180 okay

289 00:13:02,459 --> 00:13:10,820 and then here and then go to status bar

290 00:13:05,639 --> 00:13:10,820 paste it all right so this is done