Automatically Adding 20% to an Entry

Julie would like to have some input cells on a worksheet that, when someone enters a value, automatically add 20% to whatever was entered. For instance, if someone enters 200 into one of these cells, what is actually entered is 240.

There are a multitude of ways you can approach this task using macros. The best approach is to create a macro that is run, automatically, when a cell is changed in the worksheet. You can then check to see if the change was made in one of the input cells and adjust values accordingly. The following example modifies the entered value if it was made in any of three cells: A1, C3, or B8.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Change the input cell range as desired
    If Not Intersect(Target, Range("A1, C3, B8") Is Nothing Then
        If IsNumeric(Target) Then
            Application.EnableEvents = False
            Target = Target * 1.2
            Application.EnableEvents = True
        End If
    End If
End Sub

Remember that this is an event handler, which means it is triggered (in this case) whenever something changes in the worksheet. In order to use this macro, right-click on the worksheet tab and choose View Code from the resulting Context menu. Excel displays the VB Editor and you can then add the Worksheet_Change code.

Note that the key to figuring out whether the change was done in one of the three defined input cells is the Intersect function. It checks to see if there is an intersection between the Target range (the changed cells that triggered the Worksheet_Change handler) and the your input cells (specified using the Range function). If there is, and if the Target cell contains a numeric value, the macro multiplies the value in Target by 120%. (Multiplying by 120% is the same as increasing the value by 20%.)

Note that the .EnableEvents property is set to False when the multiplication is done; if this safeguard were not taken, then each multiplication would trigger this event handler again and you would repeatedly (and forever) multiply the cell value by 120%.

If you wanted to actually do some other processing to the values—such as rounding to a specific number of decimal points or to an integer value—then you simply need to make a change to the single line that actually does the multiplication.

If your input cells are in a contiguous area, a better approach might be to define those input cells as a named range, and then use that named range within the macro to determine the intersection of the changed cells. In that way you don't need to modify the macro when or if your group of input cells change.

To use this approach, let's say that your range of input cells is B7:B19. Select those cells and then, using the name box at the upper-left corner of the cells area, enter the name "plus20pct". This action assigns the name to the range. You can then use that name within the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
    ' Change the input cell range as desired
    If Not Intersect(Target, Range("plus20pct")) Is Nothing Then
        If IsNumeric(Target) Then
            Application.EnableEvents = False
            Target = Target * 1.2
            Application.EnableEvents = True
        End If
    End If
End Sub

Note that the only change is in the way that the intersection of cells is determined—the Intersect function uses the "plus20pct" range as a parameter. Everything else works as before.

Now that you've seen how to do this using macros, the question remains as to whether you really should do it using macros. First, there are limits to what these macros can do. For instance, what if your user enters a date or time into one of the input cells? Internally, Excel handles dates and times as numbers, meaning that they, too, would be increased by 20%.

Second, you need to consider what happens to your worksheet if someone modifies the worksheet structure by adding or deleting rows or columns. The macros are using either absolute cell references (A1, C3, and B8) or a named range (plus20pct). While the named range may be adjusted by row or column addition or deletion, the absolute cells references would not change. Thus, you could end up with the macro checking (and adjusting) cells that are no longer the expected data entry cells.

Third, let's say that someone enters a value (200) into one of your input cells. It is automatically increased by 20% and becomes 240. The person sees this change and wondered what happened, so they select the cell and press F2 to start to edit the cell. Before they make the change, they remember that "Oh, yeah; it is supposed to increase by 20% automatically." So, they simply press Enter to accept the 240 value. However, Excel sees this as a change and increases the 240 by 20%, resulting in 288—not what you or the user intended.

This second consideration—user confusion—is the biggest potential problem with automatically changing what a user enters into the worksheet. A less confusing approach would be to have a well-defined input area for your workbook. The user puts figures into the input area and those figures remain as they input them. Then, in other cells or in your formulas, you do the adjustment by 20%.

This design approach (modifying the worksheet design for easy data entry) is potentially less confusing to the user than automatically changing what they entered in a worksheet cell. It also gets rid of a risk that is inherent with any macro-enabled workbook—the user could load the workbook without enabling macros, thereby ensuring that the figures are not adjusted as you intended.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12684) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.