Hello again and welcome back to our course on Excel 2016.
In this section we’re going to start to look in more detail at how to enter and edit
data in Excel 2016 and then I’m going to set you your first actual exercise on the
course. Now before I start I am once again going to
be basically working with mouse and keyboard. I will refer to touch once or twice. If you
are using a touch device you’ll either need an onscreen keyboard, so you’ll get the
equivalent of this, or if you have an external keyboard for your touch device you need to
have that setup because we’re going to be entering quite a bit of text and numbers during
this and subsequent sections. Now the other thing to bear in mind is that
in this and the next few sections I’m really going to concentrating on getting the numbers
in and formatting them in the sense of making sure that dates look like dates and so on.
The actual cosmetics, how pretty what we’re doing looks, is something we’re going to
come back to later on in the course. Now the first thing that we’re going to
do is to start work on a new workbook and to make a new workbook, you’ve already seen
how to do that. You can also use a Keyboard Shortcut. The keyboard shortcut is Control-N.
So there is my new workbook and what we’re going to put in this workbook is we’re going
to do a very simple monthly electricity account. So the first thing I’m going to do is to
save the book with the name Monthly Electricity. There we are. And now what I’m going to
do is to enter the months of the year. Now you’ve already seen that we can actually
use a Series Fill feature here. I’ll come to that again in just a moment. The first
thing I’m going to do is just select a cell, it doesn’t really matter which one at this
stage, and I’m going to type in the first month of the year which of course is January.
Now when I’ve finished typing January if I press the Enter key two things happen. First
of all I leave that cell and that cell has its value January in it. But also notice that
the selection, the cell selection goes to the next cell below and that lines me up well
for typing in February. Now if after you’ve typed you press the Enter key by default the
cell that’s selected next is the cell below the one you’ve just typed in. But if instead
of hitting the Enter key I’d used the Enter, the tick mark on the Formula Bar, and if I
used that now after typing February watch what happens. February is entered but I’ve
now still got the same cell selected. So although I’ve got the same content my next selection
is different depending on how I terminate entry of data into a cell. If I now want to
go to K10 I can of course click in K10, enter March and of course now I’ve got the same
options but also I can terminate entry in a cell by just clicking anywhere else on the
sheet. So it’s important to recognize that when
you’re entering data what happens after you’ve entered that data depends on essentially
what you do at the end of the data entry. Now the next thing I want to do is to use
the feature that we saw before to get all of the months of the year in quickly without
having to type them all. So I’ve selected January. I’m going to use the little symbol
at the bottom right hand corner of the border there, drag down the requisite number of months,
release, and I have all of the months of the year.
Now notice that when we get down to September and November and December they’re actually
slightly too wide to fit into the available space and they spill over apparently into
the next column. Now in fact they’re not in the next column at all and September, for
example, is wholly within the K16 cell. But because we haven’t told it otherwise what
happens when something is too wide for the cell is that Excel lets it just display over
the next one. Now one way of getting round this is to make
column K wider and there are a couple of ways of doing this. One way of doing it is if you
hover over the top of the column over the K itself you see that down pointing arrow.
If I move to the right until it becomes a vertical bar with two arrows, one pointing
at each side, and click with the mouse I can actually drag to make that column wider. And
I can drag it to pretty much any width that I like.
As an alternative to that, let me just put it back. In fact I’ll overdo it and make
it really far too narrow. With that column selected if I go up to the Home Tab and the
Cells Group and the Format button at the bottom of that there is a dropdown and one of the
options on there is AutoFit Column Width. And if I select AutoFit Column Width then
it will automatically choose a column width whereby everything in the selected column,
column K in this case, fits comfortably within the column.
Now in fact you can apply that command to more than one column at a time. So if I’d
actually put data in columns K, L, M, and N, for example, I could select all four columns
and use that AutoFit feature and all four columns would have their width suggested to
accommodate the data contained within them. What I’ve done now is to make a mistake
in the spelling of September. And having entered the data and got the column width sorted out
I look at it and I think, “Oh dear I’ve spelt September wrongly.” To correct an
error if you just click on Setmember in this case and started typing you would overwrite
everything that’s in that cell. Now if I don’t want to overwrite it, I just want
to correct the bit that’s wrong, I’ve got a couple of options. One option is just
to press the X button, the Cancel button next to the Formula Bar and it goes back to what
it said before so I can recover what was there before. The other thing I can always do, remember,
is an Undo. So there’s always the Undo option. Of course if I press the Undo button too many
times, such as here, and undo that correction I made or the change that I made to the column
width don’t forget I’ve always got the Redo button and that will redo the column
width for me. But let’s go back to having the September
error. So I’m going to Redo the September error now. If you want to just edit within
a cell there are a couple of options. I find the simplest way to do it is to select the
cell and then click in the Formula Bar and do the correction actually in the Formula
Bar. So I’m actually looking at the Formula Bar as I’m typing here. Some people prefer
to just click and then click the cursor within the cell, so in this case the K16 cell, and
basically look at the cell as they’re typing. I find it easier to use the Formula Bar.
By the way if you don’t like the Formula Bar you can actually disable it. You might
want to have a little exercise now, give you a little extra job to do. See if you can find
in Excel Options where the option is to disable the Formula Bar. If you have trouble finding
it it’s on the Advanced Tab in the Display Options. But if you want to get rid of it,
perhaps to give yourself a bit more space, you can. I tend to use it so I tend to keep
it onscreen pretty much all the time. So I’ve got my list of months. I’m just
going to do a little Save here to save my work. Of course I’ve got AutoSave on anyway
but just to be extra safe. Now what I’m going to do against January
is to put in the cost of electricity in January. So I’m going to type in 124, hit the Enter
key and you can see 124 as the cost. Now, one of the most noticeable things is that
when you enter text such as January by default it is left aligned. When you enter numbers,
by default, they are right aligned in the cells. So as I enter these costs, hitting
the Enter key each time, the numbers are right aligned. However they don’t look much like
amounts of money. They just look like any regular numbers. And when you are entering
numbers there are many, many options for what the numbers actually mean. And one of the
things we’re going to do here is to actually indicate that these numbers are amounts of
money. So let me go back and click on 124 and what
I’m going to do on this occasion is to right click and I’m going to choose Format Cells
from the Contextual Menu. The first page, the first tab within dialogue is selected.
It’s the Number Tab. And you can see that by default is taken 124 as a general format
number. Note the little tip there. General format cells have no specific number format.
Now supposing I want to make it a currency amount. Let me click on Currency. Now in this
case because I’ve got myself setup to be using U.S. currency, I’ll tell you more
about that later on, it gives me a sample of what the number will look like if I choose
this setting. Note the sample there. Dollar symbol, 124.00. It gives me an option to change
the number of decimal places. It also gives me an option to change the symbol that’s
being used from a U.S. dollar symbol to just about any other symbol there is. And it gives
me a choice of showing negative amounts using either a minus symbol or red or a number in
brackets, the sort of accountancy type formats. I’m going to stick with the defaults here,
click on OK, and 124 is now displayed as $124.00. Now if I now want to apply that same format
to the other two numbers there I can select both of them. I could perform exactly the
same operation. It’s worth noting that I can access that Format Cells Dialogue from
a variety of places. Another one is up on the Ribbon on the Format button, the bottom
part of it in the Cells Group on the Home Tab there is a Format Cells button link there,
opens the same dialogue. I could do exactly the same thing again, change the currency
to the same settings. I can in fact also apply it to the cells that I haven’t yet put amounts
into or indeed to the whole of column L. If I selected column L, click on again Format,
Format Cells. There’s a difference this time because when I’ve selected column L
I’ve selected a load of cells. Three of them are in currency format and the rest will
still be in the same format they were in before which was that general format. When you make
a selected the covers different formats then when you bring up the Format Cells Dialogue
Excel doesn’t know which of those to show so it basically indicates that you have a
multiple selection by not showing any specific format. And what I would need to do now is
to make all of the relevant selections. So if I said Currency and I say the symbol I
want to use is the dollar symbol everything seems to be okay. Two decimal places, click
on OK. Now that format has been applied to all of those cells. And if I click say in
the amount for April and just type say 97.5, press the Enter key, then that is automatically
formatted with that currency format that I’ve now applied to all of those cells.
So finally then I filled in the rest of the month electricity costs there. I’m going
to Save that and that’s the end of this section. I’ll see you in the next one.