|
Home > Skills > Data Entry > Paste Special Add Number to Range of CellsIn Microsoft Excel, see the easiest way to add a specific amount to the numbers in a range of cells, either manually, or with a macro, no formulas required!
Tip: If you're looking for the steps to get a sum of values, go to the sum function examples page, for written steps, videos and sample files. |
How to Add Amounts to Cell ValuesTo increase the amounts in a range of cells -- adding the same amount to each cell -- you can use see the steps below. There is a simple example shown below, and you can see other ways to change cell values with on the Paste Special command page --add, subtract, multiply, and divide. Increase Cell Values with Paste SpecialIn this example, there are test scores in the worksheet cells A2:B6. All of the scores need to be increased by 10. To increase the amounts by 10, follow these steps
|
Paste Special Dialog BoxIn the Paste Special dialog box, follow these steps:
On the worksheet, all numbers in the selected cells have increased by 10 Then, to clean up the worksheet, clear the cell where you typed the 10
|
Video: Add Number - Macro DemoQuickly add the same amount to values in multiple cells, manually or with a macro. For example, select a group of date cells, and increase by 1 week, by adding 7 to each cell. In this video, you'll see how to add amounts manually, with the Paste Special command. There's also a quick demo of the macros from my sample file:
Tip: For details on changing the macro code, watch the video in the next section, starting at the 2:44 mark |
Video: Add Number - Macro Code DetailsThis video starts with the manual steps, followed by a demo for the 2 macros. Then, starting at the 2:44 mark, you'll see the macro code, and the steps for making minor changes to the macro code, so it fits your requirements. NOTE: The macro code is below the video, as well as the full video transcript. Video Timeline
Video TranscriptIf you'd like to read the video transcript, click on the green check box below. When you're finished, you can click the check box again, to hide the transcript Video Transcript: Add Number to CellsIn Excel, you might have a group of numbers or dates that you want to add a set amount to. For example, you might have a group of numbers and you want to add a hundred to everything or a group of dates, and you want to add a week so that you're moving everything to next week. Manually Add NumberWe're going to look at how you can do that manually. And I'll show you a couple of macros that you can download from my website that will let you click a button and automatically add a number to selected range of cells. So first the manual option is Paste Special. So I've got these cells and I'd like to add 10 to everything on this worksheet.
And now everything has increased by 10. Once I've finished, I can go back and just clear that cell - just select and press the Delete key. Macro 1: Add Specific NumberIf this is something you do frequently, it might be easier to do with macro. So I've got a couple of examples here. In this example, I've got a button that runs a macro, which adds a set number to the selected cells And in the sample file, I've got it set up so it will add 7.
It will also add 7 to numbers.
Macro 2: Prompt for NumberAnd the other macro asks you what number you'd like to add, so it's a little more flexible.
Do the same thing with dates -- I can add seven and it didn't change anything else. See the Macro 2 CodeAnd we'll take a look at those macros.
So we've got some variables set up
Modify the Macro CodeAnd it's going to prompt you to put in a number and there's a string that will show up in that input box.
See the Macro 1 CodeThe other macro is very similar, but it doesn't have an input box, it just has a set number.
Macro ButtonsAnd then these macro buttons, just click them to run the macro. Or you could add those macros to a workbook that you have open all the time, like your personal workbook, and then make an icon up in the quick access toolbar or on the ribbon to run that macro |
Sub AddNumberPrompt()
Dim ws As Worksheet
Dim rngSel As Range
Dim rng As Range
Dim Num As Double
Dim i As Long
Dim j As Long
Dim lAreas As Long
Dim lRows As Long
Dim lCols As Long
Dim Arr() As Variant
Dim strPrompt As String
Set rngSel = Selection
lAreas = rng.Areas.Count
strPrompt = "Enter number to add to selected cells"
On Error Resume Next
Num = InputBox(strPrompt, "Number to Add", 7)
If Num <> 0 Then
For Each rng In rngSel.Areas
If rng.Count = 1 Then
rng = rng + Num
Else
lRows = rng.Rows.Count
lCols = rng.Columns.Count
Arr = rng
For i = 1 To lRows
For j = 1 To lCols
Arr(i, j) = Arr(i, j) + Num
Next j
Next i
rng.Value = Arr
End If
Next rng
End If
End Sub
Get the Sample FileTo get the sample file, click here: addnumbertocells.zip The zipped file is in xlsm format, and contains macros. Enable macros, if you want to test the code in the sample file. NOTE: Make a backup of your file, before using these macros. |
More Tutorials |
Last updated: March 16, 2023 2:57 PM