1 00:00:01,569 --> 00:00:04,330 Let's talk about protecting your work. 2 00:00:04,330 --> 00:00:09,940 But first off here's the most important thing you need to keep in mind when you work with protection 3 00:00:09,940 --> 00:00:10,890 in Excel. 4 00:00:10,930 --> 00:00:15,120 Protection does not mean security when you use Excel. 5 00:00:15,220 --> 00:00:19,500 So if you give your workbook a password or you give your worksheet a password. 6 00:00:19,630 --> 00:00:24,990 This is going to keep away the majority of users that might unintentionally change things 7 00:00:24,990 --> 00:00:26,460 you don't want them to change. 8 00:00:26,680 --> 00:00:33,280 But if someone has intention and they really want to get access even though you have a password they can. 9 00:00:33,280 --> 00:00:39,350 Because there are tools on the Internet that they can download that can crack these passwords. 10 00:00:39,370 --> 00:00:42,720 So keep this in mind when you use protection. 11 00:00:42,730 --> 00:00:48,670 First off, let's start by protecting the workbook. If you want to give a workbook a password so that only people 12 00:00:48,670 --> 00:00:51,840 who know that password are going to be able to open it. 13 00:00:51,850 --> 00:01:02,080 You have to go to File > Save As > go to More options, down here under Tools, go to general options, and provide 14 00:01:02,080 --> 00:01:07,320 a password to open. If you want certain people to be able to modify this file 15 00:01:07,510 --> 00:01:10,890 you can provide a password to modify and then click on Ok. 16 00:01:10,900 --> 00:01:14,350 I'm just going to press cancel here and go back. 17 00:01:14,950 --> 00:01:18,600 So that's how you can password protect an entire workbook. 18 00:01:18,610 --> 00:01:26,040 The other protection options you have are all inside the Review tab here. In this category protect, 19 00:01:26,170 --> 00:01:32,530 we have the ability to protect the workbook. Here the protection of the workbook means that the structure 20 00:01:32,890 --> 00:01:34,420 of the workbook is protected. 21 00:01:34,420 --> 00:01:40,580 I'm just gonna give it a simple password, click on OK, re-enter the password, and click on OK. 22 00:01:40,780 --> 00:01:46,510 Protecting the structure of the workbook means that people can't insert a new sheet. 23 00:01:46,510 --> 00:01:48,480 So notice that this is grayed out. 24 00:01:48,490 --> 00:01:51,460 They also can't move any of the sheets. 25 00:01:51,460 --> 00:01:56,360 So basically the structure of this entire workbook is kept as is. 26 00:01:56,560 --> 00:01:57,630 They can't add sheets. 27 00:01:57,640 --> 00:02:03,770 They can't delete sheets and they can't move sheets. To take away the protection 28 00:02:03,790 --> 00:02:09,169 just click again on protect workbook, add in the password and click on Ok. 29 00:02:09,169 --> 00:02:16,010 Now I can add new sheets and I can move sheets. 30 00:02:16,060 --> 00:02:19,430 Another option you have is to protect your worksheet. 31 00:02:19,510 --> 00:02:24,020 So if we just click on protect, this time I'm just not going to give it a password. 32 00:02:24,040 --> 00:02:26,250 I'll just protect without a password. 33 00:02:26,410 --> 00:02:27,610 Click on OK. 34 00:02:27,820 --> 00:02:30,360 Everything on this sheet is protected. 35 00:02:30,370 --> 00:02:32,830 If I come to input anything here, 36 00:02:32,830 --> 00:02:38,770 I'm not allowed. It tells me "The cell or chart you're trying to change is on a protected sheet". To make 37 00:02:38,770 --> 00:02:39,320 a change 38 00:02:39,330 --> 00:02:40,570 I need to unprotect. 39 00:02:40,960 --> 00:02:42,700 So there's nothing I can do here. 40 00:02:42,730 --> 00:02:45,610 I can't even add a cell color to this. 41 00:02:45,610 --> 00:02:49,660 Everything is grayed out. To be able to make changes to this 42 00:02:49,690 --> 00:02:55,750 I have to unprotect the sheet and if I had a password I have to know the password to be able to unprotect 43 00:02:55,750 --> 00:02:57,140 the sheet. 44 00:02:57,140 --> 00:03:04,210 Now let's say you're creating a template and what you want to do is to only leave this area unprotected 45 00:03:04,270 --> 00:03:10,090 but leave everything else protected. You don't want anyone to come in and change marketing cost to 46 00:03:10,090 --> 00:03:10,990 some other header. 47 00:03:10,990 --> 00:03:16,270 You don't want them to change these labels and you definitely don't want them to overwrite any of these 48 00:03:16,270 --> 00:03:17,290 formulas. 49 00:03:17,290 --> 00:03:22,060 You just want to give them the ability to input here. 50 00:03:22,060 --> 00:03:23,800 Here's what you need to do. 51 00:03:24,070 --> 00:03:30,760 Highlight the cells you want unprotected, right mouse click, go to format cells or use the shortcut key 52 00:03:30,760 --> 00:03:35,020 we learned which was control+1. All the way to the end here 53 00:03:35,020 --> 00:03:36,880 there's a protection tab. 54 00:03:36,880 --> 00:03:41,360 The default for all the cells is that the cells are locked. 55 00:03:41,410 --> 00:03:45,790 So every single cell in this sheet would automatically be locked 56 00:03:45,850 --> 00:03:52,450 the moment you click on protect. But you can take this away. If I remove the check mark for this area 57 00:03:52,900 --> 00:03:59,230 and say that if after I protect these ones shouldn't be locked, they're going to remain open. 58 00:03:59,760 --> 00:04:06,250 So let's do it for these as well. You can of course highlight many cells by just making your selection 59 00:04:06,280 --> 00:04:10,510 and holding down control and then highlighting the different areas. 60 00:04:10,540 --> 00:04:17,320 So I'm just going to de-select, control+1, go to protection, take away the check mark, and click on. 61 00:04:17,350 --> 00:04:20,630 OK let's go and protect the sheet. 62 00:04:20,769 --> 00:04:24,820 This time I'll just give it a password, confirm password 63 00:04:24,890 --> 00:04:28,060 and now let's test. First of all let's test this. 64 00:04:28,060 --> 00:04:29,620 Can I type here? 65 00:04:29,620 --> 00:04:31,730 No, I can't delete this formula. 66 00:04:31,870 --> 00:04:34,680 Can I type something in September? 67 00:04:34,840 --> 00:04:35,740 I can. 68 00:04:35,740 --> 00:04:37,970 Can I overwrite these? 69 00:04:37,990 --> 00:04:38,980 Yes I can. 70 00:04:39,070 --> 00:04:42,150 Because this whole area is open. 71 00:04:42,520 --> 00:04:45,270 Can they change the header here? 72 00:04:45,280 --> 00:04:46,410 I can't. 73 00:04:46,500 --> 00:04:52,990 So don't forget to take away that check mark if you want to leave some cells unprotected. 74 00:04:53,050 --> 00:04:57,830 Now you also have the option to allow users to edit ranges. 75 00:04:57,880 --> 00:05:03,800 This gives you the ability to define different passwords for different ranges. 76 00:05:03,800 --> 00:05:07,240 So for example, let's say for salaries and wages 77 00:05:07,370 --> 00:05:12,680 we just want someone from HR to be able to overwrite these values. 78 00:05:12,710 --> 00:05:16,760 So we want to provide a separate password for this range. 79 00:05:16,760 --> 00:05:19,340 That's when we can use this option. 80 00:05:19,430 --> 00:05:22,230 First we have to define what the range is. 81 00:05:22,250 --> 00:05:23,860 So let's click on new. 82 00:05:23,990 --> 00:05:25,580 We can give this a name. 83 00:05:25,580 --> 00:05:28,480 I'm just going to call it HR. Which cells 84 00:05:28,490 --> 00:05:31,190 do we want included in this range? 85 00:05:31,220 --> 00:05:38,940 I want all these cells to have a special password and the password, let's just do HR and click on Ok. 86 00:05:38,940 --> 00:05:42,350 So we have to re-enter HR and 87 00:05:42,380 --> 00:05:42,970 OK. 88 00:05:43,040 --> 00:05:49,410 So that's the range that has its own password. I can go and protect the sheet from here 89 00:05:49,550 --> 00:05:52,550 but there is one change I need to make. 90 00:05:52,790 --> 00:05:54,800 I'm just going to click on OK. 91 00:05:55,100 --> 00:05:59,110 Remember originally I took away that checkmark. 92 00:05:59,330 --> 00:06:03,180 I need to put back that checkmark. Press control+1, 93 00:06:03,350 --> 00:06:09,710 go back to protection, and put back that checkmark. Because if you don't put back the check mark this 94 00:06:09,980 --> 00:06:14,480 is going to overrule the protection you set up here. Click on ok. 95 00:06:14,480 --> 00:06:18,590 Now let's go and protect the sheet. 96 00:06:18,590 --> 00:06:20,480 I'm going to give it a different password. 97 00:06:20,480 --> 00:06:23,210 I'm going to call it LG and LG. 98 00:06:24,140 --> 00:06:24,550 OK. 99 00:06:24,590 --> 00:06:27,940 Now remember this was completely open. 100 00:06:28,010 --> 00:06:35,030 So I should be able to input here without problems. I can't input here because the sheet is protected. 101 00:06:35,030 --> 00:06:36,170 Now what about here? 102 00:06:36,170 --> 00:06:40,020 So let's say I come and I want to change one of these salaries. 103 00:06:40,190 --> 00:06:45,780 The moment I start entering I need to know the password to be able to change. 104 00:06:45,830 --> 00:06:50,070 So that's when the allow edit ranges comes into effect. 105 00:06:50,240 --> 00:06:53,160 If I type in LG which was the wrong password right. 106 00:06:53,180 --> 00:06:56,860 That's the password for the sheet and I click on OK. 107 00:06:56,900 --> 00:06:57,740 It's not correct. 108 00:06:57,770 --> 00:07:06,770 So I have to be able to give it the right password which was HR and now I'm able to make changes here. 109 00:07:06,820 --> 00:07:07,060 Okay. 110 00:07:07,100 --> 00:07:11,110 So to take away the protection I can unprotect the sheets. 111 00:07:11,130 --> 00:07:18,620 I have to know the sheet password which was LG. If I want to take away the additional password I have 112 00:07:18,620 --> 00:07:22,370 to go back to allow edit ranges, click on the range 113 00:07:22,490 --> 00:07:25,930 I want to delete and then select delete and 114 00:07:25,940 --> 00:07:27,510 OK. 115 00:07:28,030 --> 00:07:35,840 Now one last tip is that when you protect a sheet by default we have two check marks here and the rest 116 00:07:35,840 --> 00:07:38,680 of these don't have checkmarks. Until now 117 00:07:38,690 --> 00:07:41,960 We just click on Ok and we accepted the default 118 00:07:42,080 --> 00:07:45,010 but you can make changes to this. 119 00:07:45,110 --> 00:07:52,610 The default doesn't allow the users to format any cells, they can input numbers. I can change this 120 00:07:52,610 --> 00:07:58,810 value but if I go to format options here I can't add a color to this. 121 00:07:58,820 --> 00:08:07,010 So if you wanted them to be able to do this you can allow that by adding a checkmark to that field. 122 00:08:07,010 --> 00:08:10,860 So let's go back to protect and here for format cells. 123 00:08:10,970 --> 00:08:15,170 I'm going to put a checkmark and click on OK, the sheet is protected. 124 00:08:15,290 --> 00:08:16,520 Let's go to home. 125 00:08:16,520 --> 00:08:19,250 I can add a color to this. 126 00:08:19,250 --> 00:08:25,510 So there are some features that you can still let the users control aside from inputting numbers. 127 00:08:25,550 --> 00:08:29,120 So just take a look around and see the different options you have. 128 00:08:29,870 --> 00:08:36,260 So these are the different ways you can protect your worksheets or specific ranges. 129 00:08:36,409 --> 00:08:41,840 Make sure you do the activity for this section because it's going to require you to test your knowledge 130 00:08:41,960 --> 00:08:42,890 on protection.