Hiding errors where data doesn’t appear

Imagine a bunch of formulae copied down columns B, C, D etc. that are all driven by the data appearing in column A. An example might be a staff reference number in column A, with associated details (forename, surname, gender etc.) populated through formulae in the subsequent columns.

The trouble is, I don’t know how many rows will contain data—that is the decision of the operator of the spreadsheet. So I usually copy the formulae down beyond the point I think they will ever be used.

In the past, I’ve always added logic in the formulae in columns B and beyond to get rid of the horrible N/A# errors that appear when you try to look up a blank value, usually through one of the following

=if(ISERROR([INSERT LOGIC HERE]),””,[INSERT LOGIC HERE])
=if(A1=””,””,[INSERT LOGIC HERE])

The former checks whether the logic will return an error, in which case it will blank the target cell. The latter checks for the presence of data in column A.

Thinking about the problem today, I figured it probably makes more sense, both from a presentation and a memory perspective, to cater for this circumstance using conditional formatting as follows:

=ISERROR($A1)

The conditional format for the columns to the right of column A would simply contain ;;; as the custom number format, which hides the cells’ contents.

(In the course of writing this post, I remembered that the conditional formatting of number formats was only introduced in Excel 2007, so this tip is only applicable in the latest version, I’m afraid.)

Comments

Leave a Reply