Cell content testing

There are several functions in Excel that literally serve to “find the cell in time and space”. They can be used to identify the row and column in which the cell is located, count the rows or columns, determine the type of value that the cell contains … By themselves, these functions are seldom used. In combination with other functions, in conditional formatting, and especially with data validation, they can be irreplaceable.

Let’s see where we are now! The ROW and COLUMN functions are used to determine the order and column in which the cell is located. Their syntax is:

ROW (<cell address>)

COLUMN (<cell address>)

For example, if we choose cell A2 and write a formula:

=ROW(A2)

result will be 2, because A2 lies in second row. If we write a formula:

=COLUMN(A2)

result will be 1, because A1 lies in first column.

If we want to count how many rows in a given range we use the ROWS function, and if we want to count the number of columns we will use the COLUMNS function. The syntax of these functions is:

ROWS (<range>)

COLUMNS (<range>)

For example, if we write a formula:

=ROWS(A1:B5)

we would get the result 5, because this range has 5 rows. If we applied the column counting function (COLUMNS) we would get result 2 because the given range contains two columns.

Several functions are used to test the data type in the cell. They all have the same syntax and are used for testing: whether the cell contains a number (ISNUMBER), whether it contains text (ISTEXT), not contains text (ISNONTEXT), whether it contains a logical value (ISLOGICAL), a reference (ISREF), whether even number (ISEVEN), odd number (ISODD),  a cell without value (ISBLANK), etc. The syntax of these functions is:

ISNUMBER (<argument>)

ISTEXT (<argument>)

ISNONTEXT (<argument>)

ISLOGICAL (<argument>)

ISREF (<argument>)

ISEVEN (<argument>)

ISODD (<argument>)

ISBLANK (<argument>)