1 00:00:04,810 --> 00:00:08,960 Let's take a look at our update and protect challenge. Number one. 2 00:00:09,010 --> 00:00:13,300 Move the row "Twistrr" to be between "Baden" and "Games other". 3 00:00:13,300 --> 00:00:15,340 So this is where "Twistrr" is sitting. 4 00:00:15,340 --> 00:00:16,570 That's the row. 5 00:00:16,680 --> 00:00:20,760 I want to move it to be between these two. Now there are different ways I can do this. 6 00:00:20,800 --> 00:00:26,340 I can use the shortcut key control+X and then paste the row in between these two. 7 00:00:26,530 --> 00:00:34,750 I can cut and then insert the row in between these two. Or, I can also use what we just learned before 8 00:00:35,120 --> 00:00:38,610 is just drag this down while I'm dragging it down 9 00:00:38,620 --> 00:00:43,930 I'm going to hold on to the shift key and then I'm going to let go of the mouse and it's going to drop the 10 00:00:43,930 --> 00:00:47,400 entire row between Baden and Games other. 11 00:00:47,410 --> 00:00:48,530 So that was number one. 12 00:00:48,550 --> 00:00:49,440 That was easy. 13 00:00:49,450 --> 00:00:51,300 Number one is done. 14 00:00:51,410 --> 00:00:58,830 Number two. Protect only the cells that have formulas - leave all other cells open. 15 00:00:59,020 --> 00:01:00,720 So let's just take a look. 16 00:01:00,730 --> 00:01:02,450 This one has a formula. 17 00:01:02,500 --> 00:01:04,569 These don't have formulas. 18 00:01:04,569 --> 00:01:05,650 This one has a formula. 19 00:01:05,680 --> 00:01:10,740 So basically any cell that has a formula we want protected. 20 00:01:10,780 --> 00:01:12,700 Anything else should be open. 21 00:01:12,790 --> 00:01:17,500 So first challenge is to find all the formula cells. 22 00:01:17,510 --> 00:01:20,360 Now, this is a small dataset here. 23 00:01:20,380 --> 00:01:26,010 It's easy also just to go through them and find them but you might be working with bigger datasets. 24 00:01:26,050 --> 00:01:29,010 The feature we can use for that is the go to special 25 00:01:29,070 --> 00:01:31,440 and we can find all formula cells. 26 00:01:31,510 --> 00:01:33,540 So that would be the number one challenge solved 27 00:01:33,550 --> 00:01:40,060 but how do we then just protect those and not protect the others. By default, 28 00:01:40,180 --> 00:01:45,900 each cell in an Excel worksheet has protection settings on. 29 00:01:45,940 --> 00:01:52,030 So if you use a shortcut key control+1 to go to format cells or just right mouse click, format cells, 30 00:01:52,150 --> 00:01:53,890 and go to protection. 31 00:01:53,890 --> 00:01:57,210 Each cell already has a checkmark for locked. 32 00:01:57,490 --> 00:02:03,010 What we want to do is unlock everything so all the cells in this sheet. 33 00:02:03,760 --> 00:02:09,600 And then with go to special only lock the cells that have formulas with them. 34 00:02:10,020 --> 00:02:10,280 OK. 35 00:02:10,300 --> 00:02:11,540 So let's do that. 36 00:02:11,650 --> 00:02:13,900 Let's just highlight everything. 37 00:02:13,900 --> 00:02:16,060 Use a shortcut key control+1. 38 00:02:16,060 --> 00:02:20,100 Take away the check mark for locked and then click on OK. 39 00:02:20,230 --> 00:02:23,400 So now by default, nothing will be locked 40 00:02:23,470 --> 00:02:25,970 when I go and protect this sheet. 41 00:02:26,050 --> 00:02:32,440 Before I go protect I need to make sure that the formula cells actually have a checkmark because 42 00:02:32,440 --> 00:02:33,640 those I want locked. 43 00:02:33,640 --> 00:02:34,580 Right? 44 00:02:34,630 --> 00:02:41,470 To go and select the formula cells I can use a shortcut key for go to special control+G and then 45 00:02:41,470 --> 00:02:45,110 click on special and we get to go to special. 46 00:02:45,150 --> 00:02:52,870 Now let's select all the formula cells and I'm OK with all these, let's go with OK, all the formula cells 47 00:02:52,870 --> 00:02:54,310 here are selected. 48 00:02:54,340 --> 00:03:03,250 Now let's go back to control+1 so basically to format cells and activate locked for them. So that once 49 00:03:03,250 --> 00:03:06,550 these sheet is protected these are going to stay locked. 50 00:03:06,550 --> 00:03:08,860 The other ones are not going to be locked. 51 00:03:08,860 --> 00:03:11,950 Let's go to review and protect the sheets. 52 00:03:12,020 --> 00:03:13,800 Then we can give this a password. 53 00:03:13,810 --> 00:03:15,610 I'm not going to put a password. 54 00:03:15,610 --> 00:03:20,270 Now just click on OK. That was number two. 55 00:03:20,330 --> 00:03:21,000 Number two is done 56 00:03:21,200 --> 00:03:23,470 and number three needs us to test it. 57 00:03:23,500 --> 00:03:27,180 So we should be able to input in cell E9. 58 00:03:27,220 --> 00:03:29,090 Let's go and try that E9 59 00:03:29,260 --> 00:03:31,670 but not in cell E12 60 00:03:31,740 --> 00:03:32,040 OK. 61 00:03:32,080 --> 00:03:34,300 So let's just make an input. 62 00:03:34,300 --> 00:03:36,920 35000, enter. 63 00:03:37,000 --> 00:03:39,870 It works but not in E12. 64 00:03:40,000 --> 00:03:41,890 So here, it's a formula cell. 65 00:03:42,070 --> 00:03:42,940 I can't. 66 00:03:42,940 --> 00:03:44,530 The cell is protected. 67 00:03:44,530 --> 00:03:45,440 That's it. 68 00:03:45,460 --> 00:03:47,300 Number three is done. 69 00:03:47,320 --> 00:03:48,600 Challenge completed.