Excel Beginners

Data Types and Formatting: Step 2 To Mastering Spreadsheets

The is-test method

Determining Data Types: Method 2

This method consists of using a set of four functions to isolate all the relevant information required to treat a cell, and will identify the data type of the cell will relative ease. Where the first method required little time and resulted in a close idea of what the cell was meant to be formatted as (while determining it’s data type), this method will guarantee the data type of a cell but not give any clues as to its ideal formatting. It does so using 5 formulas which exist within Excel.

The first step is to isolate which cell you want to analyse. Once you have this information locate an empty section of the worksheet which contains 5 empty cells which you can then use to insert five formulas.

In this example there are seven fields which we are analysing, they are noted by the grey background. We organised five cells beneath each one to begin.

We follow that up by inserting the five necessary formulas into the cells we located. While the order is not important, it is suggested to follow the order described below

=ISFORMULA()

=ISTEXT()

=ISNUMBER()

=ISLOGICAL()

=ISERROR()

To help identify which formula is which, we have also placed them next to their results in the examples.

To finalise the process, we will look through the outputs of these formulas to understand not only the data type of the output, but also if small formatting choices were made to the output. Using these functions will yield a maximum of two TRUE values, and that is only if the cell contains a formula. If the cell is not a formula, only one of the other four functions will return a value of TRUE, thereby confirming which of the data types the output is.

The majority of the examples are in fact formulas which are providing the outputs we are seeing. The second example by contrast is not a function, displays the number ‘9’, but the system is telling us that the result is in fact of the text data type. This information is also highlighted by the face that there is a green triangle in the top corner of the cell.

You may be asking why the ‘#N/A’ error does not have the green arrow like the ‘#DIV/0’ error in example three does. This is because the ‘#N/A’ error is a special error type which can be placed into a cell manually in order to signify that values are missing or have yet to be introduced.