Number Format Macro

Excel users often spend a bunch of time applying the same format to number cells. Some numbers are stored values and others are calculated with formulas. The task of manually applying the same format to all of the numbers in the worksheet, especially when the numbers aren’t in a continuous range, can feel quite repetitive. And when there are lots of numbers, this task can take time. Since our objective is to get our work done quickly and move on with our life, this post will show you how to set up a macro to format all of the stored and calculated numbers in a worksheet. Thanks Amanda for posting the question for this topic!

Objective

Before we get too far, let’s confirm our goal. Let’s say we have finished getting our worksheet set up, and now just need to apply a format to the cells containing numbers. We need to include cells that store numbers and those with formulas that return numbers. We don’t want to change the format of any other cells. Rather than do all of this formatting manually, we’ll create a macro to help.

Recording the Macro

We can record the basic macro, and then tweak it as needed. Here are the steps we’ll take:

• Start recorder
• Select A1
• Select stored numbers
• Apply desired format
• Select A1
• Select calculated numbers
• Apply desired format
• Select A1
• Stop recorder

You can record the macro using any worksheet, so, create a new worksheet or use an existing one. Be sure that this worksheet includes both stored numbers and calculated numbers. After the macro is recorded, you’ll be able to run it on any other worksheet.

Start recorder: Begin by turning on the macro recorder as follows:

• View > Macros > Record Macro

Give the macro a descriptive name, without using spaces or funky characters. Something like NumberFormat would be just fine.

Store the macro where you’d like and make a note of the location so you can edit it later. We’ve discussed the Personal Macro Workbook and the Macro Toolbox idea previously, so, rather than walk through all of those details again here, I’ve posted the links below.

Select A1:  We want the macro to operate on all cells in the worksheet, regardless of the range that is currently selected. So, begin by selecting cell A1.

Select stored numbers: To select all cells in the worksheet that contain a stored number, we use the Go To Special command (Home > Find & Select > Go To Special). In the resulting Go To Special dialog box, we select Constants and then check the Numbers box, as shown below.

Hit OK and now you should see that Excel has selected the cells that contain stored numbers.

Apply desired format: Now, simply apply the desired format to the selected cells. You can do this using the Ribbon commands or the Format Cells dialog (Ctrl+1).

Select A1: Now, we need to repeat the process again for formulas, so, we’ll once again select cell A1 in order to ensure the macro continues to operate on the entire worksheet.

Select calculated numbers: To select all cells in the worksheet that contain calculated numbers, we’ll once again open the Go To Special dialog. This time, we’ll select Formulas and then check the Numbers checkbox, as shown below.

Hit OK and Excel selects any cells that have a formula that returns a number.

Apply desired format: Apply the desired format.

Select A1: Select A1 just to end up at the top of the worksheet.

Stop recorder:  Now, stop the recorder:

• View > Macros > Stop Recording

OK, we are just about done. Our macro should work well, but, will generate an error message if the worksheet doesn’t contain stored and calculated numbers. So, we can edit the macro to hide the error message.

Edit the Macro

We can edit the macro by using the following command:

• View > Macros

Select the macro in the resulting Macro dialog box and click the Edit button.

Note: if you saved the macro to the Personal Macro Workbook you’ll need to unhide it first with the View > Unhide command.

Currently, the macro looks something like this:

Sub NumberFormat()
  Range("A1").Select
  Selection.SpecialCells(xlCellTypeConstants, 1).Select
  Selection.NumberFormat = "#,###"
  Range("A1").Select
  Selection.SpecialCells(xlCellTypeFormulas, 1).Select
  Selection.NumberFormat = "#,###"
  Range("A1").Select
 End Sub

We don’t want Excel to display an alert if stored or calculated numbers aren’t found in the worksheet, so, we’ll tell it to keep going. We do this by adding the On Error Resume Next line at the beginning. The updated version of our macro appears below.

Sub NumberFormat()
  On Error Resume Next
  Range("A1").Select
  Selection.SpecialCells(xlCellTypeConstants, 1).Select
  Selection.NumberFormat = "#,###"
  Range("A1").Select
  Selection.SpecialCells(xlCellTypeFormulas, 1).Select
  Selection.NumberFormat = "#,###"
  Range("A1").Select
End Sub

Now for the big test. We open a worksheet and run the macro. We confirm that the macro applied the desired format to the stored and calculated number cells as desired…sweet!

A note about the range

The macro above begins by selecting A1. This causes the macro to format all numbers on the sheet, regardless of the initially selected range. If you want the macro to only operate within the initially selected range, then, the sample file has a version of the macro that does this. It is called NumberFormatSelection. It is shown below for reference.

Sub NumberFormatSelection()
 On Error Resume Next
 Dim rSelection As Range
 Set rSelection = Selection

 rSelection.Select
 Selection.SpecialCells(xlCellTypeConstants, 1).Select
 Selection.NumberFormat = "#,###"

 rSelection.Select
 Selection.SpecialCells(xlCellTypeFormulas, 1).Select
 Selection.NumberFormat = "#,###"

 rSelection.Select
 Set rSelection = Nothing
End Sub

A note about dates

To Excel, a date value stored in a cell is a number. So, the macro presented above includes stored date cells in the formatting. If your worksheet contains a mix of dates and numbers, and you want to exclude dates from the formatting, then the sample Excel file includes a version of the macro that will exclude dates. It is called NumberFormatNoDates, and is shown below for reference.

Sub NumberFormatNoDates()
  On Error Resume Next
  
  Range("A1").Select
  Selection.SpecialCells(xlCellTypeConstants, 1).Select
  For Each c In Selection
    If Not (IsDate(c)) Then c.NumberFormat = "#,###"
  Next c
  
  Range("A1").Select
  Selection.SpecialCells(xlCellTypeFormulas, 1).Select
  For Each c In Selection
    If Not (IsDate(c)) Then c.NumberFormat = "#,###"
  Next c
  
  Range("A1").Select
End Sub

Related Posts

There are several ways to store and launch the macro, and rather than repeat the details again here, I’ll simply refer you to the related posts that contain the details.

• Personal Macro Workbook and QAT: https://www.excel-university.com/subtotal-macro/
• Macro Toolbox: https://www.excel-university.com/macro-toolbox/

Resources

Excel file download: FormatNumberMacro