Hello again and welcome back to our course on Excel 2016.
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.
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.
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.