- Everyone makes mistakes.
That's why it's important
to include error checking in your spreadsheets.
But when something isn't working right,
how do you find the issue?
And more importantly, how do you fix it?
After this video,
you'll be able to expertly recognize common problems
and correct them.
Let's start with hidden rows.
They can cause a variety of weird results.
For example, this spreadsheet
is saying that two plus two is 136.
And since math is absolute,
hidden rows are likely the culprit.
Unfortunately, it can be hard to spot hidden rows.
A hidden row is indicated by the tiniest sliver
on the left bar.
However, it's much easier to look
for whether a row jumps numbers like this one.
If you're getting weird results, select all,
then right click on the row headings
found on the left side of your sheet.
From the menu, you can select Unhide,
making all hidden rows visible.
Also, be aware that if you copy-paste a range
that includes hidden rows,
the hidden part will get copied.
And if you delete a range that includes hidden rows,
the hidden part will get deleted.
So be careful about hiding elements
you need for your spreadsheet to work.
However, if you delete or copy a range of filtered cells,
only the visible cells will be included.
This can cause a different problem.
If you fill or copy a formula with a filter on
and then later remove that filter,
the formula will not be included in the cells
that you previously had hidden.
If you open a spreadsheet
and aren't able to scroll through it,
it's because the panes have been frozen in the wrong place.
To fix this, go to the View ribbon,
select the Freeze Panes button, and Unfreeze Panes.
Sometimes you'll try to select a column
as a range in a formula,
but when you do the selection changes into multiple columns.
Here, I just tried to click on column C,
but it changed it to column C through N.
That's because you have merged cells.
To fix this issue, find any merged cells in your range,
then go to the Home ribbon
and select the Merge & Center button to unmerge them.
Now I'm able to select just column C in my formula.
Another common problem is formula errors.
Luckily, Excel has a shortcut
to color code each section of a formula
to make it easier to spot any errors.
First, click on the cell with a formula
and hit F2 or Command+U on a Mac,
or click into the Formula bar.
In this formula,
I can see that the lookup range and return range
of the X lookup are different sizes.
Other common errors in formulas
are missing parameters or arguments,
missing commas, and parentheses in the wrong place.
Excel makes it easier to debug formulas with helper text
to show you what parameters you need to enter
at the spot you have the cursor,
and color coding the parentheses
keep you from getting lost in your formula.
What do you do when your error checking formulas
tell you there's a discrepancy
in how you're calculating a number?
You know something's wrong,
but how do you find the problem so you can fix it?
First check if the discrepancy
exactly matches a number on your spreadsheet.
Then, likely a formula either double counted that number
or failed to include it.
Or, as is the case in this example,
your formula added up both a cell that contains a sum
and the cells that some formula is totaling,
which is another way of double counting.
If your discrepancy is exactly double a number
that appears in your spreadsheet,
then you likely added when you should have subtracted,
or vice versa.
This is especially common in accounting,
where costs are sometimes listed as negative numbers
and sometimes positive numbers.
And if your discrepancy is a multiple of nine,
then you likely transposed numbers.
In this example, the revenue at the top is 2256,
while the revenue at the bottom is 2526,
which causes a $270,000 difference.
It may be hard to find the typo,
but at least you know what to look for.
And this is a good reason to avoid hard coded numbers
and use cell references instead.
Then you don't have to worry about typos in your numbers.
Errors and miscalculations come with the territory,
but you can rest at ease because you have the knowledge
to quickly and easily solve these problems.
By regularly tackling these issues,
you'll master producing high quality spreadsheets that work.