Now, we have nearly completed our workbook. The last thing we want to look at is creating as friendly a user interface as we can. There are several aspects to this like protecting cells to stop them from being accidentally deleted or changed. But we are just going to start with a number of tips and tricks that will add a professional look to the workbook. And a lot of this can be done on the View tab. Now to begin with, with this report, we actually don't need all these additional columns. So if you want to, we can actually hide them. So I'm going to select all of them, right click and Hide. On the View tab, we also have the option to hide our Gridlines, and that does make it much easier to read. And you even have the option to hide the Headings which, as we are going to be creating calculations in this sheet, makes a lot of sense. So that's looking already a lot more attractive. Now just before we start looking at protecting some of the cells, I'm going to right click and copy this graphic, because we are going to need it shortly. Okay. Now, what we don't want is for our users to worry about accidentally moving these charts around, or deleting these calculations. So we want to lock down the whole sheet, except for this slicer which our users are going to interact with. So the first step is to unlock anything that will require user interaction, and to do this with the slicer we're going to right click on the slicer and select Size and Properties. That will open up a panel on the right, and if necessary, expand the Properties section. You will notice it is locked. This is the default, but we go to untick that, so we unlock it, and now we can close the panel. The next thing we're going to do is come to our Review tab, and we're going to click Protect Sheet. By default, you're allowed to select locked cells and select unlocked cells. Now there's no value in allowing our users to select the locked cells in this case. So we are going to untick that, and then very important, we are going to tick Use Pivot and Pivot Chart. It is probably a better idea, in this particular case, to not select Edit Objects, as that will allow them to click on the other objects and potentially delete them. So just those two options, and then OK. Now you'll notice that I can't actually click on any of these other things. I can't move them, delete them, or break them. But if I come to my slicer, I can change it. So that was tidying up a sheet, and protecting data. The other thing that might be a good idea is to hide any worksheets that the users don't have to interact with at all, or at least on a regular basis. So the Calculation, the Pivot Council, and Pivot Method sheets, these are all sheets that are part of our background workings and we don't really want our users to stray onto them by mistake. So I'm going to select all three of those click and Hide. So we only have three critical sheets left. There's one more sheet, however, we're going to add now. And that is a nice friendly welcome screen. Click on the plus. We're going to call the sheet Home. And then on the Home sheet, we're going to paste the house GIF, only we're going to make it a lot bigger. We'd also like a big heading, and we've seen two different ways of doing this. But one really good option is to just use a shape. So I'm going to go for a rounded rectangle, and I'm going to put this big rectangle here, and then I will format it, so ties in with the rest of the spreadsheet. Now let's pop in some text here, which is the company's name, and then Sales Report 2017, and let's add some formatting. Now on this Home sheet, you could obviously put any useful information you choose. Some tips, quick notes, but importantly, you can use this for navigation. And creating navigation in Excel is actually fairly easy. Now I've seen situations where people have used macros to do this. Generally, if a macro can be avoided, it should be avoided. And for navigation in Excel, you can usually just use hyperlinks so that's what we're going to do. Once again, we'll grab some shapes, and I'll go for one similar to my heading here. And there are three places that our users will potentially need to go in this workbook. The first one is our Sales Data. Let's add a bit of formatting again. And then rather than having to re-draw that shape, I'm just going to press Control and dragged across to duplicate it, and once more. Then let's spread those a little bit, and this one is going to be my Lookup Lists, and this last one is going to be my Report. I'm then going to select all three shapes, come up to my Drawing Tools Format and use my alignment tools to just align them nicely to the left, and distribute them evenly vertically. But all they are at the moment are shapes. So the next step is to actually add the navigation. So clicking on my Sales Data, I'm going to come to my Insert. And if you look across the Insert tab, you will either see Hyperlink or Link, depending on which version you have. They do much the same thing. So I'm going to click a Link, and I could link to an external document or web site, or any number of things. But what I want to do, is link to a place in this document. So on the left, I'm going to click Place in This Document, and you'll notice you can link to another worksheet, or to a defined range, which is going to come in handy very shortly. But right now we want to link to the Data sheet, and cell A1 is absolutely fine for now and we're going to say okay. Now when I click away from that button and come back, you'll see there's a little white hand. And when I click on it, it takes me directly to my Sales sheet. So that's how you can easily link your sheets. Now having come to the Sales sheet, I will want a button to get back to my Home sheet. So I'm going to press Control+V to get my house, make it a little bit smaller here, and if you think the blue is not going to be very clear, you can of course come to your Picture Format tools and recalibrate. Something black might be easier to see. Okay. So there's my house. And then when I right click on that, you can choose to link this way as well. I'm going to choose to link back to the Home sheet, and this little button can then be copied and pasted onto the other sheets, so you don't have to redo it each time. Now I want to use a lands on the Sales Data sheet, getting to the end of the data for someone who's not familiar with Excel can be a little bit tricky. So what we'll do here, is create a link that will actually take the user down to the end of the data set. But then when I get to the bottom of the sheet, I still want to be able to see my headings. So the first thing we're going to do, is we're going to use Freeze Panes. Now if you'll remember from the earlier courses, the trick with freeze panes is to click into the cell to the right and below where you want to freeze. So by clicking into this cell, it means I won't be freezing any of the columns, but I will be freezing the three rows above. So I've clicked into that cell, I'm going to come back up to my View tab, and I'm going to select Freeze Panes. Now let's go down to the bottom of the table which is where I actually want my link to go. At the moment, the last cell that I'm clicked in, is 12237. The problem is this is going to change once we add new entries to our data. So while I could link directly to cell 12237, I will need a more dynamic way of doing it. And for that, I'm going to use a named range. So we're going to start with a little bit of a cheat. I'm going to select this cell, and I'm going to call that cell nav_end, so we're going to use a naming convention here, and then what I'm going to do is add a little arrow in. So I'm going to use another shape now, and I'm going to use my down arrow, make it black to match my home button, and then I'm going to add a link to that. So once again, Insert Link and this time, I'm actually going to link a defined name which is my nav_end, and at the moment that's working fine. But of course the minute we add another row, that's not actually going to go to the right cell. So what we're going to do now, is make a change to our named range. Rather than linking to a specific cell, we're going to create a dynamic named range. And all that means is that the named range will be generated by a formula, rather than being typed in directly. Now let's just see how this works. We're going to type in the calculation in our worksheet first, just to make life a little easier. So I'm going to click into a cell, just not directly underneath but a couple of cells underneath. And the calculation I'm going to need to use is an INDEX function. So I'm going to type =INDEX, because that's going to allow me to return a particular cell reference, the array I'm using is the whole of column A. And while this is something that you generally avoid doing, in this particular case, it's probably the easiest way. Now the next step is to work out which row number is the last row used in the workbook. And because I've used a table, this actually becomes really easy. So what I can do, is use my ROWS function which will return the number of rows in a given array and I can then use my table. And because I've called all of my tables tbl, I can just type tbl and select table Sales, and then close my brackets. What that's going to do, is return the number of rows in my table Sales, which isn't quite what I want because I need to account for the three rows of headings at the top. Plus, I want to go one below my actual table. So I'm going to need to add four onto that, and that will return the first available cell after the table in column A. And if I press Enter here, at the moment, it's just going to say zero because there's nothing in that cell. But let's see how it works when we apply it to the named range. So I'm going to select that entire formula and actually delete it out of there. I'm now going to come back to our Name Manager. So Formulas > Name Manager, and we're going to edit nav_end. Double click on it to edit it and where we've got =Data, we're going to replace that with our dynamic range and we're going to say OK and Close. Now is the time to test it. So I'm going to click on my down arrow, and as you can see, now we have a named range that will take us down to the next available cell. Let's just add some data at the end here to check that it's still going to work. So we're going to put in a record for the first of the tenth, and you'll see our table auto-extends. And if we go and select a Suburb, all of our calculations are nicely coming down. All of our data validation has extended as well. So we have a really easy to use dynamic workbook. If I now come back up to the top of my workbook, so I'm just going to press Control+Home, and then click my down arrow, it is now going to go to the new next available cell. So that is how you can reasonably easily, add navigation to your workbooks without needing to use any macros at all. So just to recap then, what we're looking for in our user interface, is something clear simple and easy to navigate.
