How to save and apply custom number formats in Excel?

You can save and apply a custom number format within one workbook easily as follows:

Save custom number formats in one workbook

Step 1: Select a number cell, right click and select Format Cells from the right-clicking menu.

Step 2: In the popping Format Cells dialog box, go to Number tab, click to highlight the Custom in the Category box, type the custom format code into the Type box, and click the OK button. See below screen shot:

In our case, we type the format code $#.##,," M"; into the type box which will format numbers as currency and show in million.

So far the custom format code is saved in current workbook. And you can apply this custom format to any cell of any worksheet in this workbook.

Apply saved custom formats to other ranges in this workbook

When you want to apply this custom number format to other ranges in future, you can right click the range and select Format Cells from the right-clicking menu.

Then in the opening Format Cells dialog box, go to Number tab, click to highlight the Custom in the Category box, and then click to highlight the custom format code we created above in the Type box (in our case please click to highlight the $#.##,," M"; ), and click the OK button.

Then you can see all numbers in the selected range are formatted to the custom format at once.

The first method can only save custom number formats in one workbook. If you would like to save and apply custom number formats in all workbooks, you can try Kutools for Excel’s AutoText utility.

Save a custom number format into Auto Text pane

Step 1: Select a number cell, right click and select Format Cells from the right-clicking menu.

Step 2: In the opening Format Cells dialog box, go to Number tab, click to highlight the Custom in the Category box, type the custom format code into the Type box, and click the OK button. See below screen shot:

Step 3: Keep selecting the formatted cell, click the  button at the far left of Navigation Pane, and then click the Add button  as below screen shot shown:

Step 4: In the popping New AutoText dialog box, type a name for the new entry in the Name box, specify a group from Group drop down list, and click the Add button.

Now the number with custom number format is added as Auto Text entry, and you can insert it into any workbook at any time.

Apply custom formats to ranges in any workbook

For example you want to apply the custom number format to another workbook, you can do as below:

Step 1: Activate the AutoText pane with clicking the  button in Navigation Pane, and click the entry with custom number format to insert it into the active cell. See screen shot:

Step 2: Go to the Clipboard group on the Home tab, click the Format Painter  to copy the custom number format, and then apply the format to specified range by selecting the range.

Then the specified range is formatted with custom number format immediately.

How to copy formulas from one workbook to another without link?