Welcome to week four, and welcome Nikki. Hi, Prashant. This week, something a little bit different from the last two weeks. We are going to be looking at formula auditing and worksheet protection. So, Nikki, formula auditing, can you prime our learners about this place? I'm pleased you asked. At the risk of an oversell, what we're going to be looking at is Excel's formula auditing tools, which is a collection of tools to help us find and correct errors in our workbooks. Now, in week one, we actually looked at data validation already. So, what's the role of formula auditing? Well, data validation helps us prevent data entry errors, whereas formula auditing is more about correcting errors in our calculations. This is really important because it's estimated that 88 percent of workbooks contain significant errors. Yeah. Yeah. This can have very misleading and even disastrous results. Large financial institution, not so long ago, lost billions over copy-paste error in a risk model. I suppose the problem is Excel is so powerful and so easy to use, just about anyone can use it. Yeah, absolutely. That's why it's so important we learn to use it right. But, Excel actually gives us error messages sometimes. Yeah. If your calculation is syntactically incorrect or you've tried to use a value that's invalid, you will get an error message. We're going to start by looking at some of the more common error messages and how we can avoid and fix those. But, the problem is, we can have a calculation that's producing the wrong results even though the calculation is not invalid, but because we've put it together wrong. The problem is Excel doesn't know what we intend, and those areas can be a bit more subtle and harder to fix. Sometimes, Excel tries to help us by putting a green triangle at the top of cells. Yes. You need to look out for those because very often, they are an indication that your formula is inconsistent or just doing something that doesn't look correct to Excel. So, yeah, we need to look out for those. Any other tools? Yeah, there are quite a few actually. So, there is a tool called the Error Checking utility and that will allow you to scroll through a larger workbook and locate all the errors. There's another little tool for finding circular references, which are nasty little errors, but we can easily fix them once we find them, and you can even use pivot tables for auditing your data. Yeah, because you can quickly get summary data or totals and just cross-check those against your own totals and make sure they match. Any other error checking suggestions for our learners? Well, I think the most important thing is that we check our calculations at time of creation. Sure. Yeah. Not just that it didn't get an error message, but that actually is producing the correct results. Also, try and automate as much as possible, so that way, you avoid the copy-paste type errors. Finally, once you've got your calculations all correct, it's a very good idea to protect them to avoid being accidentally changed or damaged. That's the last topic we're going to look at is how we can protect our workbooks and our worksheets. So, some great tips on error checking, formula auditing, and protecting worksheets. Thank you, Nikki. We've got some practice videos coming up for you. Make sure, as always, you download the Excel workbooks and work alongside us step-by-step. Practice makes permanent, so come back and practice this again. The practice challenge this week will take you even further and check out this week's toolbox, as well as this week's great initiative. Now, it's over to you.
