Hello again and welcome back to our course on Excel 2013.
In this section, we’re going to continue looking at entering and editing data and in
particular at some of the data formats that are available in Excel 2013.
We’re going to continue with this straightforward sheet that we created in the previous section.
We’ve got a couple of columns.
We’ve got Column B which has the description of some business expenses and Column C which
has the actual amount of money spent.
We’re now going to put a date on each of these expenses.
And the dates are going to go into Column A. Now the entry and editing of dates in Excel
2013 demonstrates a couple of very useful fundamental things and I want to spend a little
bit of time on dates in this section.
The first thing it demonstrates is that if you type part of a date into a cell, I’m
actually going to use the Cell A2.
So this is the date for the first expense in the list.
What Excel thinks I’ve done will to some extent depend on the local setup of your device.
Now I’m going to type 10/11.
Now as you may appreciate it wouldn’t necessarily be the case that 10/11 is a date.
10/11 could be a fraction.
It could be 10/11th’s of something.
It could just be some sort of part code or other identification on an item.
But let’s see what happens.
Having entered 10/11 if I now press the Enter key watch what happens.
And what happens is that Excel treats that as the 11th of October.
Now that’s because dates in my case are being handled in U.S. Date Format.
In another part of the world with somebody using a different format such as the U.K.
Format that would be treated as the 10th of November; so 10/11 is the 10th of the 11th
month.
So, first of all, there’s a factor here related to locale and in order to understand
that, we need to take a look in the Windows Control Panel.
In Windows 8 from Control Panel, if you click on Region then we see this dialog and within
this dialog a couple of things.
First of all, I’m going to just click quickly on the second tab which is Location.
My location is United Kingdom but I can also set various aspects of how things are displayed
according to another location.
And the other location I’ve got is U.S.
So if I go to Formats, the Format in which I basically display Dates and Times for that
matter is English United States.
Now there’s basically for dates, there’s a Short Date Format and a Long Date Format.
And there are examples of each based on the day that I’m recording this near the bottom
of this tab of the dialog.
The Short Date gives the month number which is 12, stroke, day number which is 3, stroke,
year 2012.
So that’s M/D/YYYY.
That’s four digit year.
The Long Date has actually four things in it.
First of all, it has DDDD; now, the four D’s means that it’s going to show me the day
of the week.
So, first of all, it says Monday then four M’s, MMMM, means it’s going to give me
the month number in four.
So instead of putting 12 it puts December, the single D means a 3, and YYYY, 2012.
So they are the defaults for displaying dates.
Now note that in this case, I’ve got a day number, a month number, and a year number.
So when I’ve got all three, it’s displayed in that format.
Now when you look at your equivalent settings on your device, you may or may not be happy
with these defaults.
But you need to go into Control Panel to change them.
Let me just give you an example.
The drop down to the right of Short Date offers you various ways that you could display those
depending on your locale.
When you set the machine up, you’ll get a default for these which are usually the
one that people use for your locale.
But if you want to change it, you go into Control Panel to change it.
However, I should point out we’re only talking here about the defaults.
You can in Excel 2013 in any particular case change it to anything you want to.
So let’s go back to Excel.
So let’s see that default Short Date in action.
On this occasion, I’m going to now enter the same date as I did in A2, but I’m going
to enter it with all three parts specified.
So the month number is 10, the day number is 11, the year number well it’s 2012 but
I’ll just put 12; 10/11/12, press Enter, and as you can see Excel used the default
Short Date display format which is 10/11/2012.
That shows how the default date display works.
Let’s now look at making a couple of changes to the format in which both of those dates
are shown.
So let’s take a look at formatting the two dates that we’ve put in already.
And let’s start with that first one.
Now the date is displayed there is 11-Oct.
And I actually entered it as 10/11.
To look at the formatting of any individual cell on a worksheet, there’s a couple of
options.
But one of the options is on the Ribbon.
It’s in the Cells Group and if you click on the Format button or the little arrow to
the right of the Format button, one of the options right at the bottom is Format Cells
and that will format the selected cells.
Now the selected cells in this case are just that one, the one that says 11-Oct.
Now if you look at the Format Cells dialog, it has several tabs.
We’re only interested in the first one at the moment.
And what happens when you enter data in Excel 2013 is that Excel tries to figure out what
sort of data it is.
Now a little bit later on we’ll look at this very special category at the top, General,
and you can see categories here like Date.
You’d sort of expect that to be a date really wouldn’t you?
But no, what Excel decided was this is a custom piece of data and the format it decided on
for this custom piece of data was d-mmm.
Now the little D means the day number, so that’s the 11th.
And three little M’s as opposed to four big M’s, three little M’s gives you the
abbreviated form of the month name.
So instead of putting October it puts Oct.
To change that format, it’s really straightforward.
All I have to do is to choose one of the others.
Now not only can I choose one of the others and there are quite a few there, you can actually
type your own in if you wanted to.
So if I wanted to change the format, I could literally just type four big M’s instead,
D, hyphen, four big M’s.
Now watch what happens when I click on OK.
What I’ve now got is 11-October.
So you’ve seen a couple of very important things there.
One of them is that for any cell or in fact any group of cells that you have selected,
you can look at the format of that cell or those cells using the Format Cells dialog.
And secondly, you’ve seen that you can change that format.
Now you cannot change the format of every cell to any format you like.
So for instance, the text there, Train fare New York to Atlantic City, you couldn’t
turn that into a date.
But you can do an awful lot of conversion, some of which might surprise you.
But let’s now look at this second date, the one in A3 where we entered 10/11/2012.
Now again we’re going to go into Format Cells to look at that.
But on this occasion I want to just quickly show you how to do that with a touch screen.
To do the equivalent with a touch screen what you need to do is to tap once on the cell
to select it.
You then get a version of a sort of mini toolbar and at the right of that mini toolbar, there’s
a drop down.
Click on the drop down and that gives you a contextual menu.
And one of the items on the contextual menu is Format Cells.
So you can tap on that and that brings you into exactly the same dialog that we looked
at just now.
Now what has happened here is very important because here because I entered three parts
to the date, 10/11/12, Excel actually said that must be a date and it has formatted this
cell as a Date Cell with a Date Value in it.
And in addition, if you look on the right here, this box on the right, it has indicated
which format it has assumed; 3/14/2012.
Now note the little asterisk there.
The asterisk there says look at the text at the bottom, second sentence.
Date formats that begin with an asterisk respond to changes in Regional Date and Time settings
that are specified for the operating system.
So it has assumed not only that it’s a Date but it has decided that the format it’s
going to use is the default Short Date Format.
Now one of the advantages of this format and it’s usually an advantage.
Sometimes it can be a little bit of a disadvantage.
But one of the advantages is that if I stick with these and somebody else looks at this
same worksheet on a different machine, so for instance the computers that I use some
of them are formatted as U.S., some of them are formatted as U.K.
If I took this worksheet with this date setting and looked at it on a U.K.
PC, the date format would be changed automatically on the display to show the U.K. format.
So instead of saying 10/11/2012 it would say 11/10/2012 because in the U.K., we have the
order Day/Month/Year not Month/Day/Year.
So this is a good format from the point of view that other people looking at these dates
in other locales will see them in their local format.
If I didn’t want it to be flexible, if I wanted it to always look like that I’d choose
one of the formats that doesn’t have an asterisk.
Now just on this occasion, and just to demonstrate this a little bit more, let’s suppose that
I still want to use one of the formats that responds to changes in locale.
Let me say I wanted it in the Long Format.
So I’m going to change the format to the Long Format and I’m going to click on OK.
Watch what happens.
Well what happens interestingly is that I get a line of hash symbols.
Now with Excel, you’ll see a line of hash symbols quite a bit and that means there isn’t
room to show what’s in this cell.
And one thing I could do would be to change the format back to the old format.
But what I’m going to do on this occasion is make Column A wider.
Now I hope you can remember how to make a column wider.
If you’re using keyboard and mouse hover over the right hand edge of the column letter
at the top there, that’s A. When you’ve got the double sideways pointing arrows and
the vertical bar click with the mouse, drag it out, and there we are.
We can now see the full length default date format for that date.
So, just a couple of other things to finish off with in this section.
We specifically been mainly looking at dates but we’ve also looked at using Format Cells
in general.
Very often when you’re dealing with spreadsheets in Excel, you’re trying to format a whole
block of cells.
Sometimes it can be an individual cell which is what we’ve looked at here.
But you may want to format a block of cells, a whole column, several columns, several rows;
many, many possibilities.
You always need to be aware of what you’ve got selected.
When we were working just now in the Format Cells dialog we had that single cell selected.
I could select both of these cells just by clicking in the first one, dragging down to
the second one, or the equivalent on a touch screen.
I could format all of these cells, that includes the three I haven’t put in values in already.
Or indeed I could format the whole of Column A. Let’s try formatting the whole of Column
A as this Long Date format.
So with Column A selected, all I need to do is to right click and one of the options there
on the contextual menu when I right click with the mouse is Format Cells.
Now note when I’ve got many cells selected and they currently have different formats
so we know already that these two cells have got different formats at the moment, I’ll
have no selection shown here.
So what I’m doing here is choosing a format for all of the selected cells, that’s the
whole of Column A. And if I’ve already got a conflict if you like, if these two already
disagree, it won’t default to either of them.
It’ll just leave me with a completely free choice to enforce on all of the cells.
So let me go for Date.
Let me specify that Long Format.
Now see what happens when I click on OK.
And I can see that both of those have now got the same format.
Let me type a date into one of those empty cells now.
Now the date I’m going to type is the next day in the sequence.
So these were on October 11th.
Now I’m talking about October 12th.
But I’m only going to type 10/12 and press Enter.
Watch what happens.
It automatically gets the format that I’ve specified for Column A and I don’t have
to type anymore than enough information to tell Excel 2013 what this new date is according
to the new format.
So there we are.
We’ve covered quite a bit about data formats, particularly in relation to entering and displaying
data.
In the next section, we’re going to look at a few of the other formats that are available,
but we’re also going to look at editing data and in fact at deleting data as well.
So please join me for that.