Excel Beginners

Data Types and Formatting: Step 2 To Mastering Spreadsheets

Overview + Formula & Format Bar

Determining Data Types: Overview

Now having covered the 4 primary data types, the next step is to find ways to determine which of them you are dealing with at any given time. Without easy ways to identify them in situations where they may not be as evident as we might like, you could find yourself in a position where your formulas begin to return unprecedented results.

For this series of methods we are going to act under the rule that we cannot modify the contents of the cell in question to assess which type it is. This rule can also be understood as a situation where the cell contains a formula which we do not understand, and therefore should not modify.

We will be going over two methods which can be used to ascertain the data type of a given field.

The first is the Formula & Format Bar method, which uses the tools available on the Home tab to help narrow the scope of potential data types by comparing formatting outputs without causing permanent modifications to the original cell. This is the simplest method and the one which ca be done with the least time investment. Depending on the situation it can take seconds to complete.

The second is the IS- Test method, which makes use of the Information Functions within Excel to isolate a field down to the exact data type it contains. While this method does take a bit more time to complete in the chance that you need to put in multiple formulas, it will guarantee you concrete knowledge of how the system is interpreting the cell.

Determining Data Types: Method 1

This method consists of using a combination of the formula bar and the cell format tool (Home tab) to help identify the data type of the selected cell.

We first look at the formula bar in order to compare what we see in the cell versus in the bar.

For this example, we can see that while the cell displays ‘June 2, 2020’ the formula bar displays ‘2020-06-02 8:34:39 AM’. This tells us that the value is a valid date format value, meaning it is of the numerical data type.

The second step is to open the cell format menu and examine what the value would display as under different formatting options. Comparing the formatting options will often help to narrow down what the intended use of the value was.

For this example, the menu informs us that the value of this cell is ‘43984.3574’ when we look at the last entry (Text). We can also compare against this value as a fraction, percentage, time, and currency value. Given it holds more than two decimal places it is unlikely to represent a dollar amount, and due to its extraordinarily high value as a percentage that is also unlikely. This leaves it as a value which was either meant to be interpreted by its date/time value, or its numerical value.

Once both steps have been completed you should have a much more narrow scope of formats to work with. Error types are the only exception, as the will display a small triangle in the corner of the cell to inform you that an error has occurred and should be looked into.

In this example the value was a valid date value. Had we looked in the cell formatting menu and seen that the text value of the cell was the string ‘June 2, 2020’ this would have signalled that the cell was not properly formatted and was being treated as the text data type.