Excel has a bunch of different formats for numbers, currency, dates and times. Some of them are particularly confusing especially dates and times, but I'm going to show you the basics of these different formatting options that are available in Excel. Before I get started with some specific examples, I wanted to just show you where and how you can access these different formatting options. Here in the Home tab, in the number group, you've got this general drop down menu, this is where you can find all of the different types of formatting options. But there's a lot more if you go down here to the bottom, more number formats, it brings up this Format Cells box, you can get the same Format Cells box by right-clicking anywhere and going down here to Format Cells. So let me just go through some of the more popular formats for different cells in Excel. First of all, you can just type in a label like this, we can type in numbers here. If I just type in 8.23, that's going to be recognized as a number, numbers are right-centered and text is left-centered. If you wanted to specifically specify cell C5 as a number, you can go in here and specify as a number. You can also decrease and increase the decimal of a number by going up here and using these Increase Decimal and Decrease Decimal command buttons. So I could decrease for example to get it to 8.2, or maybe all you want to show is the ones digit there which is eight, so I can increase the decimal by clicking to the left. We can also put in really big numbers 34,872 for example, the default of that is not to have commas, but if you want commas you can always go ahead and click the "Comma style." When you do that, it added those two trailing zeros and let's just go ahead and decrease the decimal because we don't have any information there. So depending upon what your application is, you can tailor the different numbers however you want. There's also percentages, if we put in a fraction like 0.54 and you want to convert that to a percentage, you can go ahead and click up here, "Percentage" and that's 54 percent. There's scientific notation in here, for some of you you might be working with really big numbers, we can go ahead and convert that by using this drop-down list to scientific notation, it always gives you a preview there. So if I click on "Scientific", it will be 3.49E+04, so some of you know what the E is, I would encourage you to play around with some of these different formatting options. Another one is currency and that's typically used by business and finance folks, so if I had $10.54 in there, if you wanted to make this currency, you could click up here on "Currency". There's also accounting, so I'm going to go ahead and click on "Currency". Currency and accounting are very similar, if I click on this and assign this dollar sign, that makes it accounting. Accounting, puts the dollar sign all the way to the left, let's make this a currency and see how the dollar signs don't line up. If we put in another item here maybe a $1.23 and we make that accounting, then the dollar signs line up and it always makes the decimal points line up. Also, you can have negative $5.43 if I convert that to currency, but in accounting if you have a negative number, negative $67.12 and we convert that to accounting, then it always puts the negative value in parentheses, so negative dollar amounts in accounting are enclosed with parentheses. Just some other miscellaneous formats, if you wanted to specify a fraction in a cell, we could right-click on there and you could "Format Cells" or you could go up here to "Fraction" and now it's formatted as a fraction and so I could just type in like one third, and it keeps it as a fraction. You notice up here on the formula bar, it actually interpreted as a decimal number. You've got to be careful with fractions because if I put in 1/3, that actually is interpreted to mean January 3rd, and this brings up the date formats, so date formats are quite confusing to use. If you go up here you can select "Short Date", so I can convert January 3rd into something known as a short date, we can also convert that into a long date and it adds the day on there. So if you didn't know what day of the week a particular date was going to be, you can always do what I just did. So let's say, what is April 3rd of 2020 going to be? You can do that and then you can convert this to a long date and it will tell you, so that's going to be Friday. You also notice that when I type into a cell the date, so let's say it's January 27th, it automatically converts it to January 27th of the current year, so I didn't put in the 2020 but the current year is 2020, so it does that. If you just want it to leave it as a label, you can always precede it with an apostrophe, so if I did 1/27 with that little apostrophe before it, it always interprets it as text and it centers it to the left, so that's how you can force a label by using the apostrophe in Excel. Dates are actually interpreted internally by Excel as serial numbers or numbers, so let's go back to I guess my Friday, April 3rd, 2020. If we convert that up here to a number, you see that this is known as a serial number, and I'm going to go ahead and just decrease the decimal there. Starting on January 1st, 1900, that receives the serial number of one. So for example if I put in 1/1/1900, I could either convert that to a number, or there's a number value formula. January 1st, 1900 has a serial number of one, April 3rd, of 2020 has a serial number 43,924, so there have been 43,924 days since January 1st 1900. But we generally do not work with these serial numbers as dates, so I'm going to go ahead and convert this back to, let's just do a short date. Time is also interesting, in Excel I can put in 6:34, if you just put in 6:34 and I go here and you look up here at the formula bar, it's storing it as a custom time and it interpreted to be 6:34 AM. If you want to specify if it's AM or PM, let's just do 5:45 and you can type in P or PM, if you just do P, it will interpret it as 5:45 PM, and you see up here on the formula bar that that's what it is interpreting it as. If you wanted to do 1:23 in the morning, you can just put an A and it converts that, you can convert 5:45 PM to different types of time. There's all sorts of other formats, if you go to more number formats, it brings up this Format Cells box. You can also get this by the way by right-clicking on any cell and it's in the drop-down menu, but I can go here to time and if you wanted to convert it to something like military time, I could click on the 13:30 option which denotes that it's going to convert it to military time and so that's 17:45. Time is also stored as a serial number, there's actually a function in Excel called NOW with empty parentheses, this gives us the date and time. If I convert this, if I use the number value function for this, or if you convert that just to a number, you end up with a serial number. This 0.64 is telling us a fraction of the day, so right now it's about 3:30 in the afternoon and we're about 64 percent through the day. So I would strongly encourage you to just get in there and experiment around yourself with these different formatting options, that's the best way to learn about all the formatting options that are available to you in Excel. Thanks for watching.
