We often get errors in our Excel spreadsheets – some intentional and others not. Once you have fixed all the unintentional errors, you may feel that you don’t want to display the valid ones.
There are two ways of hiding these errors – an easy way and the correct way 😀
The Correct Method
There is a formula called IFERROR(your formula or calculation, what you want the error code replaced with).
For example. You need to divide one value by another – but you do not have the 2nd value yet. You will receive a #Div/0 error when you enter the formula. If you put the formula in the IFERROR() function, you can decide what will replace that error. IFERROR(A2/B2,”-“) Now all your #Div/0 errors will show as dashes.
The Quick Method
In the newer versions of Excel, highlight the area that contains the errors.
- Go to the Home tab, Styles group and select Conditional Formatting.
- Select Manage Rules.
- Click New Rule.
- Select Format only Cells that contain….
- And from the dropdown select Errors.
- Click the Format button and make the font white.
- Click Ok
All errors will now be in white and will not be visible.
Which method should you use?
That all depends on what you need the spreadsheet for. Obviously the formula method is the best as it shows others how clever you are. The problem with the Conditional formatting method appears when you need to use the spreadsheet on a coloured background. The white writing will then be visible.
Now go and show the rest of your company how clever you really are.