Subtitles of the Movie

In the proceeding movies, we discussed the advantage of working with data validation to limit what can be entered into a cell. Sometimes we just want to keep people out of the spreadsheet or out of a particular cell. In this movie I'd like to talk about protecting your data. There are two functions that I would like to talk about at this particular time. The first is setting a password. The second is protecting individual cells on a worksheet. Let's start with setting a password. I'd like you to consider using Save As rather than Save with saving all your documents in Excel. By selecting Save As, you will notice that there is a Tools Button on the menu. Now, in prior versions you will notice that the Tools Menu will be in the upper, right-hand corner. It's exactly the same menu, just a different location. Select Tools and go to General Options. In General Options, here you will notice that I can set a password to open this particular file. I can have yet another password to modify it. This is where I can really set up a document so that it's Read-only. This is a great protection. You will eliminate someone from accidentally typing over formulas and other information within the spreadsheet. Also, if I do choose to save changes, it will force me to put a new name to the spreadsheet. That means your original document is protected and I can still make changes and save them but to a new document. Notice on the top also that it'll allow me to create a backup. Now, this can be a very good idea for some important document, but be cautious. If you set it for every, single document, you could have a storage problem on your hands. I'm going to simply say OK and you'll notice I'm back at the same screen. This is identical to Save in that I just name my file, the location and the type of file and simply set Save. In this instance I'm just going to cancel out. Utilizing Save As rather than Save is just a little extra bit of protection. Now what about the cells? I have a copy of an invoice on the screen and very frequently we'll have a little disclaimer information on the bottom that we do not want anyone to change. Here's where protection comes in. First, keep in mind that when you're protecting a worksheet, by default Excel will lock every, single cell out and not let anyone make any changes. Perhaps I'd like you to be able to put the client's name in. There's some cells I want protected and some cells not. We can do this with ease in Excel. The first thing I must do is format the cell to be unlocked. To format a cell I need to go to the Format Menu. Now, in the older versions it was a menu on its own. In 2007 I'm going to the Ribbon for Number and to the pull-down next to it. Here I'll find Format Cells. You'll notice in all versions we have a Protection Tab. By default, all cells are marked to Locked. When we protect the sheet, they will be locked. So I'm unlocking the cells I do not want protected. I'll simply say OK. You'll now be able to type under name. To set the protection itself, I'm going to review in 2007. In prior versions, I'll stick with the Tools Menu. Here's where I'll find Protect Sheet. In protecting the sheet, you'll notice it immediately asks me if I would like a password. It will protect the worksheet and the contents. I'm going to simply go ahead and say OK. Now here's what happens. When I go to name and I type in a new name, when I tab it takes it with no problem. When I go down to my disclaimer and I hit the Delete Key though, you'll notice that I immediately get an error message. I have the best of both worlds now. I can limit where you can type on the spreadsheet. This is a wonderful way to protect your formulas. Now notice, when I selected the protection of the sheet, the button has changed to Unprotect. It's telling me if I do want to type in, that I must unprotect it first. Here's where it's a really good idea to set that password so that someone doesn't accidentally select Unprotect and open up the entire spreadsheet. Parts of Automation is to protect ourselves from doing things a little too fast and causing extra work. Setting up protections is well worth the effort when it comes to Automation.