In this lesson we will look at how Excel sees Dates and Times and how formatting a cell affects the data within it (note European style dates are used). 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.
The term Format according to the Microsoft Dictionary and Thesaurus is:
The arrangement of data for storage or display
A method for achieving such an arrangement
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
Changing the format of a cell will not change the underlying value of a cells content.
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 the first.
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.
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 next lesson.
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/2008 or 12-12-2008. 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/2008 or 12/12/08. Both are valid.
Select Cell 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. (You can also get to the Format Cells dialog by selecting the Dialog box launcher under the Numbers option on the Home tab).
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: 39794. This is the number for our date: 12/12/2008. This is because the this date is 39794 days from the 1/1/1900
If we enter a date into Excel and omit the day eg; Dec-2008 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-2008 in the cell, the underlying value of the date will be 1/12/2008 or 39783. 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.
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:
24:00:00 is equal to 1
18:00:00 is equal to 0.75
12:00:00 is equal to 0.5
6:00:00 is equal to 0.25
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 05:30:00. 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.
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).
Try this:
Type the date and time 12/12/2008 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 39794.75, where 39794 represent the date (serial number) and .75 represents the time (decimal fraction).
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 Excels 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 try this:
Type 12/12/2008 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 Format Cells.
Click the Number tab on the Format Cells Dialog Box, then click Custom in the Category box.
By default you should have the format dd/mm/yyyy hh: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.
Hold 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.
10. Let us now push the space bar and use a space as our date separator, or if you like, use two or more spaces.
11. Now type a single d and you should see December 12 in your Sample: box.
12. 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.
13. Push the d again, so that you have ddd and you should see the abbreviated day Fri.
14. Push the d one more time, and you will see the full weekday Friday.
15. So from this, we can safely assume that 12 December in the year 2008 is a Friday.
16. Again, push the space bar and then push the y and you should see 08.
17. Type yy so you now have yyy, you now have the full year 2008.
18. 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.
19. For this example, we wont use anything, so push the m so that you have hm together.
20. Look in your Sample: box and you will see December Fri 2008 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.
So, basically, 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.
Try this:
1. 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 box.
2. Again, left click in the Type box after the last character, push the backspace so that there is no formatting at all in the type box
3. Now type any old date and time including the quotation marks ().
4. Click OK 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. We wont 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.
The only 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 Home tab of the Ribbon. Remember to wave your mouse over each icon for a short description of what it does.
As with most Microsoft Office applications, Excel contains a very handy feature that allows us to Undo or Redo certain steps we have taken.
The Undo and Redo can both be found on the Quick Access toolbar. These are represented by the arched left pointing blue arrow for Undo, and the arched right pointing blue arrow for Redo. There are also shortcut keys - Ctrl + Z for Undo and Ctrl + Y for Redo but most often you will probably use the icons on the Quick Access toolbar as they are easily accessed.
The other reason why you would probably choose the Undo and Redo via the Quick Access toolbar is because you may notice next to the arched arrows to the left or right, there are two small downward pointing arrows. Selecting these arrows will present you with a list of up to the last 100 actions taken. In other words, Excel will store the last 100 actions taken by the user.
What this means is that if you have performed 101 actions, the first of the 100 actions will be replaced by the 101st. To see what how the very handy Undo/Redo feature works, close the Workbook you currently have open and say No to saving any changes.
1. Now open a new workbook. The reason we are doing this in a new Workbook will become apparent soon.
2. In the new Workbook type the numbers 1-10 in cells A1:A10. Do not use the Fill Handle as this will defeat the purpose of the exercise.
3. Now go up to the downward pointing arrow to the right of the Undo icon and left click on it and you should see your last 10 actions. Take note of the fact that you have Typing 1 in A1 as the action at the bottom of the list.
To Undo the last action, you simply click the Undo icon.
4. Click this now and the number 10 should disappear.
5. If you now click on the drop arrow to the right of the Redo action, you should see that the Typing 10 in A10 is now a Redo action as opposed to an Undo action.
6. Now go back to the downward pointing arrow to the right of the Undo action and hover your mouse pointer over the top action, ie; Typing 9 in A9.
7. Without clicking, drag your mouse down slowly and you will see Excel highlight each action as you go. This allows us to select the number of actions to Undo. When you have reached the action at which you wish to stop, simply stop dragging and click on the last highlighted action (or push Enter) and Excel will Undo all the highlighted actions.
8. If you now click on the downward pointing arrow of the Redo feature, you will see the list of all the actions that we have just Undone.
9. So again, select these actions and click (or push Enter) and you should be back to exactly where we started.
If you had the numbers 1 to 101 in cells A1:A101 and then selected the downward pointing arrow to the right of the Undo icon and left clicked on you will notice that Typing 1 in A1 is not there and at the top of your list should now be Typing 101 in A101. In other words, Excel has replaced your very first action taken with the last action taken. This will continue on as such at any time you are working in Excel.
The most important point to remember here is that Excel 2007 will store the last 100 actions taken. If you are using a previous version of Excel, the Undo/Redo feature stores only the last 16 actions taken.
The other very important point to note of the Undo and Redo feature, is that as soon as you Save, all of the Undo or Redo actions are wiped and there is no way to get them back.
When working in Excel there are times when you may wish to copy the formatting of a cell or range of cells to another location without copying the content of the cell(s). To make this simple, Excel has what is known as the Format Painter. This can only be found under Clipboard options on the Home tab and is represented by a paintbrush. To be honest this is much the same as using the Paste special and selecting Formats as the option. The big advantage is that its much easier and quicker to access and the copy and paste range do not have to be of the same shape and size. So you can get a feel for this feature try these two simple exercises.
1. Type any number in cells A1:A5.
2. Type any numbers in cells B1:B10.
3. Now highlight (select) cells A1:A5 and click the $ (dollar icon) under Number options on the Home tab.
4. Now click the B (Bold) and I (Italic) icons, under Font options on the Home tab.
5. Click the Format Painter icon and then highlight cells B1:B10.
You should now have painted the formatting we applied to cells A1:A5 to B1:B10. You would also have noticed there was a paintbrush symbol next to our mouse pointer, which disappeared once we had selected cell B1.
Now using the Undo feature undo steps 3 to 5 so that all we have is the unformatted numbers in A1:A5 and B1:B10.
Now again apply the same formatting to these cells; eg. $, bold and italics, A1:A5. With cells A1:A5 selected, this time double click the Format Painter
Highlight B1:B5 and the formatting should be painted to cells B1:B5. The difference this time is that the paintbrush symbol should still be present. This is because we double clicked the Format Painter instead of single clicking it
Now highlight cells B6:B10 and cells B6:B10 will also have the formatting applied.
To clear the Format Painter, either single click the Format Painter icon or push Esc on the keyboard.
As you can see a single click will apply the Format Painter once, while a double click will keep the Format Painter active until we either click the icon again or push Esc.
We mentioned above that the copy and paste range do not need to be of the same shape and size when using the Format Painter, to see this follow these steps.
1. Type any numbers in cells C1:G1.
2. Select cell A1 and bold it, then single click the Format Painter.
3. Now select cell C1 and with the left mouse button still held down drag to cell G1 and then release.
You should now have the same formatting applied to cells C1:G1 as you have in A1.
So as you can see the copy and paste range does not need to be of the same shape and size, unlike the Paste special.
This should give you enough to practice with for a while and we mention above, have a play about with the Format Cells dialogue box. Do take note of any questions you may have concerning it or any other feature of Excel and direct them to us and we will answer them.
The next lesson we will be starting to head into the best part of Excel, which is numbers and formulas.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.