What Are the Most Common Problems With Excel?
Research by Ray Panko indicates that nearly 90% of spreadsheets on average contain errors in 1% or more of all formula cells. With an average cell error rate of nearly 4% this makes the probability of calculation errors very high.
All errors you encounter in Excel will begin with #, with the following the most common.
Error |
How to Fix |
#### |
This error message appears when a column is not wide enough. Simply increasing the column width will fix this error. |
#REF! |
This error appears when Excel is trying to reference a cell that has been deleted. This will happen when you change data and forget to update the formula. Fix the error by updating the formula, or if the cell was erroneously erased, use the ‘undo’ command to place the cell information back into the spreadsheet. |
#NAME? |
This error will appear when parts of your formula are incorrect. The fix for this is to check your formula for inconsistencies. If the formula is correct, check the cells to see if any have incorrect information in them. |
#VALUE! |
This is another common error that occurs when there is an error in a cell that the formula is trying to reference. For example, if you’re attempting to calculate a column, but if you have a word or letters in that cell instead, this error will appear. The fix is simply getting rid of the letters in the cell. |
#DIV/0! |
This error can appear when you are attempting to divide a set of cells and the formula is trying to divide by zero. This can happen if the formula references the wrong cell to divide by or is written wrong. The quick fix is to correct the formula or the cell that the formula is referencing. |
These aren’t the only errors that you’re likely to encounter in Excel, but they are probably the ones that you’ll see most often.
If you regularly use Excel, you’ll also have to keep an eye out for errors that don’t have a message. These are formula errors. Research
For example, you’ve created a spreadsheet with departmental expenses, with a formula at the bottom of the spreadsheet that calculates the totals automatically.
Yesterday, your departmental expenses totaled $1450. Today, your assistant entered $1,000 in expenses but forgot to update the formula. That means that your total is still displayed at $1,450, even though the actual total is $2,450.
Another common error in Excel is simple human error. Let’s go back to your assistant today. She was tasked to enter $1,000 worth of expenses, but because of a data entry error, she entered $10,000 worth of expenses for today, bringing your expense total to $11,450.
While that error will likely stand out, a transposed number or one even off by a few dollars can easily escape the notice of reviewers.