Excel Beginners

Data Types and Formatting: Step 2 To Mastering Spreadsheets

Rules are sometimes meant to be broken

Determining Data Types: Exceptions And Loopholes

With these two methods it should be a simple process to identify acknowledge the data type of any cell you come across. It is important, however, to not be misled into believing that all outputs can only ever be of one data type. This case exists between the text data type and its relationship with the numerical data type. Because when a cell is formatted as text it is stripped of all its other properties it is very possible that an otherwise valid number is being treated as a text value.

Thankfully Excel interprets the contents of a cell by its value when it is called within a formula, and does not limit itself by the data type of the source value. Under this logic, a cell which is formatted as text but contains the value 42 can still be used in mathematics equations while the cell itself remains a text value.

When coming across workbooks which you did not create, and you find values stored as text it is very important to try and understand why this was done. It is possible that it was a simple mistake, but it could also have been done due to other formatting or regional differences where a consistent presentation is required. Often times these formatting limitations are put in place as a result of custom formatting options not being as widely used as they could be.

If you believe you have come across a situation like this, always look for the green triangle in the top corner of the cell you are concerned with. Unless the signal has already been hidden by another user, you will be able to see what Excel believes to be the problem with the cell. In the case of a number being stored as a text value, the traditional message is ‘Number Stored as Text’ followed by the option to ‘Convert to Number’.