- There are two more things I'd like to doto finalize this workbook before we save it as a template.The first thing I want to do is add some instructionsand 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 tois protecting some cells,actually unprotecting some cellsand then protecting the sheet in the workbook.If this is something you're unfamiliar withI'll quickly show you how to do itbut you might also want to take a deeper diveinto worksheet and workbook protection.In the Excel essential training coursethat is appropriate for the version of Excel you're using.

One way people have always provided informationto other users about workbooksis to simply add a sheet that's an instructional sheet.So I can click new sheet andeither begin typing here I can dropa large text box on the screenand start entering information in it.I can add graphics, I can treat this as a canvasand provide whatever instruction I believe people need.We'll just call this instructionsor we can call it using this workbook,using this worksheet, whatever you would like to call itusing this workbook and I'll put that first.

When I save this I'll make sure that I'm on this sheetso that when someone opens up the templatebased 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 Boxand drop a nice large text box here to put whateverinformation 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 backgroundof the entire text box, I could put a light fill in itthat'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 dois 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 yetbecause I don't need to have these grid lines up here,remember that I have the ability to go to Viewand say I don't want to see any grid lines.Then I can simply type the text that I wantand create a really nice looking cover sheetso 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 informationto fill in this sheet,whatever it is that you need everybody to know,it belongs some place in the workbookso a separate cover sheet is possibility one.

The second choice, and one that I use a great dealis to put text boxes right on the surfaceof the sheet itself, so if I wanted to providesome instruction about what should or shouldn'tbe filled in here, I would insert a text boxperhaps right here, so that it's easily visible.Provide whatever information someone needsso fill in week starting date, employee names,use drop down list to fill inhours scheduled.

It's not bad, it's everything someone needs to know.Now, what I'd like to dois I'd like to make this really easy to work with.Right now, if I print this sheetit's gonna print everythingand I could restrict the print rangebut there's even a sneakier way to dowhat 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 boxand I'm gonna say move and size with the cells,actually move if cells movebut 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 boxand you can have none printing shapesof almost any kind you wish.I'll normally go in and provide againa little bit of back filland if I'm wanting to make surethat this looks incredibly spiffythen I'm going to just add a little bit of eithera Soft Edge around it or I could add a drop Shadowso it looks it's a little note right off the page,like that right there, notice that.

Here's a note, and you can provideas 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 informationon one cell and one cell only,or the same information in many specific cellsis you can choose the cell, right clickand insert a comment.It will list your name and you don't needto have that there, that feature is for peoplereviewing sheets but if you're gonna just leave a commentyou might wanna indicate list,full name of location,including location number.

Now I can resize this, I wanna make surethat when somebody points that cell,they can easily read the whole thing.You can move this, if I don't like itappearing there, I'd like it to appear herethat'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 instructionsin our worksheet that we're going to save as a template.Now the last thing that I might want to do is providesome protection.

If you're not familiar with protectingworksheets and workbooksthen I'm going to provide a bit of information about it,but again you'll find more informationin the essential training course for your version of Excel.Every single cell in an Excel workbookhas a property called locked,and locked is turned on.It's actually waiting for you to protect the sheetand when you do, that lock goes into effectand 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 themto be able to editand then we protect the sheet.

If I want them to be able to edit here for example,and then all of these areasand remember that you can hold controlto select non-contiguous ranges.I can just scroll and select every placethey 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 ideato do a few at a time.

Let's do what we can easily see hereand then this location and that location.Those are the areas I want someone to type in.I will not want them to type in I2where it now says enter date above,I will not want them to type our scheduledor 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 cellswhere ultimately my cell for other users will be typingand 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 skillwould be able to comment and unprotect itso you'll wanna provide a passwordand you'll wanna keep track of it.For example, whatever your password isand notice that it's allowing users to select cellsthat are locked and unlockedand the reason is that they might wannacopy and paste them somewhere.

You allow that when you allow them to select locked cellsor I could simply say no, only unlocked cells,they're not allowed to format cells,to format columns, to do any work with the areasthat they can't actually select.They're not allowed to delete columnsor 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 promptedto type the same password again,to make sure that worksbut I'm not going to put a password on right nowjust 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 cellso 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 oneor maybe just change my formattingthat 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 selectthe cells that are locked, I'll just turn that off.In that case, when they go to click on cellsthat have formulas on them, we won't be bothering them.They can't get to them but they can get to the cellsthat are unlocked.Protecting a sheet, protects the contentsof 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 listthat drives the drop downs in our schedule.I actually don't want somebody to be able to dowhat I just did,I'm gonna hide this again.If I want to make sure that someone can't addsheets to the book, delete sheets from the bookor hide or unhide sheets,the structure of the workbook itselfthen 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 usersfor this workbook.I'm feeling this is finalizedand is now ready to be saved.

Resume Transcript Auto-Scroll

Author

Released

12/10/2014

Office 2013 themes make branding easy, and templates help Office users to implement your themes. This course shows how to create effective themes that can be distributed and used in every Office app. Start by creating a theme in PowerPoint: choosing your color palette, fonts, and effects. Then see how to apply the theme to a PowerPoint template and lock down the slide layout. Author Gini Courter also shows how to access your theme from Word and Excel templates, and distribute the templates to others. The course ends in Outlook, where Gini shows how to set up stationery and signature options.