This post will guide you how to change values in a data range with Excel’s Paste Special feature quickly in Excel 2013/2016. How do I change values in a selected range of cells in Excel. How to add, subtract, Multiply or divide the numbers in a selected range in Excel.

1. Change Cell Values in a Range using Paste Special Feature

Assuming that you have a list of data that contain numeric values in range A1:B4, and you want to update all numbers on your selected range of cells, such as: add number 5 or subtract 5 or multiply 5 for all values. You can easily to update all numeric values on the current worksheet at once by using Paste Special feature on the Excel Ribbon. You just need to type a value in a blank cell, and then copy that cell and apply its value to other cells. Just do the following steps:

Step1: enter a number that you want to use in a blank cell of your spreadsheet. Then select that cell and click Copy or press Ctrl + c keys to copy it.

Step2: select the range of cells that contain numeric values to be updated. (Add, Subtract, Multiply, Divide)

Step3: right click on it, and choose Paste Special from the dropdown menu list. And the Paste Special dialog box will open.

Step4: select Add or others operations under Operation section. Click Ok button to apply those changes.

Step5: you should see that all selected cells will be added number 5.

2. Change Values in a Range quickly using VBA Code

we’ll delve into the power of VBA (Visual Basic for Applications) code to efficiently change values in a range. This method adds a layer of automation, allowing for more complex operations and customization in modifying your Excel data.”

Press ‘Alt + F11‘ to open the Visual Basic for Applications editor.

In the editor, click ‘Insert‘ and choose ‘Module‘ to create a new module.

Copy and paste the following VBA code into the module:

Sub ChangeValues()
    Dim rng As Range
    Dim operation As String
    Dim sourceRange As Range
    Dim operationNumber As Double
    
    ' Prompt user for operation choice
    operation = InputBox("Enter the operation (add, subtract, multiply, divide):")
    
    ' Prompt user to enter the operation number
    On Error Resume Next
    operationNumber = InputBox("Enter the operation number:")
    On Error GoTo 0
    
    ' Prompt user to select source data
    On Error Resume Next
    Set sourceRange = Application.InputBox("Select the source data range:", Type:=8)
    On Error GoTo 0
    
    ' Exit if user cancels operation, operation number, or source data selection
    If operation = "" Or sourceRange Is Nothing Or operationNumber = 0 Then
        MsgBox "Operation, operation number, or source data selection canceled. Macro aborted."
        Exit Sub
    End If
    
    ' Perform the operation on the selected range
    For Each rng In sourceRange
        Select Case LCase(operation)
            Case "add"
                rng.Value = rng.Value + operationNumber  ' Modify this line for different operations
            Case "subtract"
                rng.Value = rng.Value - operationNumber
            Case "multiply"
                rng.Value = rng.Value * operationNumber
            Case "divide"
                rng.Value = rng.Value / operationNumber
            Case Else
                MsgBox "Invalid operation entered. Please choose add, subtract, multiply, or divide."
                Exit Sub
        End Select
    Next rng
End Sub

Close the editor, press ‘Alt + F8‘, select ‘ChangeValues‘

and click ‘Run‘

Enter the desired operation (add, subtract, multiply, divide) when prompted.

Click Ok button. Enter the operation number when prompted.

Click Ok button. Select the source data range when prompted.

Click Ok button.Check your Excel sheet to see the values in the specified range modified according to the chosen operation and operation number. Adjust the code as needed for additional operations or specific ranges.

3. Video: Change Values in a Range quickly

This Excel video tutorial, where we’ll explore efficient ways to swiftly change values in a range. Join us as we uncover two methods – one using the ‘Paste Special’ feature and the other leveraging VBA code.

https://youtu.be/D9wuN-2rIWo