Whenever you enter data in an Excel worksheet, Excel checks the data type you’ve entered and classifies it according to four basic data types: Numbers Dates and times Boolean values Text Excel figures out the data type based on formatting and other information it uses to guess at your intention. Text is the category that Excel uses when Excel isn’t able to classify content as one of the other data types. To illustrate how Excel classifies data, I’ll enter some data in column B. Numbers are straightforward. As long as you haven’t overridden Excel’s default alignment, you’ll see numbers are right-aligned automatically. Text will be left-aligned by default. Dates and times are right-aligned like numbers. In fact, Excel handles dates and times as numbers. We’ll look at this in detail in another video. When a cell contains a formula, Excel classifies the content according to the result of the formula, not the formula itself. So, 2 + 2 returns 4, which is a number. On the other hand, if we use a formula to concatenate the letter A with the number 1, the result is text. In fact, if we concatenate any number with another number, we’ll get text. You can also explicitly set content as text by adding a single quote before the value. For example, if we enter ‘0001, we force Excel to treat this content as text, instead of a number. Finally, boolean values are a special data type for TRUE and FALSE values. Whether you enter them directly, or they are the result of a formula, Excel will display booleans in upper case and center-aligned. If you ever need to check if a value is text or not, you can use a special function called ISTEXT. ISTEXT takes a single argument—the value you are testing—and returns TRUE or FALSE. To make these examples easier to read, I can add a conditional formatting rule that highlights the TEXT examples. The formula, in this case, is very simple because we already have a boolean value in column D. We can just use the formula =$D5 with a dollar sign to anchor the column, and set formatting.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
