EXCEL'S CUSTOM FORMATS

Lesson 14 - Working with Custom Formats in Excel

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:

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 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 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.
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 GeneralFormat.
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.

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.

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. 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