Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
How To Check For Errors
6:57 with Tyler TallonIt’s impossible to prevent all errors, so let’s take a look at some ways that we can check for errors.
Downloads
Please note that this is a new spreadsheet, different from the one you used in the previous stage:
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up