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.
by Allen Wyatt
(last updated June 21, 2018)
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:
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.
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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When you add a combo box to a worksheet, Excel makes some assumptions about the best font size to use in the control. ...Discover More
Custom formats are great for defining how a specific value in a cell should look. They aren't that great at doing complex ...Discover More
Sorting ZIP Codes can be painless, provided all the codes are formatted the same. Here's how to do the sorting if you ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.