Hiding calculation errors in Excel

Calculation errors in Excel can be useful things, as they highlight problem areas in a spreadsheet and help to keep processes running smoothly. The problem with calculation errors, of course, is that they can be a real eye-sore when blatantly visible in the printed copy of a report.

It is possible to hide any errors in a spreadsheet by simply using the IfError function to trap the error (only available in Excel 2007 onwards).  For example, imagine you’ve got a formula which divides A1 by A2.  If A2 is 0, you will get a Divide Zero Error, which looks like a big, horrible “#DIV/0!”.  To prevent this error from being displayed (for example, if zero was a reasonably normal state for A2), you can type ‘=IfError(A1/A2,0)’ into your formula cell. This results in the error being replaced by the second argument, which in this case is a zero.  Alternatively, we could use ‘=IfError(A1/A2,””)’, which means that an error will simply give a blank cell.

That’s great and all, but sometimes it can be really handy to see the errors – so what if you want the error messages present in the onscreen version of your spreadsheet, but NOT displayed on printed copies? Thankfully, there is a way to keep these errors displayed on your spreadsheet, while preventing their appearance in any printed version. Here’s how:

In Excel 2007 onwards:
1. Click on the Windows button, scroll down to Print and click on Print Preview
2. Click on Page Setup
3. Click on the ‘Sheet’ tab
4. In the box next to ‘Cell errors as:”, select the appropriate action. ‘<blank>’ will print error cells as empty cells, ‘—’ will print three dashes in error cells, and ‘#N/A’ will leave the error message visible!

In Excel 2003:
1. Click File and select Page Setup
2. Click on the ‘Sheet’ tab
3. Same as step 4 above.

Enjoy!