How To Check For Errors6:57 with Tyler Tallon
It’s impossible to prevent all errors, so let’s take a look at some ways that we can check for errors.
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
So far, we have discussed common errors and 0:00 ways to prevent errors using data validation. 0:02 But the data validation is not perfect and it's impossible to prevent all errors. 0:05 So let's take a look at some ways that we can check for errors. 0:10 One thing I often do is create a separate check formula. 0:14 Here we have our store sales again by state and product and a total for 0:17 these states. 0:21 What we can do now is add totals to the bottom for Product A and 0:22 Product B and then get a product total. 0:27 Now we can create a check, here below that. 0:59 And take the first total, minus the second total and it should be zero. 1:02 That's just a good extra step it takes sometime to make sure there isn't an error 1:16 with the formula. 1:21 I use these a lot when working with financial statements, 1:22 to make sure all the formulas are working properly and everything ties out. 1:24 For instance, on the balance sheet, I have a check formula to make sure assets 1:29 are equal to liabilities plus shareholders equity, to make sure everything balances. 1:33 There are also a few built in tools we can use to check for errors. 1:38 Go To Special is one of them. 1:41 On the home tab, if you go all the way over to the right, you will see Find and 1:44 Select. 1:49 If you click on that, 1:49 you will see several options that can assist you in checking for errors. 1:50 If you select Go To Special and 1:54 then check Formulas, this will highlight all the cells that have formula in it. 1:56 This is a good way to check and 2:02 see if there are any hard coded numbers in places where you were expecting a formula. 2:03 Also under Formulas and go to Special, you can check the Errors box. 2:09 And this will highlight any cells that have formula errors. 2:14 There are also some helpful features under the Formulas tab. 2:17 If you click on Show Formulas, 2:21 it will replace calculated results with the formula. 2:22 This is a good way to see your formulas and make sure they are correct and 2:26 in the right place. 2:30 To remove this, you can just click on it again. 2:32 Another one I use often is the trace presidents independence. 2:36 Any cell that contains a formula can be traced. 2:40 Click on the product day total and then trace precedence. 2:43 And here we can see each one of the cells with a dot 2:47 shows what makes up the total down here where the arrow is. 2:50 It's tracing the formula backwards. 2:54 To remove the arrow, just click on Remove Arrow in the same section. 2:56 Trace dependents traces forward to find where the results of the cell are used. 3:02 So let's click on product day for California and use trace dependents and 3:07 here we can see this value is included in the total on line 12 and 3:12 also the product A total on line 14. 3:17 This is really helpful if you're working with a big file that has several tabs, 3:21 as it helps you understand how the numbers are being calculated and 3:25 how everything is connected. 3:29 I use trace dependents a lot If I'm needing to delete data in a cell, 3:31 to make sure that deleting that value won't impact anything else in my workbook. 3:35 All right, let's remove the arrows. 3:39 Another tool under the formula tab is error tracking. 3:41 You can trace the error if you click here on the drop down. 3:45 If you ever get error codes like this one, the divided by zero or any other error 3:49 code, you can click on that cell and trace the error to where it's coming from. 3:54 And lastly, you can evaluate formula. 4:04 When you click on that, it opens a box that calculates a formula, step by step. 4:06 This can help solve order of operation errors. 4:11 All right, let's move over to the Find and Replace tab. 4:14 Another helpful tool to fix errors is find and replace. 4:18 When you do find errors or need to change some of your data for 4:21 whatever reason, find and replace is a good tool to use. 4:24 Let's take a look at the raw data first or sales by state and 4:28 while reviewing, we notice when we try to filter by month. 4:32 Though we see sales for July but non for June. 4:35 We know we should only have data for the first half of the year. 4:38 So if we can confirm that July data should in fact be labelled as June, 4:42 then we can use the find and replace tool to change that. 4:46 First let's highlight the Month column and then hit Ctrl+F on your keyboard. 4:50 And go to the Replace tab and for 4:55 find what you wanna put J-U-L. 4:59 Then we want to replace with J-U-N. 5:04 For this example, we want to hit Replace All. 5:09 And once we click on that, it will search for 5:12 any cells that have J-U-L as the month and it will replace them with J-U-N. 5:15 You can see a box pops up, letting us know it's done finding and replacing. 5:20 And it lets us know how many replacements it has made. 5:24 Let's click OK and then go check to make it worked properly, 5:27 by clicking on the filter to see if we now have data for June instead of 5:31 July Let's go ahead and filter for June and make sure it looks correct 5:35 Yep, looks good. 5:51 You can also use find and replace in formulas. 5:56 Let's say we multiplied each month sales by 12 to get an estimate for annual sales, 6:00 but we accidentally miss key and type 11 instead of 12. 6:04 We can quickly correct this by using find and replace, 6:09 to replace 11 with 12 in the formula. 6:13 Once again, highlight the column you want to search in and click Ctrl+F. 6:16 And then go back to the Replace tab, and for Find what, 6:22 we want to find 11 and Replace with 12. 6:28 Let's hit Replace all again. 6:33 You can see it says all then. 6:36 We made 605 replacements, hit OK. 6:37 Now, if we scroll through, we can see up in the formula bar, 6:42 up here that it says 12 instead of 11. 6:47 So looks like it worked. 6:51 Hopefully these error-checking tools will be as useful to you as they have 6:52 been to me. 6:56
You need to sign up for Treehouse in order to download course files.Sign up