One time-consuming, error-prone technique commonly repeated is scaling numbers in Excel using formulas rather than number formats. For example, to scale a number by thousands, people use formulas such as =A1*.001 or =A1/1000. The problem with this practice is that it is time-consuming to setup or modify and it tends to be error-prone if the cell is involved in a chain of calculations.
A much simpler approach is to use custom number formats. For example, it is possible to display a number scaled by thousands using this number format:
#,##0,
Using custom number formats does not change the underlying value, and the formats are trivial to modify.
To take this a step further, it is relatively simple to add a scaling feature using a combination of named ranges, a simple drop-down list using the data validation feature, and a pinch of VBA. As an example, consider the worksheet shown in Figure 1. (NOTE: Although these images show Excel 2007, the same results can be achieved using Excel 2003.)
Figure 1: Step 1 - Name the range of numbers that should be scaled.
The first step is to create a named range consisting of all the cells that should be scaled. In Figure 1, I’ve named the range Scale_Range.
Figure 2: Step 2 - Create a named range for the cell that specifies the scaling factor.
The next step is to create a named range for the cell that contains the scaling factor options. In Figure 2, I’ve named this Scale_Factor. Additionally, I’ve added data validation to this cell to provide the list of options and the drop-down functionality.
The final step is to add the code shown in Listing 1 to the Sheet1 object in the Visual Basic Editor.
Listing 1: A method for providing scaling capabilities
Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Me.Range("Scale_Factor") Then
ScaleNumbers
End If
End Sub
Private Sub ScaleNumbers()
Select Case Me.Range("Scale_Factor").Value
Case Is = "(in $)"
Me.Range("Scale_Range").NumberFormat = "#,##0"
Case Is = "(in thousands of $)"
Me.Range("Scale_Range").NumberFormat = "#,##0,"
Case Is = "(in millions of $)"
Me.Range("Scale_Range").NumberFormat = "#,##0,,"
Case Else
Me.Range("Scale_Range").NumberFormat = "#,##0"
End Select
End Sub
The Worksheet_Change procedure is an event handler that Excel calls automatically anytime something changes on Sheet1. The Target parameter represents the range object (i.e., cell) that changed. If the cell that changed is the cell containing the scaling choices, the ScaleNumbers procedure is called.
The ScaleNumbers procedure examines the value of the Scale_Factor named range and — depending on the desired scaling — uses the NumberFormat property to set a custom number format.
As you can see in Figure 3, the number appears scaled on the worksheet but the underlying value of the cell is unchanged.
Figure 3