One very important but often overlooked aspect of spreadsheet design is documentation. This is crucially important, not just for a more user friendly experience, but to help avoid errors. Documentation starts from the point where you give the workbook a name. Ideally, there should be some naming conventions established and they should be adhered to. Once you've created the workbook, there should be naming conventions for the worksheets themselves as well. This one is quite straightforward. But if you had, for example, Sales Q1, Sales Q2 and Sales Q3, by adopting a standard naming convention, not only will you work tabs be easier to find, you'll have more flexibility in your complex calculations if you've used a standard naming convention. And we'll see this later on in the course. In some cases, it might be suitable to create a separate Notes sheet in which you would put any assumptions for example. This becomes very important when you create more complex models. Any assumptions made in your calculations should be carefully documented. You might also want to keep a change log. When a lot of people are making critical changes to a workbook, you might want to keep a record of who changed what and when, so that errors could be more easily tracked. When you have a simpler workbook like this, having an extra Note sheet is just going to add extra and unnecessary overhead. So what we are looking for, ideally, is that our workbooks are self-documenting. In other words, our headings should be nice and clear. When you open the sheet, it should be obvious what the purpose of that worksheet is. Column headings should also be as sensible and meaningful as possible. Avoid unnecessarily complex abbreviations that will need further explanation. By using named ranges and tables, we also help document our calculations because it becomes clear what those calculations are trying to do. And here again, it is very important that we adopt standard naming conventions. So for example, coming up to our Lookup sheet, Alex has added a few additional Lookups here, but what he has ensured is that all of his tables begin with tbl_ and then a meaningful name for what content that table holds. In the same way, our named ranges need to follow standard conventions, and if we just come to our Formulas tab and click on the Name Manager, you'll see all the named ranges he is using for Lookups, he has prefixed with lkp_. Now the naming convention that you adopt can be entirely your own. The important thing is, that it's standard and you can see here the very first name range set up which is the Region, we didn't put the lkp. So, let's go and edit that quickly now. So, I'm just going to put lkp at the beginning here, and then OK, and Close. And that will have immediately updated all the calculations that are referring to that named range. So, it's very easy to add the standardization later if we have missed it out earlier on. Now, coming back to our Data sheet, probably not such an obvious form of documentation, but nonetheless a really good one is to add data validation. Data validation helps avoid incorrect values going into a cell much better than some instruction hidden somewhere on a different sheet. So in this first column, we want to ensure that the user has put in a valid date in a valid date format. We're going to do this using data validation. So I'm going to select the entire column using Ctrl+Shift+down arrow, come back to my Data tab and click Data Validation. At the moment, it's allowing any value. I want to restrict this to only allow a date, and even doing that has already ensured that I will reduce data entry errors. I want that date to be less than or equal to, and I can't have a sale in the future, so I'm just going to pop in the function TODAY. And what that is going to do is ensure that we don't get invalid dates being entered and we don't get dates that were accidentally put in with say, a year sometime in the future. But we also want to make sure that this is clear to our users even before they start typing it in. We don't want them to just get an error and then have to work it out. So we're going to come to our Input Message. I'm going to give them a little bit of help upfront. And the title will be, Please enter sold date. So any additional information you want to provide to users can go here. Our input message will be, date must be in the format DD/MM/YYYY. Now, we've also made it very clear what format we're looking for. Now, obviously this format will need to match your own regional settings, but these are appropriate for ours. And then one more thing, we'll click on the Error Alert tab. Whenever a user puts in something that fail it's data validation, they get that typical Excel message, which can be quite aggressive and not necessarily very helpful. And a lot of users may think that it is the spreadsheet that is broken and they don't realize that it's actually a user error. So, let's help them by giving them message that will identify what the actual problem is. So, we're going to say, Invalid date as our Title and then the Error message we will say, Please enter a date in the form DD/MM/YYYY, and then OK. You will see, immediately, we get this little prompt which can be really helpful. And if I want to go and type the date in the wrong format, so I'm going to do 02/24/16, which is not valid in Australia, when I click on that, it's actually giving me a nice helpful message so I can go and correct the problem and I haven't ended up with an invalid date in my spreadsheet. Where you need very specific values or a finite list of values to be added, again, data validation is an excellent choice. So we're going to do this with our CouncilArea. We're going to select all of those and come back to our Data Validation. Now, we've already popped the message in there just to make it a little quicker. But I'm going to come to Settings now and specify that there must be a List and I'm going to use my F3 key to return my available Lookups. And because I have used the prefix Lookup, all my groups are now grouped close together, which again makes my life much easier. And what I'm looking for is valid council area. So I'm going to select that and say OK. The user is now provided with a dropdown list which makes for quicker data entry and makes it clear what the valid options are. If they were to go and type in the name of a suburb by mistake and click Enter, they get a friendly error message, not only explaining that that's not a valid council area, but if they need to add another council area, it's not allowing it. They need to actually put it on the Lookup list. So, here we've identified, not just how we stop problems, but how we can help inform the process. So that was working with data validation. The last thing we're going to look at is using comments, and again, these are easy to use but highly underutilized. So this time, we'll come to our Calculations sheet, and we've made a few interesting choices on this calculation sheet which other users might not readily understand, and we might not understand them as well in six months time. So, we can document those choices simply by adding a comment to the relevant cells. So, I'm going to come to this first date and we're going to explain what we're actually trying to do with that calculation. To add a comment, you can right click, and if you come to the Review tab, it has a Comment section and you can just click New Comment. It will automatically prefix the comment with a user ID on this computer. Obviously you can change that if you want to get rid of that. No problem with deleting it. Now we just want to explain that this calculation, it returns the first day of the month, 12 months before the last sold date. So, we've now added a comment. So the future user can understand what we were actually trying to do with that calculation. And then when you're done, you can actually just click away. Let's come over to these array formulas that Alex used. Not everybody understands them. And so, we need to just document that an array formula has been used. So we're going to add a quick comment saying, Array formula in use, and that's probably enough for now. And then click away. Now having added comments, you can actually scroll through those comments. So, if I wanted to get to the next comment in the workbook, it will take me there, and then I click Next and it will take me to the next comment, and that way you can quickly scroll through your comments. You can also delete comments and of course you can edit your comments. Comments are another way of adding simple documentation to your workbooks to make them easier to understand and easier to maintain in the future. So to recap, think about using standard naming conventions for naming everything from your workbooks, through to your named ranges, charts, tables, everything. Make sure that you use sensible headings where possible and take advantage of Excel's tools, like the data validation tool and the comments, to add additional clarification and instruction.
