Other Excel Add-In, Templates and Training
In this lesson we
will look at how Excel sees Dates and Times and how formatting a cell
affects the data within it. We will also look
at the Undo/Redo feature as well as Custom formats and the
Format Painter. This will give us a good background before we move on
to what Excel is best at - Working with Numbers - in the next lesson.
You may or may
not think that it is odd that we do not go into great detail with formatting
cells. This is because while it may enable us to make a spreadsheet look very
pretty, it is often at the sacrifice of correctly setting up your spreadsheet,
or in other words, the nuts and bolts. We feel that whilst Formatting is
pleasing to the eye, it should not be covered too deeply at this stage at the
expense of the real workings of a spreadsheet. However, having said
this, we do encourage you (as suggested below) to play about with any formatting
not discussed here and direct any questions you may have to us.
Format according to the Microsoft Dictionary and Thesaurus is:
This pretty much
sums it up! The big difference with formatting in Excel as opposed to many other
Office Applications is that there is a LOT to choose from! This is because Excel
needs to be able to work with numbers of all types and from all corners of the
globe, as well as text.
On top of this we
can also apply formats of our choice to the cells themselves as well as any data
contained within them. As far as numbers alone go Excel has over 100
different types of formatting that we can apply. If this is not enough we
can even define our own with Custom Formats (discussed below).
It is our belief
that there are two very important facts that we should all be aware of when
working with formats in Excel, these are:
Numbers are aligned to the right of a cell by default, and Text is
aligned to the left of a cell by default
format of a cell will not change the underlying value of a cells
If you remember
nothing else about formatting then remember these two facts. We will
explain the second point in detail later in this lesson. Let's for now look at
Whenever we enter
a number in a cell on a new worksheet Excel will take an educated guess at what
type of format it should apply. By default all cells in Excel have what is
known as a General format. Most of the time you will find yourself
accepting the formatting that Excel applies, but at other times you may wish to
change it. The context in which we are discussing formatting here is not the font type,
size or color, but rather the Number format eg; Dollar, Percentage,
Decimal places etc.
We must stress however, that it is far safer to always
leave numbers right aligned and text left aligned (Excels default). This
way you can tell at a glance what is a number and what is not! I expect by
now you are thinking, "I already know the difference between a number and
text - it's obvious!"
This is not
always the case in Excel. It is quite possible for numbers to appear as text and
text to appear as numbers. This may not be quite the case when starting to use
Excel, but is very likely as you delve deeper and create your own spreadsheet.
Dates in Excel
You will find
that Dates and Times in Excel can often seem confusing! But they often
play a critical part in most spreadsheets so it is important that you have an
understanding of how Excel interprets them.
Dates are seen by
Excel as whole numbers, starting from the number 1 for 1-January-1900,
the number 2 for 2-January-1900 and so on.......... These are known
as serial numbers. This is so we can use them in calculations such as
adding and subtracting etc. We will show you examples of date formulas in the
How we enter dates in Excel is extremely important. If
we do not insert a date in a valid form, Excel will not know that it is a date.
You should be able to tell immediately if a date you enter is valid or not as
Excel will align a valid date to the right of your cell (Number) and
align a possible invalid date to the left (Text). When Excel
recognizes a date as a valid date it will change from the General format
(default for all cells) to a built-in date format.
The type of date
format Excel defaults to is not set from within Excel, but from the Regional
Settings within the Widows Control Panel. (See HELP to find
this, as the specific location may be different depending on which version of
Excel you are using). As these settings are regional you will most likely
simply accept the format applied.
When we enter
dates on a Worksheet we must also use a valid date separator, such as 12/12/2004
or 12-12-2004. If we want our date to look different to this we must format it
after we have entered it. Lets try a couple of simple exercises to stress
what we have discussed so far.
In cell A1
type: 12/12/2004 or 12/12/04. Both are valid.
A1 and right click, then select Format Cells and then click the
Number tab. This dialog box is the Format Cells dialog box.
This is where you can apply formatting to both the cells themselves and the
data contained within them.
If the default date (as
set in the Regional Settings) is not within the Category: box, Excel
will default to Custom.
Ignore this for
now and click General under Categories. Now look in the
Sample: box (top right). You should see the number: 38333.
This is the number for our date: 12/12/2004. This is because the
this date is 38333 days from the 1/1/1900
Date from within the Categories: box.
all the different dates within the Type: box and see the result in the
Sample:: box. Select the format 3/14 and then click OK
Your date should
now appear as 12/12. I say appear because while we may have
changed its appearance we have NOT altered its underlying value which is
12/12/2004 or 38333. If you are still selected in cell A1
look in the formula bar and you should see 12/12/2004. So while it
may appear we have dropped the year from the date we have not! In fact it
is not possible to enter a valid date that has no Year, Day or Month. We
could in fact format this cell to appear as 1/1/1985 and still have the
true value of 12/12/2004 or 38333. We will explain this when
we go on to Custom Formats.
If we enter a
date into Excel and omit the day eg; Dec-2004 Excel will recognize this
as a valid date, but you should be aware that Excel will (by default) assign the
first day of the month to the date. This means that while you may only see Dec-2004
in the cell, the underlying value of the date will be 12/1/2004 or
38333. This means that it is not possible to have a valid date in Excel that
does not have a day of the month assigned to it.
Times in Excel
Ok, if you are
still with me let's now look at Times in Excel. While dates in Excel are
seen as whole numbers (serial numbers) starting from 1, Times are seen as a
portion of a day (decimal fractions) with 1 being equal to 24:00:00 or a
whole day. This means that:
We can see this
by entering any one of the above times in Excel and then formatting the cell
containing the time as General, just as we did with the date. There are
only three ways you can enter a valid time in Excel.
Make sure your
cells are formatted as General
To enter a time
based on a 12-hour clock, type the time followed by a space and then AM
or PM. eg: 5:30 PM
Type the time
followed by a space and then A or P. eg 5:30 P (Excel
will convert this to 5:30 PM)
To enter a time
based on a 24-hour clock, type 17:30
If you just typed
5:30 Excel will (by default as your cells are formatted as General
by default) base your time on the 24-hour clock and store your time as
5:30:00 AM. In other words by default it sees all unspecified times as
24-hours but stores them based on a 12-hour clock.
The easiest way to see this
is to type 5:30 in any cell, then select that cell and look in the
Formula bar. No matter which method we use to enter times we must separate the
hours, minutes and seconds by a : (colon). If we omit the minutes and/or seconds
Excel will (by default) assign zero minutes and/or zero seconds. So
entering a time, as 5 P will force Excel to see it as 5:00:00 PM.
Obviously this is not the case for a time entered based on a 24-hour.
Entering 17 will be seen as nothing more than the number 17.
Entering a Date and
Time in Excel
Now that we have
covered the fundamentals of dates and times, we can have a quick look at
entering dates and times into the same cell. To enter a valid date and time in
the same cell, you simply type any valid date, then a space and then any valid
time. Excel will then store this as a whole number for the date (serial
number) and a portion of a day for the time. (decimal fractions).
Type the date
and time 12/12/2004 18:00 in any cell
Right click in
the cell and select Format cells and click the Number tab, then
General under Categories:
Look in the
Sample: box and you should see 38333.75, where 38333
represent the date (serial number) and .75 represents the time (decimal
What we shall
look at now is the Custom Format option under Categories in the
Format Cells dialog box. This is a much better way to grasp the
concept of applying formats from the Numbers page tab than using the
built-in formats. The reason for this is because you can easily see what
affect one of Excel’s built-in formats has on any data in the cell by simply
selecting the format and looking in the Sample: box.
Now that we have
gone through the fundamentals of dates and times in Excel, we will use a date
and time as an example so that you can see how the custom formats can be
applied. We will only look at the basics of custom formats as to try to go
into it with too much detail would more than likely add confusion, but as with
any area of Excel, if you have a question about it, feel free to ask and we will
clarify for you.
For this example
Type 12/12/2004 23:59 into any cell.
Make sure you are selected in the cell you have just typed the date and time in,
right click and select
Click the Number tab on the Format Cells Dialog Box, then click
Custom in the Category box.
By default you
should have the format m/d/yyyy h:mm showing in the Type box or
something very similar. As mentioned before, this is determined by the Regional
settings in the Control Panel of your Windows operating system.
What we will do
now is go through the steps of how to apply a custom format. While we are using
a date and time for our example, we can apply a custom format to any type of
data in any cell.
Lets click inside the Type: box (first line), so that
the mouse insertion point is flashing at the end of the last character. We
will use this default format as a starting point for a custom format.
down your backspace button until the format showing in the type box is
completely gone. Note: This will not loose this particular format, it is only a
means of adding a new one.
If you then look in the
Sample: box, you will
see that Excel will be showing the serial number for the date and the decimal
fraction for the time. This is simply because the default format for any cell
that has formatting removed is the General format.
To apply a format
to our date and time, so that it uses abbreviated text to display the month
name, type mmm.
If you look in your
Sample: box, you should see the
abbreviated month name Dec. To get the full month name, you simply need to type
one more m (mmmm).
You should now have the full month December
showing in the Sample: box.
From this point, we can choose to, or not
to, use the
/ (forward slash) or – (hyphen) as our date separator. It is important to note
here, that as we are only changing the format of the cell, and NOT the
underlying value, we do not have to enter the format of our date the same way as
we must enter the date itself into a cell. We should also note that any
valid custom format we apply to a Workbook that is saved, will always be
available to us.
Let us now push
the space bar and use a space as our date separator, or if you like, use two or
Now type a
single d and you should see December 12 in your Sample:
Push the d
again and your date should not have changed. This is because we have a two-digit
number for the day of the month. If we had any day before the 10th of the month,
typing dd would place a zero (0) in front of the day of the month.
Push the d
again, so that you have ddd and you should see the abbreviated day Sun.
Push the d
one more time, and you will see the full weekday Sunday.
So from this, we
can safely assume that the 12th of December in the year 2004 is a Sunday.
Again, push the
space bar and then push the y and you should see 04.
so you now have yyy, you now have the full year – 2004.
Again push the
space bar and type the letter h and you should see the hour 23. From
here, again you can choose to, or not to, use a space(s) or a colon as your hour
and minute separator.
example, we won’t use anything, so push the m so that you have hm
Look in your
Sample: box and you will see December 2004 2359. This is a perfectly
valid date format. But as we discussed above, we would not be able to type this
directly into a cell and have Excel recognize it as a valid date and time.
should you have a date and/or date and time that you wish to appear different to
the formats that Excel accepts when entered directly in a cell, all you need do
is simply change the format of that cell. In fact, we stress to all our students
that even if you are using dates as headings, you will probably be better off
entering the date as a real date and then formatting the cell(s) accordingly.
Ensure you are
still selected in the cell that contains the date and time and have the
Format Cells Dialog box visible and select Custom in the Category
click in the Type box after the last character, push the backspace so
that there is no formatting at all in the type box
Now type ”any
old date and time” including the quotation marks (“”).
and you should see what appears to be the text any old date and time
in the cell containing the date and time.
You should notice that what
appears to be text is aligned to the right as opposed to the left. If you click
in the cell, and look in your formula bar, you should see that the underlying
value of the cell has not changed at all. We can still reformat this cell to
reflect the original date by defining the date/time format.
Of course, the
same could apply to any number or text when working with the formats of the
cell. No matter what format we apply, the underlying value will never change.
However, having said this, there is one instance in which formatting can have an
adverse affect on the value of the cell. This is the format Text which is
also found under Category: on the Number tab of the Format Cells
Dialog Box. I won’t go into any detail at this point as it will be best
explained when we look at using formulas in Excel.
As you will have
noticed, while we had the Format Cells Dialog Box visible, there were
other page tabs that can also be used to manipulate the formatting of a cell.
The best way to find out what each feature on these page tabs will do
is to simply type some data in a cell or cells and have a play with the
settings. This way you will see immediately what affect it has on your data and
you cannot do any harm whatsoever in doing so.
exception to this is the Protection page tab. We will be
discussing this in a later lesson when we look at Protecting Worksheets. Many of
the most common options and features available through the Format Cells
Dialog Box can be found on the Formatting Toolbar. Remember to wave your
mouse over each icon for a short description of what it does.
Goto Next Lesson
Back to Excel Add-in, Templates and Training Main Page
Get the Excel VBA Training for free when you purchase our Technical Indicators now!