This course covers mostly
error handling - dealing with all of the nasty errors that Excel
throws at you like #NAME? or #REF!
We also go into logical errors... things that are wrong with your
spreadsheet but not necessarily things that generate an error message.
These are even harder to track down.
I'll teach you about errors with rounding numbers, and how to
force Excel to round values off with the ROUND function. I'll also show
you some instances where Excel is just dead WRONG with it's
calculations.
You will learn about all of the different logical functions, like
TRUE, FALSE, NOT, AND, and OR, and how they work with your IF function
to create much more powerful statements.
We will then take a more detailed look at the Auditing Toolbar.
No, this has nothing to do with the IRS. You'll learn how you can track
down your errors by tracing the precedents and dependents of each cell
(which other cells are responsible for it's data - and which depend on
it for their data).
You will learn about the powerful Evaluate Formula toolbar that
allows you to step through a complex calculation and see Excel evaluate
each part. You'll also see how to Circle Invalid Data, and use the Watch
Window.
Next, I'll show you how to hide errors on your printouts, and how
to turn Automatic Error Checking on and off. You'll also learn about
Circular References: what they are, why you don't usually want them,
how to fix them, and when you might actually WANT them. Essentially, you
can use a programmer's trick to use Circular References as a DO LOOP.
Finally, I've got a few Tips & Tricks for you... how to transpose
your data, how to use the Fill command (like Autofill, but with a
few more options), and so on.
This is a good class. It might not seem as exciting as charting or pivot
tables, but there is a lot of good, solid information in this class that
will make you a better Excel spreadsheet designer. Lots of fundamentals.
MICROSOFT EXCEL 231
78 minutes
LESSON 1. ERROR TYPES
Types of Errors
Syntax Errors
Logical Errors
Reference Errors
Circular References
Mismatched Parentheses
LESSON 2. SPECIFIC ERRORS
#DIV/0!
Handle DIV/0 error with IF function
ISERROR() function
#NAME?
#REF!
#VALUE!
#NUM!
SQRT() Function for square root
Exponents too large 10^308
#NULL!
#N/A
=NA() Function
LESSON 3. ERRORS WITHOUT MESSAGES
Logical Errors
Relative v. Absolute References
Rounding errors
Actual v. displayed values
ROUND() function
Force "Precision as displayed"
Floating Point Errors
Numbers close to zero not rounding right
LESSON 4. LOGICAL FUNCTIONS
TRUE
FALSE
NOT
AND
OR
=IF(AND(X,Y),TRUE,FALSE)
LESSON 5. AUDITING
Tracking Errors
Auditing Toolbar
Trace Dependents
Trace Precedents
Dependents on another sheet
Goto jumping to those dependents
Trace Errors
Blue and Red Arrows
Evaluate Formula
Evaluate, Step In, Step Out
Circle Invalid Data
Watch Window
LESSON 6. HIDING ERRORS
Automatic Error Checking
Hiding Cell Errors from Printouts
LESSON 7. CIRCULAR REFERENCES
Circular Iterations
When you WANT a Circular Reference
Simulating a DO LOOP
LESSON 8. TIPS & TRICKS
Transpose Data
Edit > Fill
Series Trend
Increasing X Weekdays
AutoFill with RIGHT Mouse Button
|