CUSTOM FORMATS IN EXCEL
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 try this:
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.
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.
1. Let us now push the space bar and use a space as our date separator, or if you like, use two or more spaces.
2. Now type a single d and you should see December 12 in your Sample: box.
3. 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.
4. Push the d again, so that you have ddd and you should see the abbreviated day Sun.
5. Push the d one more time, and you will see the full weekday Sunday.
6. So from this, we can safely assume that the 12th of December in the year 2004 is a Sunday.
7. Again, push the space bar and then push the y and you should see 04.
8. Type yy so you now have yyy, you now have the full year – 2004.
9. 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.
10. For this example, we won’t use anything, so push the m so that you have hm together.
11. 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.
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.
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. 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.
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 Formatting Toolbar. Remember to wave your mouse over each icon for a short description of what it does.
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Go back to:
|Lesson 1 - Excel Fundamentals|
|Lesson 2 - Starting Excel and Excel Workbooks|
|Lesson 3 - Excel Toolbars and Task Panes|
|Lesson 4 - Excel Worksheets|
|Lesson 5 - Excel Cells and Navigating a Worksheet|
|Lesson 6 - Excel Cut/Copying and Pasting Data|
|Lesson 7 - Excel Copying with the Fill Handle|
|Lesson 8 - Excel Paste Special|
|Lesson 9 - Excel Insert Command|
|Lesson 10 - Excel's default options|
|Lesson 11 - Excel's Undo and Redo|
|Lesson 12 - Excel's Format Painter|
|Lesson 13 - Excel's Dates and Times|
|Lesson 15 - Excel Formulas|
|Lesson 16 - Excel Cell References|
|Lesson 17 - Excel: Avoid Typing|
|Lesson 18 - Excel Formulae Arguments & Syntax|
|Lesson 19 - Excel Autosum Formula|
|Lesson 20 - Excel Auto Calculate|
|Lesson 21 - Excel's Insert Function|
|Lesson 22 - Excel's Useful Functions|
|Lesson 23 - Excel's Named Ranges|
|Lesson 24 - Excel's Constants and the Paste Name Dialog|
|Lesson 25 - Excel's Calculations|
|Lesson 26 - Excel Comments Cell|
|Lesson 27 - Excel Find and Replace|
|Lesson - 28 - Clear Excel Cell Contents|
|Lesson 29 - Effective Excel Printing 1|
|Lesson 30 - Effective Excel Printing 2|
|Lesson 31 - Sorting in Excel|
|Lesson 32 - Hide/Show Row/Columns in Excel|
|Lesson 33 - Auto-Formats in Excel|
|Lesson 34 - Creating a Basic Excel Spreadsheet|
|Lesson 35 - Excel Charting Lesson: The Basic Excel Spreadsheet|
|Lesson 36 - Excel Worksheet Protection|
|Lesson 37 - Excel IF Formula Nesting|
|Lesson 38 - Excel Function Now/Today Formulas|