Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
When you type a value into Excel, the program tries to identify what sort of data you just entered and assign the cell an appropriate format. If you want to assign a cell a format yourself, or if you need to change a format Excel assigned, you can do so using the controls on the Home tab of the Ribbon. You can find the number formats here on the Home tab of the Ribbon, and in the number group. The most common formats that you use are already on the Ribbon. I'll show them to you right now. Let's say that you want to add a currency format, or in this case the accounting format.
So I have the number 14,000 here in cell C3. If I want to make it look like an accounting number, in other words I want to give it a currency symbol, I can click the Currency button, which ironically enough applies the Accounting format. Yes it is called the Currency button, and it does apply what's called the Accounting format. Now you will notice that this button has a down arrow. If you want to apply another currency format, you can click it, and you can apply dollars, which is the default, UK pounds, the Euro, the Chinese Yuan, Swiss Franc, or you can display more Accounting formats and display a lot of others.
In this case, I'll just leave it with the US dollar. The next format I will cover is the Percentage format. Percentages indicate parts out of a hundred, so, for example, 25% means the 25 out of a hundred of something has a particular property. It's 25% of that whole. When you write 25% as a decimal, you write it as .25. When you express it as a percentage, such as by clicking the cell and then clicking the Percent button, Excel displays .25 as 25%.
Next we have the Comma style. The Comma style is a style that adds a separator, in this case a comma, every three digits. It's often called the thousands separator because it separates the hundreds digit from the thousands digit. In this case, I have 95,000 and if I click the Comma button, Excel applies the style, and I have 95,000.00. One of the elements of the style is that it has two digits to the right of the decimal point. If you want to get rid of those digits to the right of the decimal point, you can use the Decrease decimal button.
So if I click it twice, it goes away. If I want to bring the decimal points back I can click it there, and now we have our two digits to the right of the decimal point again. You can do the same thing with the Percentage style. So let's say I click here and change its format by clicking the Format List box's down arrow and change it back to general, so I have .25. If I change the value to .2596 and press Return and style it as a percent, then the Excel rounds up to 26%, because by default the Percentage style doesn't show tenths or hundredths of the percent.
It stays at two or more digits. If I want to I can add decimal points there, so I can go to 26.0%. Again, we are rounding because the values 25.96. If I do it again, then I get the full value of 25.96. So if you're working with percentages, and you need more than just the base percentage, you need the decimal points as well, you can use the Increase Decimal button to do that. Next I'll talk about dates and times. I will click here in cell C6. When you enter a date, you can enter it in several ways. For example, in the US, months come first, then days, and then the year.
So if I were to type in say September 4 and hit Return, Excel applies one of its date formats and calls it 4-September. If I were to type in September 4, 2010 and press Return, you get the same thing; basically Excel ignores the year and applies the date format. But let's say you want to apply another Date format; you actually want the year. To do that, you can go up to the Number group, click the Format list box's down arrow, and click Custom.
When you do, Excel displays the number page of the format cells dialog, and then you can click Date to show all of the available Date formats, and you can select whichever one you want. In this case I will do 3/14/01, which gives me the two digit month, the two-digit day, and the two-digit year. If it were okay to have a single digit for the month and either one or two digits for the day and two digits for the year, then I could select this one, but in this case I'll use this format. Click Ok, and there we have my date.
If you want to enter a time, Excel operates in a similar manner. So if I click here, in cell D6, and I type in the time 1:15 P.M. - it's very important that you have the space between the 5 and in this case the P. If you didn't and I will backspace over this, and I just had this 1:15 P.M. Excel treats it as text, but if I add the space Excel, treats it as a time. And you'll also see, on the formula bar, that Excel displays the seconds as well, so you have the hour, you have the minutes, and you also have the seconds, although this particular format doesn't display the seconds. If I wanted it to display the seconds, I can select the format, once again, by clicking this down arrow, clicking Custom, clicking Time and then selecting a format that displays seconds, such as this one.
There are also formats that let you combine date and time, so, for example, if I wanted to display 09/04/10 1:15 P.M., I could type that in, press Return, and Excel displays that in a combined date time format. The last two items I will show you deal with phone numbers and zip codes, which are the US's postal codes. If you have a number that is a phone number, you can either type it in exactly like this, or you can format it like a phone number, with a three digit area code, and so on.
To do that, you click the cell, you click the format number list boxes down arrow, click Custom, click Special, and then you have Zip Code, Zip Code + 4, Phone Number or Social Security Number, and these items do change based on your location. For example, if you're in France and are using the French version of Office, or you have your location set to France in the Mac OS, then it will display special items that are appropriate for your location. So this is a phone number.
I will click Phone Number, click OK, and Excel displays it as a phone number. Same thing here. I have zip code, and in this case it also has the four digit Zip + 4 extension. So if I click Number format box, go to Custom, click Special and then Zip + 4, Excel applies the proper format. Proper formatting makes your data easier to understand. Take the time to experiment with the number formats and how to change them, so you can present your data effectively.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91221 Viewers
80 Video lessons · 138197 Viewers
59 Video lessons · 57027 Viewers
52 Video lessons · 70666 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.