How to Understand Text & Numeric Formats in Excel

Learn how to understand text and numeric formats in this Howcast Excel tutorial with expert Shir Moscovitz.



So let's take a closer look at all the different text and numeric formats that Microsoft EXCEL has to offer. Let's go through it one at a time.

First thing I'm going to do - is I'm going to select the CELLS that I want to edit and then I'm going to do CTRL+1 to get to Format. In the Number tab, I have a lot of choices for which data type to use. The nice thing is EXCEL actually gives you a little breakdown of what it is. Here, the general data type is simply EXCEL's best guess at what the data is. So if there's TEXT in it, it'll think of it as TEXT. If it's a Number, it'll think of it as a Number. It doesn't do too much with it.

A Number, let's hit OK. Let's select these ones and do some cool Formatting with it. I'm going to hit CTRL+1. A Number is simply any Number of digits with up to as many decimals as you want. Standard - let's keep it as two. We can also use the separator for when it's over a 1,000. We can then choose our type for positive and negative how that looks. Currency ($) is exactly the same. It simply adds a symbol for Currency which we can choose different symbols if we have them in our database. Accounting is exactly the same as Currency except it adds an alignment of the different Currency symbols. So let me show you what I mean. Let's make this the Number Format with two decimal points. Let us make the Amount Donated the Currency with two decimal points and the Currency symbol. And if we wanted to align these Currency symbols throughout, we would instead choose the Accounting option. And now they all line up beautifully.

Let's go to some other options. The Date option chooses the best way to show the Date. You've a lot of choices. You can choose the day of the week depending on your regional settings and we have a lot of different choices for how you want it to show. You can even include the Time inside the CELL itself. Let's choose this option which I like to do. The two digits for the month, two digits for the day, two digits for the year. Let's hit OK. We can also choose a specific Time.
If we wanted to take Time only, we can have that show up with an AM/PM, with a 24-hour or with seconds as well or with the Date. Let's keep it as - with the PM and AM like that. Cool.

Let's go to the next one which is a Percentage. So over here to the Efficiency Percentage and let's choose the Percentage choice with two, with only one decimal point. Beautiful. Next we'll go to the Fraction which we have a bunch of choices for. We can choose up to one, two or three digits for the numerator and for the denominator. We can choose a way of rounding the number to the nearest half, quarter, eighth, sixteenth, or by tenths or hundredths. So we have a bunch of different choices here. I usually keep it as two digits to keep it simple. If we have a very large number we want to show using Scientific Notation, we simply choose on the Scientific option and choose the number of decimal points. Anything that has TEXT in it, let's go and choose TEXT to make sure it is treated as TEXT. And even if there are Numbers in the CELL, it still treats it as TEXT.

We have another option for Special Formatting which we can go to the Special and then choose, in this case, Zip Code. So let's choose a Zip Code+4 and this is a sample of what it looks like. And there we are. Let's choose a Special Formatting for the Social Security Number which has dashes in between. And let's choose the Special Formatting for the Phone Number as well which has parentheses and a dash. Let's widen this COLUMN. The last type we can use is Custom. We can change it anyway we want. It'll always show the way we want to. If you have a lot of time, I encourage you to check it out. Each of these symbols mean something else. So go around and play with each one and see what each does. You can show any data in any specific format that you want. This is for more advanced users. And that's how you differentiate between the different TEXT and Numeric Formats in Microsoft EXCEL.

Popular Categories