What it does
Uses an auto-complete search box to find any custom number formats you’ve saved to your library.  It displays the date last updated, the number format code, and a preview of the number format code as applied to your active cell’s value.  Once you find the one you want, click Enter to apply it to any selected cells.  You can also save the number format from the active cell to add to the library.

When to use it
When you want to reuse a custom number format that has been saved to your library.

Why to use it
Custom number formats are extremely flexible and helpful, but can be tricky to set just right.  This macro (and the related Bulk Edit Number Formats macro) allow you to easily save your custom number formats to a central library, where they are easy to access, reuse, and share with colleagues.

Default shortcut
None

Other Details

• Category: Formatting / Number Formats
• Difficult: 2/5
• Usage/frequency: 4/5
• Automation factor: 4/5 (estimated 30 seconds saved each time used)
• Type: Shortcut
• Date added: 12/20/2024
• Tags: Number, formats, custom, search, bulk

Related Macros
Number Format Picker
Toggle Number Formats
Number Format Shortcut

Other Articles
None

None

Prerequisites
Add custom number formats to your library using the instructions below.  Alternatively, add them to your library in bulk with the Bulk Edit Number Formats macro (see below for instructions on the bulk edit approach).

Instructions – Search Number Formats
With custom number formats in your library, run the Search Number Formats macro.  It will show you an auto-complete search box, and as you type, it will display custom number formats you can apply in your workbook, displaying the date last updated, the number format code, and a preview of the format applied to your active cell’s value as you browse through the custom number formats matching your search.

To add the custom number format used in the active cell to your library, click the Save Number Format button.  You can remove a record or all records using the buttons on the right side of the search box.  You can also launch the bulk edit form using the bottom button on the right.

Instructions – Bulk Edit Number Formats
To manage your custom number format library, where you can add, edit, delete, review, or share the library, run the Bulk Edit Number Formats macro.  It looks for the Bulk_Edit_Number_Formats sheet within your active workbook.  If not found, it is added.  Any existing custom number formats in your library will be displayed.  Make any edits to the columns described below, then run the Bulk Edit Number Formats macro again to process your contents and save the custom number formats in the sheet to your library.  These are the columns within the Bulk_Edit_Number_Formats sheet to review or fill in:

• Column B – Date Updated (Optional): to document the date you built or last updated a custom number format, enter a date in this column.
• Column C – Label (Required): give each custom number format a unique label to help search for them and tell them apart.
• Column D – Number Format (Required): enter the custom number format code you want to save. To copy some custom number formats you’ve recently used, go to the number format dialog box (Ctrl+1 or right-click > Format Cells or use the number formats area in the ribbon) and click the custom option at the bottom-left.  It will display recently used custom number formats on the right.
• Column E – Preview (Informational): this column shows you a preview of the custom number format, as applied to the value 12345.67.  To refresh the previews for any changes you make, run the Preview Bulk Number Formats macro.

TBD…