- There are two more things I'd like to do to finalize this workbook before we save it as a template. The first thing I want to do is add some instructions and there are a couple of different ways we can do that, I'll show you both of them. The second thing I'd like to attempt to is protecting some cells, actually unprotecting some cells and then protecting the sheet in the workbook. If this is something you're unfamiliar with I'll quickly show you how to do it but you might also want to take a deeper dive into worksheet and workbook protection. In the Excel essential training course that is appropriate for the version of Excel you're using.
One way people have always provided information to other users about workbooks is to simply add a sheet that's an instructional sheet. So I can click new sheet and either begin typing here I can drop a large text box on the screen and start entering information in it. I can add graphics, I can treat this as a canvas and provide whatever instruction I believe people need. We'll just call this instructions or we can call it using this workbook, using this worksheet, whatever you would like to call it using this workbook and I'll put that first.
When I save this I'll make sure that I'm on this sheet so that when someone opens up the template based on this workbook, that's always what they're going to see first. When I talk about adding a large text box, you could simply choose Insert, Text, Text Box and drop a nice large text box here to put whatever information in you wish. This is formattable, so if you want to add some text here.
Notice that my text is branded. If I want to change the color of the background of the entire text box, I could put a light fill in it that's one of our colors, one of my nice KinetEco colors like that light blue. You can do a lot of different things in text boxes, for example, one of the things that we can do is we can insert a picture into a text box. Then I can add whatever text I wish, I can also simply place this right out on the page. I could drop my icon here.
That kind of looks a little funky here, well yeah, it does but don't worry about it yet because I don't need to have these grid lines up here, remember that I have the ability to go to View and say I don't want to see any grid lines. Then I can simply type the text that I want and create a really nice looking cover sheet so that's one possibility. If you have a set of instructions about how this is used, where people get support if they need help with it, what are appropriate sources of information to fill in this sheet, whatever it is that you need everybody to know, it belongs some place in the workbook so a separate cover sheet is possibility one.
The second choice, and one that I use a great deal is to put text boxes right on the surface of the sheet itself, so if I wanted to provide some instruction about what should or shouldn't be filled in here, I would insert a text box perhaps right here, so that it's easily visible. Provide whatever information someone needs so fill in week starting date, employee names, use drop down list to fill in hours scheduled.
It's not bad, it's everything someone needs to know. Now, what I'd like to do is I'd like to make this really easy to work with. Right now, if I print this sheet it's gonna print everything and I could restrict the print range but there's even a sneakier way to do what I'd like to be able to do here, which is to change the properties of the text box itself. I'm going to go to Size and Properties, I'm gonna click the properties of my text box and I'm gonna say move and size with the cells, actually move if cells move but don't resize this text box, it's the right size and don't print it.
By setting the properties so it doesn't print, if we go and look at print view of this, notice there's no text box there. This nice none printing text box and you can have none printing shapes of almost any kind you wish. I'll normally go in and provide again a little bit of back fill and if I'm wanting to make sure that this looks incredibly spiffy then I'm going to just add a little bit of either a Soft Edge around it or I could add a drop Shadow so it looks it's a little note right off the page, like that right there, notice that.
Here's a note, and you can provide as many of this as you need and because they don't print, they're not a problem, nobody needs to get rid of them. They provide help exactly where somebody needs it. The third possibility, if you're providing information on one cell and one cell only, or the same information in many specific cells is you can choose the cell, right click and insert a comment. It will list your name and you don't need to have that there, that feature is for people reviewing sheets but if you're gonna just leave a comment you might wanna indicate list, full name of location, including location number.
Now I can resize this, I wanna make sure that when somebody points that cell, they can easily read the whole thing. You can move this, if I don't like it appearing there, I'd like it to appear here that's just fine. Now when someone hovers over the cell, they see list full name of location, including location number. Cover sheet, none printing notes, comments, three different ways that we can leave instructions in our worksheet that we're going to save as a template. Now the last thing that I might want to do is provide some protection.
If you're not familiar with protecting worksheets and workbooks then I'm going to provide a bit of information about it, but again you'll find more information in the essential training course for your version of Excel. Every single cell in an Excel workbook has a property called locked, and locked is turned on. It's actually waiting for you to protect the sheet and when you do, that lock goes into effect and no one can edit that cell. If we want people to be able to go in edit some cells, what we do is we unlock the cells we want them to be able to edit and then we protect the sheet.
If I want them to be able to edit here for example, and then all of these areas and remember that you can hold control to select non-contiguous ranges. I can just scroll and select every place they will need to type. You can also do this one at a time. I'm actually gonna do it in two passes. If you make a mistake like that, you have to start again, so it's not a bad idea to do a few at a time.
Let's do what we can easily see here and then this location and that location. Those are the areas I want someone to type in. I will not want them to type in I2 where it now says enter date above, I will not want them to type our scheduled or the hours at the right, those are all formulas. I can right click in any of these cells, choose Format Cells, choose Protection, and unlock them, click okay. I'll repeat that then and choose all of the other cells where ultimately my cell for other users will be typing and for each set I'm just gonna right click, choose format cells and unlock them.
One more set I'll try to be very smooth here. There we go, right click, Format Cells, unlock. Now, all the other cells on this sheet are locked. Go to Review, Protect Sheet, and you can provide a password to unprotect the sheet. If you don't, then a user who has some skill would be able to comment and unprotect it so you'll wanna provide a password and you'll wanna keep track of it. For example, whatever your password is and notice that it's allowing users to select cells that are locked and unlocked and the reason is that they might wanna copy and paste them somewhere.
You allow that when you allow them to select locked cells or I could simply say no, only unlocked cells, they're not allowed to format cells, to format columns, to do any work with the areas that they can't actually select. They're not allowed to delete columns or delete rows. They're not allowed to edit the objects, this is an object for example, a chart would be an object. If I provided a password I would then be prompted to type the same password again, to make sure that works but I'm not going to put a password on right now just so we can see how this works.
I'm gonna say okay. I'm allowed to select, all the formulas still work. I'm allowed to type a date here. I'm not allowed to adjust the width of this cell so I'll need to make sure that I clean that up. Let's unprotect the sheet. Give this a little bit more width on this one or maybe just change my formatting that might be a really good idea. This looks good but let's just make it smaller again, that's great.
That way this all get to stay about the same size, liking that. Okay, we'll go back and protect the sheet again, provide a password for it. If I don't want my users to be able even to select the cells that are locked, I'll just turn that off. In that case, when they go to click on cells that have formulas on them, we won't be bothering them. They can't get to them but they can get to the cells that are unlocked. Protecting a sheet, protects the contents of the sheet the cell. You might remember that I quickly hit a worksheet earlier, it's down here and I can unhide it again.
It's a sheet called list and it has just the list that drives the drop downs in our schedule. I actually don't want somebody to be able to do what I just did, I'm gonna hide this again. If I want to make sure that someone can't add sheets to the book, delete sheets from the book or hide or unhide sheets, the structure of the workbook itself then I need to protect the workbook. Again, provide a password. If I try to unhide now, not a choice.
That's looking good. So I have provided protection, I've provided basic instruction for my users for this workbook. I'm feeling this is finalized and is now ready to be saved.
- Creating themes for PowerPoint
- Modifying slide masters and slide layouts
- Adding placeholders and instructions to PowerPoint templates
- Creating and modifying Word styles
- Creating a custom header, footer, or Quick Part
- Saving building blocks in a Word template
- Creating Excel templates
- Adding instructions and protection in Excel sheets
- Sharing templates
- Branding in Outlook