Excel, is one of those subjects that are hard to learn without real-life examples with which to work them out. Which is why becoming an excel junkie is nearly impossible unless you are an analyst and must spend 8 hours of your 10 hour work day in excel. But there are many benefits outside of that space that warrants being familiar with and comfortable with excel. (Read my blog here for a few of those reasons.)
And it’s not as hard as one would think. Breaking excel down into bite-size pieces and then having the confidence and courage to try out some of these lessons can help get you going.
GRR! WHAT THE HECK ARE ALL THESE ERRORS!
As I was learning excel, one of the biggest hurdles I had in continuing my learning was running into an excel formula error and Microsoft office would offer no help what-so-ever as to figuring out what that error means and what to do to resolve them! I suppose that is great news for the many Microsoft certified professionals that make a living from helping us all bridge the gap between real-world application and using a Microsoft product. But when your coming up on a deadline and need to be able to use excel to get you there quicker, it’s very little help.
Below are a few of the most common errors and what they mean.
Trying to divide numbers and keep getting this error? #DIV/0!
The formula you used is trying to divide by 0 (zero), which if you remember from your elementary math courses, is something not allowed in the human equation. This formula is simply telling you that your math stinks, so try again.
You can’t divide by zero or by a cell that is empty.
Not applicable? Whaa? #N/A
Excel can be used to lookup values or reference values in other cells. When you get this error it simply means you lost your direction and are referencing data that isn’t available. Check your formula. Many times when I get this formula I have to delete the formula and start over. Don’t be a man, ask for directions and then type very slowly.
What, My name is: #NAME, not Slim Shady
If you get this error, you are likely using a name that Excel doesn’t register or recognize. This can happen if you delete a name that is used in the formula. Same happens when you delete a cell in the formula, except you will get the error #REF! Say you are multiplying A4 by a6 and then you delete A6, this formula will magically appear. This can also occur when you have unmatched quotes with text. Say that you were using “text” in the formula but instead of using double quotes on both sides of the phrase, you used single quotes on the left and double quotes on the right. It won’t fly, not even for a white guy.
Your formulas coming up #NUM ?
Your math stinks again! When you see this error it means that there is a problem with the function argument: say for example the SQRT function is attempting to calculate the square root of a negative number or the calculated value is too large or too small for excel to support. Again, when I get this, honestly I scrap the whole formula and try try try again.
Seeing #### is not a secret excel formula to display the next super uber cool #saying that will take the social media world by storm, this error appears when the column is simply too small to display the result, just take your mouse up to the column edge and double click or drag the column to a larger width, problemo solved. This will also happen if the result is a negative date or time.
Remeber that getting an error in your formulas don’t give you permission to throw in the excel learning towel. I’ve seen analysts, CFOs and nerds alike return their fair share of formulas. Often times when they are buzzing around a spreadsheet trying to show off. But none the less, remember that getting an error is commonplace, often time we just reenter the formula and try again. Many times we simple referenced a wrong cell or did some wonky math. Do remember though that a single excel error can ripple through your entire spreadsheet, that’s why I try to address an error in my sheets as they occur. If you aren’t this fortunate and uncover errors at the end of a sheet or workbook that took you too many hours to count to complete, check the first formula that all the other formulas you may have in the error sequence works from and then go from there.
Also the tools in the Formulas -> Formula Auditing group on your excel menus tabs can help you trace the error source.
Until next time, Happy Error Checking!