How to Format Numbers in a Microsoft Excel 2016 Spreadsheet
During this Microsoft Excel 2016 training tutorial video, we will show you how to modify numbers entered in a cell into various formats such as fractions, scientific, accounting, special and time formats. We will also demonstrate how to format number as text, as well as how to edit contents and delete characters in a cell.
Video Transcript
I’m starting this section with the Business Expenses workbook that I created in the previous section and I’m going to look first of all at some of the other available formats in Excel 2016. Then I’m going to turn my attention back to entering and editing data and point out some other very important things about that.
Now given that this is supposed to be an expenses sheet if I wanted to now start putting in the costs against the train fare York to London on Monday, March the 9th, I’m going to say that it was 127.50. So I’m going to put in 127.5. Now look what’s happened. Because I inadvertently had left a Date Format on that cell I come up with that really strange old date. So what I will normally need to do is to make sure either before I enter data or after enter data that I’ve selected the right sort of format.
Now if I had put 127.5 in the next one, well actually it’s the taxi fare so let’s not go too far. Let’s say 32.5. If I’d put that in that cell bear in mind that I haven’t formatted this cell as a Date Cell. It just stays as 32.5.
32.5, let’s look at what format has been assigned by Excel. It actually has assigned the General Format and it will very often assign the General Format. Now there are many number formats that can be applied to a cell with a number in it. So for instance I could assign a Fraction Format. Watch what happens if I apply the Fraction Format to that. I can choose the number of digits. I can choose whether I want it as say eighths or sixteenths or quarters. Supposing I wanted to do it as halves. Note the format isn’t applied until I click on OK and it becomes 32½.
Let’s format it again. This time I’m going to try Scientific Format. Note that I can choose the number of decimal places. Let’s stick with two decimal places. Click on OK and it becomes 3.25E+01. That’s 3.25 times 10 to the 1.
Let’s try another one. Now this time I’m going to apply a Counting Format. Notice that each time I change the category I have a different set of options on the right. So in the case of Accounting Format I can choose the number of decimal places and I can choose the symbol. If I were say a U.S. based accountant I may generally work in U.S. dollars but I may have clients around the world and for each client I would need to use the accounting symbols that are appropriate to that client.
So I’m going to stick with the default here, decimal places two, symbol dollar. Accounting Format is actually very similar to Currency Format. The main visible difference is that the currency symbol appears on the left of the cell. But also the way that negative amounts are dealt with is a standard way. So click on OK and I’ve now got $32.50 with the dollar currency symbol on the left of the cell.
Now could I apply that format to the figure above? Now bear in mind that I typed in a number there earlier on. It was converted to a date by Excel because the cell already had a Date Format applied to it. Let’s try changing that now to Accounting Format. Now watch carefully what happens here because if I select Accounting Format all seems to be okay but the symbol says None and the reason is that when Excel applied Accounting Format it looked at that cell and say that it already had a number in it although it was formatted as Date. And because there was no symbol, no currency symbol it stuck with none. So I need to be careful that I select my dollar symbol, click on OK and now I have my format applied.
Now I’d like to talk about a couple of the other formats now. Let’s go back into the Format Cells Dialogue. Towards the bottom of the list of categories, you’ve already seen Custom Category where you can actually make up your own formats. That’s out of scope for the course, certainly in these early stages. There are some custom formats already there and of course you can use those. And it’s pretty straightforward to create your own. But as I say that’s not something for this stage of the course.
Want More? Get Started With a Free Excel 2016 Course! Click Here
But I want to look at Special because depending on your locale you may have some special formats. Now I’m not going to go through any of these in detail at the moment but it’s worth being aware of them. And it’s also worth being aware of the fact that although you can apply these formats to cells what actually happens in the case of each format will depend not only on the format itself but also on what’s in the cell. So if I apply Zip Code Format to the description of the expense, Train Fare York to London there, click on OK, nothing really happens because as far as Excel is concerned that can’t be a zip code so it just doesn’t really do anything in this case. If I applied that same format to $127.50, don’t forget I typed a number in there, and applied Zip Code Format to that watch what happens. It does actually turn it into what it considers to be a reasonable go at that zip code. Of course I typed in 127.5 so it’s called it zip code 00128. So you have to be really careful about these formats in terms of what happens with many different types of cell content. I’m just going to undo that one.
Now as I mentioned earlier the available special formats depend on locale. So let’s just go back into Format Cells again and click on Special. I’m going to change my locale to, so let’s go for French (France) and I get a nice long list of available special formats there as well corresponding to various French special formats.
So that’s Special.
I want to talk about a couple of other very specific format options here now. I typed 7.2 in cell O13. I can format that as text. Now if I format it as text it’s absolutely fine. 7.2 can be text. You will notice there’s something unusual about it because it’s aligned on the left in the cell. But I won’t be able to use that in the same way for calculations as I could say a currency figure of 32.50. It is treated as text and of course text can contain numbers as text but it’s a very important distinction that it is treated as text in this case.
The other format that I could apply to that 7.2, again a very important one to be aware of, is Time Format. If I click on that as time it comes up with the rather mysterious time of 4:48. Now you might think how did it turn that into 4:48? Well the answer to that question is going to sound a little bit implausible but believe me it is true. The way that Excel stores times is that it stores the time as a fraction of a day. So in this case when it sees something like 7.2 it takes the 0.2 as the time. Now the 0.2 is a fifth, 0.2, of a day. So it divides 24 hours by 5 and comes up with 4.8 hours and that corresponds to a time of 4 hours and 48 minutes. So that’s where 4:48 comes from. So because of this way that Excel stores times 4:48 is a fifth of a day and therefore it corresponds to a numeric value of 0.2 of a day.
Now when you’ve got something in Time Format you have a number of important options. So here, for instance, you can choose the Time Format for the locale. Let’s go back to English (United States) and notice how it is on a twelve hour clock. So we have 1:30 PM Note the PM marker there. So let’s click on OK and what it comes up with, a fifth of a day, of course that’s in the morning. So it’s 4:48:00 AM.
If I wanted to do it on the 24 hour clock, go back into Format Cells again. Let’s choose the next format which is 13:30, that’s 24 hour clock format. That’s 4:48. If I apply that format to a cell and then I’m going to now type in that cell 2 PM what do you think will happen? I formatted the cell as 24 hour clock and I’ve typed 2 PM what do you think that’s going to say when I tick it? That’s right, 14:00. Excel looks at 2 PM and it says yep that looks like a time to me but the format applied to this cell is 24 hour clock format so I’m actually going to display it as 14:00. So Excel is pretty clever at that sort of thing.
The next thing I want to talk about in this section is editing the contents of a cell.
Now I’ve selected cell N9 and the contents of N9 are displayed in the Formula Bar. To edit those contents I can either go up to the Formula Bar and edit away in the usual way. I normally use the keyboard so I’d use the arrow keys to go left and right and the Backspace key to delete what’s to the left of the cursor, the Delete key to delete what’s to the right of the cursor. If you want to work within the cell and you may want to do this for example if you don’t use the Formula Bar or haven’t got it displayed at the moment. If you just double click somewhere within the cell the cell basically comes to the forefront. It looks as though the contents of O9 have been deleted there. They haven’t. It’s just the contents of cell N9 are now in front of those. You can use the arrow keys to go left and right. I can even go beyond the range of N9. I can go way off to the right. I could delete the word London and change it to say Bristol. And then when I finish I can either click elsewhere or I can use the tick mark next to the Formula Bar and my changes are saved.
If I’m using Touch it’s pretty much the same situation. Instead of a double click you do a double tap. You may want to make the screen a bit bigger if you’re going to do editing on a worksheet with as many columns and rows as this displayed. So let me just stretch this out. Now let me double tap on cell N12 and you can see I’ve got a cursor off to the right there. I can tap somewhere else, do my editing using my onscreen keyboard or my external keyboard if I’m using one, and other than that it works pretty much the same way that it works when you’re editing with a touch device anyway.
Want More? Get Started With a Free Excel 2016 Course! Click Here
And the next thing is pretty similar with touch or mouse and keyboard as well. If I want to delete individual characters within the contents of a cell I first of all need to make sure that I’ve got a cursor there. I can do this of course using either the Formula Bar or within the cell. As I’ve got the cursor in cell N12 here and I’m using touch at the moment I’ll demonstrate this with touch. It’d be the same with a keyboard. Press the Delete key and it’s the character to the right of the cursor that’s deleted. So press Delete once, that character is gone. If I want to delete the character to the left I use the Backspace key. If on the other hand I select a cell, let me select M10 using touch, and all I’ve got is the cell selected. Note I haven’t got a cursor within the cell. I can tell the cell is selected because I get a border. When I’m using touch I get those two little circles as well. If I now press either the Delete key or the Backspace key I will delete the whole contents of the cell. So let me press the Backspace key and the whole contents of that cell is deleted.
And that’s it for this section. I’ll see you in the next one.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!
