Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
A very common scenario when working with Excel is to share a workbook, that is to have more that one person having access to the data, either to make changes or update or additions. Protecting that workbook is then something to consider and there are some new options here in Excel 2013, we're going to explore them using this file named No Obstacle Sport - Customer Database3. As you can see down below there are more in than one tab. There is our main sheet, Sheet1, containing our customer data base information; and there's also a Power View, and if we switch over to that tab, you can see we're looking at a table with some tiles. Okay.
So, maybe this is an area where anyone can have access to manipulate the data and visualize it in different ways. We don't need to protect that. But if we go back to our sheet, maybe we do want to protect this sheet. Well, maybe we want to protect a part of this sheet, and that is an option now using a Range Protection, so let's talk about that. Looking at our data, we have First Name, Last Name and we have Phone, Street Address, City, Zipcode. There is some information here that should never change, like our customers' first and last names and their logins, everything else, like phone number and their address, might change.
So, we want to get certain users access to this range even when our sheet is protected. So, let's do that now. First, we'll go up to the Review tab on the ribbon and here's where we see our Protection Options like protecting a workbook. This is nothing new. Using the Protect Workbook option allows us to password protect our workbook. Anyone trying to open up the file called No Obstacle Sport - Customer Database3, would have to know the password, but then they'd have full access to the content. Here's Protect Sheet which allows us to password protect the sheet we're looking at.
That would leave our Power View available to users to make changes. However, here in Sheet1, they would need to know the password to get in. But maybe we want to make it so that we could protect our sheet and give users access to this range containing the phone number and address. In that case, you'll see this new feature Allow Users to Edit Ranges. Let's go there. We'll give it a click and now all we need to do is create our New Range. Click the New button and we'll give it a name. Let's call it Customer_Info.
Now, the range itself, we could have selected it ahead of time and it would appear here in the Refer to cells field, but we can go ahead and select it using this button off to the right, give it a click and temporarily now we have access to our spreadsheet. So, let's start with the Phone field here at cell C6. We'll click and drag across to the Zipcode and all the way down to bottom. Now, as we scroll quickly, you'll notice Excel slows down as it gets close to the end, which is well in to the 200s here. There we go.
206 is the last row. So, we'll let go there. That's the range, so we'll click that same button. Notice the range now appears in the New Range field. Click that same button to get back in, and we'll give this range a password. Let's type in any password you like. You won't see what you are typing and when you click OK, you'll be prompted to type it in again, to make sure you got it right and click OK. All right, so we now have our New Range. You can see it there. We can add as many ranges as we like.
And if we wanted to assign permissions that is to specify who can edit the range without a password. And if you have a number of contacts, for example, this is workbook that is available to people on the network, you could select names and just give them straight out permission without using the password. When we click Apply, it gets applied to our range and now we have to actually protect the sheet. So, let's go to Protect Sheet. We could also have done it from up here in the changes section, Protect Worksheet. When we click that, we get to choose now exactly what our users will be allowed to do in this worksheet.
Select locked cells, Select unlocked cells, is checked off by default, so is Protect worksheet and contents of locked cells, unless they know the Password. So, we'll type in a Password here for the sheet, click OK, and just like we saw with the range, we need to reenter it and click OK. All right, we've protected our sheet and we actually have a range. Ctrl+Home on your keyboard is going to take you back to the top. Let's say we wanted to make a change now to the First Name or Last Name here at the top.
So, we type in there, click, and type something like Rivers. As soon as I start to type, you can see the cell or chart that I'm trying to change is on a protected sheet. To make changes, I have to unprotect it and I'd have to know the password to do that. So, I go up to Unprotect Sheet. But what if I did want to make changes here to this range? I know I'm allowed to. Let's say someone's zipcode has changed. We click in there, and when we go to change the zipcode, you see it's a different message that pops up, a cell that I'm trying to change is password protected.
Well, I know the password for the range. I don't know it for the worksheet, so I can't unprotect it, but I can get access to this cell. So, I type is the password, click OK or press Enter, and now when I go to type in a new zipcode, I'm allowed to. So, that's how you can protect ranges within a sheet, protect an entire sheet, even an entire workbook and assign permissions all at the same, here in Excel 2013.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 74284 Viewers
80 Video lessons · 129608 Viewers
52 Video lessons · 63870 Viewers
59 Video lessons · 49659 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.