Welcome to week one and welcome Nicky. >> Hi Prashan. I'm really excited about this course because you are going to be covering some amazing topics. >> And this week we're going to focus on design and documentation. Now Nicky, we're used to focusing on the functionalities and the technical stuff of Excel. So why are we focusing on design and documentation? >> Well it is really important. And now we have some power users we're going to be working with. We need to make sure that we don't create badly designed spreadsheets that cause damage. And, we've heard the horror stories, the high profile ones, but even in small organizations you want your workbooks to be accurate, flexible. You need them to be responsive, easy to maintain, and of course, very importantly, user friendly. So we're going to look at how we can actually create those workbooks. >> So how do we practically make sure that our workbooks fulfill all these criteria? >> Well, I think the first thing we need to do is stop and decide, first of all, if Excel is in fact the correct tool to be using. If we're sure it is, then we need to structure our workbooks quite carefully. And the general design principle is you want to get a clear separation between your inputs, that's the data coming in, your calculations and the presentation. So what the end user actually sees, how they get the information they need. And if you do this, you form a nice solid foundation on which to achieve all those goals. >> So, once you have got the solid foundation, how can we make sure that our workbooks are accurate, flexible and responsive? Yep, good question, and we want to get stuck into the nitty-gritty of it which we will in the videos. And I think we're going to find it comes down to the calculations because at the end of the day, that is the heart of our spreadsheet. We're going to look at, first of all, how we can structure our calculations so they're less likely to break. We're going to use tables and named ranges to achieve those goals. But it comes down even to the choice of calculation. We want to get a best fit. We want the calculation that's simplest and best to do the job. But even something simple as making a choice between a VLOOKUP and an INDEX MATCH, we need our calculations to be auditable, so we want to be able to see an error really quickly. If you choose a VLOOKUP, and you do a Trace Precedents for example, it will go back to the entire range. But if you use an INDEX MATCH, it will point directly to the columns you're actually working with. So even a small choice like that can make your functions easier to audit, easier to find the errors. >> And what you just mentioned, in my own experience, it's critical. Because you're sharing spreadsheets across teams, across your organization. So you really need to take all of those things into account. You also mentioned about spreadsheets being easy to maintain and user friendly. How will we be tackling that, please? >> Well, I think the key thing there is documentation. And, it's not about just having a separate spreadsheet where we put some notes. That is useful. But really what we want to do is make sure that our spreadsheets are self-documenting. And that can be as simple as clear headers. But, it's also about using Excel's tools efficiently. So, take advantage of data validation. Take advantage of comments, and we're going to look at how to use these Excel tools. But then there's another layer which is probably even more important and that is the risk management and that starts from when you name your workbook and name your sheets and name your named ranges. You want to set up nice standard naming conventions and you want to have checks and balances to make sure those are followed. And by following these sort of sensible practice, not only do you end up with something that's user friendly, but something that is lower risk and less likely to error. >> And anything else we're going to be focusing on this week? >> Yeah, there are a few other topics we're going to look at. So still tied into the flexibility and the user friendliness, we're going to look at working with themes and styles and templates. And then, we're also going to look at how we can set up really nice user interface, using some of the tools like hyperlinks. And we'll get into a little bit of the graphical element there as well. >> Sounds amazing! Thank you so much Nicky. So this week, we're going to be equipping and empowering you to make sure that your spreadsheets are accurate, flexible, responsive, easy to maintain, and user friendly. We've got some great videos and tools coming up, so now it's over to you. >> [SOUND]
