Excel is one of the most popular, powerful, and useful programs in the Microsoft Office Suite. With a massive user base, Excel makes its presence felt in almost every industry. However, the tool is not without its share of hangups. From misplaced digits to accidental deletion, a single click is all it takes to ruin an entire whole spreadsheet. And once the damage is done, getting everything back on track is a herculean feat. Even the most skilled users must cope with such issues from time to time. To help you resolve the most common Excel nightmares quickly and efficiently, we’ve come up with a few pointers. (And for some more pointers, check out our previous story on Excel hotkeys.)
The dreaded #VALUE! message flashes on your screen when you enter text, characters, or spaces in a formula instead of a number and is perhaps the most common of the Excel nightmares you will face. Remember that formulas in Excel do not support anything other than numeric characters, not even formulas related to numbers. Thus, including something else in their stead will bring up an error message. Avoid this error in the first place by double-checking the formula for numbers only. If the error message refuses to go away, check for special characters being used, blank cells, or missing formulas, linking to Excel cells.
Prepare to view #NAME? error on your screen whenever Excel fails to understand the formula you’re trying to run. The message is shown also when Excel cannot compute one or multiple values in the formula itself. Correcting this error is easy. All you need to do is inspect the spelling or the formula entered. In case you find the spreadsheet still displaying an error despite everything being in perfect order, it could have something to do with one of the entries in the formula. In that case, just let Excel create the function on your behalf with the Formula Builder.
Broken Excel links
Human errors aside, Excel documents are prone to viruses or other digital threats. In that case, you might end up with a document with broken links. This is more common than you think, occurring especially frequently in workplaces where files are moved from a particular server to some different one. Another reason why you might end up with issues in your spreadsheet is if your company is moving all your files to a cloud-based application, such as SharePoint.
An Excel document with broken links is a cause for concern in any business and it can severely hamper the workflow or lead to the dissemination of erroneous information. For this reason, you should always have backup copies of the files stored on your server or in the cloud.
Seeing ##### in one of your spreadsheet cells can be intimidating, to say the least. Thankfully, it simply indicates that the column lacks the necessary width to display the inputted value. And it’s extremely easy to fix the problem. Just click the column header’s right border and extend the column width as desired. To make things easier, you can tap the right border of the header twice and it will automatically expand to accommodate the widest cell of that specific column.
Anytime you request Excel to divide a particular formula by an empty spreadsheet cell or zero, you receive the #DIV/0! on your screen. Now, if you did the math by hand or using a calculator, it would fail to compute the calculations. And, it fails to work in the Excel tool either. Resolving this issue should not pose a problem. All you have to do is alter the value of the cell to something other than 0 or choose some value if that cell was left blank earlier. Sometimes, you are just waiting to enter some value in a certain cell, and you press “0” as a sort of placeholder. It is then that you see the #DIV/0! message on your monitor. Instead of doing that, it’s easier if you input a customized display message.
Bear with us, because explaining #REF might be a little convoluted! Now, the #REF! message is usually displayed on the screen when the formula tries to reference an invalid cell or one that simply does not exist in the first place. In case you remove a particular row, column, or cell in your Excel spreadsheet, and then insert a formula that includes a deleted cell name, then you will see the #REF! error pop up in the cell containing that specific formula.
So, the next time you decide to paste over certain cells present in your spreadsheet, ensure that there are no formulas present which you will need later on. Otherwise, when a formula attempts to refer to the cells you have already removed from the page, it will come up with the #REF! message. Moreover, when you are removing cells from the spreadsheet, be sure to double-check the formula that is being referred to in those sets of cells.
In some cases, you might accidentally hit ‘Delete’ and remove a couple of cells from your spreadsheet. Don’t panic! Just press the Undo button located on the Quick Access toolbar to restore the document to the previous state.
Users will receive a #NULL! error message if they specify a particular intersection comprised of two sections that have no way to intersect, or if they use the wrong range operator. If you want some more context on the subject, take a look at the way Excel reference operators handle below:
- Intersection operator (space): The reference is returned to the intersection of dual ranges.
- Union operator (comma): Used to combine two individual references into one single reference.
- Range operator (semicolon): Is used for defining a reference to a whole set of cells/
Resolving this issue is fairly straightforward if you know what you’re doing. Start off by making sure that the proper syntax is being used in the formula. Always make use a colon for distinguishing between the initial cell from the final cell when referring to a continuous string of cells in a given formula. Also, whenever you refer to two cells that cannot intersect, it must be done with a comma.
Anytime you get the #N/A message, it is an indication of the fact that the numerical characters being referred to the formula are not available. Several reasons might attribute to this error. For example, it is possible that you might have deleted a row or number being used in the formula by accident or you are referring to a different sheet that is no longer present on your system, was not saved, or was deleted completely.
If you consider yourself an advanced Excel user, then it is imperative that a cell is located easily from a formula referenced in a VLOOKUP. Otherwise, it could result in a #N/A issue.
Excel nightmares don’t have to haunt you
Excel has its share of problems, but the utility and functionality of this tool more than make up for any shortcomings. All you need to do is be a bit careful and you won’t face any Excel nightmares. And, even if you do, do not stress out. Instead, use the solutions above to fix the problems fast.
Featured image: Shutterstock