Please Note: This article is written for users of the following Microsoft Excel versions: 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Moving Custom Formats to Number Formatting Categories.
Written by Allen Wyatt (last updated November 5, 2022)
This tip applies to Excel 2000, 2002, and 2003
Neil knows how to create custom formats in Excel. He has created a custom format that displays dates exactly as he wants them displayed and he would like this custom format to show up in the Date category (Number tab of the Format Cells dialog) rather than being left in the Custom category. Neil wonders if there is a way to do this.
The short answer is that no, there is no way to do this. The formats that appear in the non-custom categories are hard-coded by Excel. The only thing you could do to make the application of the custom formats faster (if that is your goal) is to use a macro that applies the format. The following is an example of a macro that applies a custom format to whatever cells are selected:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here:
Sub MyNumberFormat()
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
End Sub
You can assign the macro to a shortcut key or to a button on a toolbar, thereby making it very easy to apply.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8842) applies to Microsoft Excel 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Moving Custom Formats to Number Formatting Categories.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2019 For Dummies today!
It's easy to automatically set the contents of one cell to be equal to another cell. But what if you want to copy the ...
Discover MoreWhen entering data in a worksheet, Excel tries to figure out how your entry can best be shown on the screen. When it ...
Discover MoreExcel allows you to format your numeric values in a wide variety of ways. One such formatting option is to display ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments