Home > Skills > Data Entry > Paste Special
Add Number to Range of Cells
In 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.
Add With Paste Special
Most of the time that you create a sum in Excel, you use a SUM formula, or the SUBTOTAL function, to total a a column of numbers, or a row of numbers. Later, if any of those worksheet numbers change, the totals update automatically.
With the technique shown below, you will add a number to the values in one cell, or cell ranges, without using formulas or cell references.
WARNING: This is an efficient way to update numbers quickly, but be careful! Before you use this technique, I recommend that you make a backup copy of your workbook, in case you need to see the original values later.
Video: Add Numbers with Paste Special
To see the the steps for adding numbers with the Paste Special command, watch this short video. It shows a different example of adding numbers to cell values, using Paste Special.
First, the items in a price list are all increased by $1.50. Next, in another copy of the price list, the amounts are increased by 5%, instead of a specific amount.
How to Add Amounts to Cell Values
To 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 Special
In 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 Box
In 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 Demo
Quickly 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 Details
This 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.
If 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 Cells
In 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 Number
We'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 Number
If 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 Number
And 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 Code
And we'll take a look at those macros.
So we've got some variables set up
Modify the Macro Code
And 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 Code
The other macro is very similar, but it doesn't have an input box, it just has a set number.
And 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
Macro 1: Add Specific Number to Cells
This macro adds a specific number to all the selected cells. In the sample file, click teh Add 7 button, to run this macro.
In this example, the Num variable in the ceod is set to 7, to make it easy to add one week to cells that contain dates.
Copy the AddNumber Code
NOTE: Make a backup of your file, before using this macro. Excel wipes out the Undo stack any time that you run any macro that makes changes to the worksheet.
Sub AddNumber() 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 Set rngSel = Selection Num = 7 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 Sub
Macro 2: Add Number to Cells With Prompt
This macro prompts you for a number, then adds that number to all the selected cells.
In the input box, the default number is set as 7, and you can overwrite that with any number (whole or decimal, positive or negative.).
Copy the AddNumberPrompt Code
In the input box, the default number is set as 7, so it's easy to add a week to a group of dates.
Num = InputBox(strPrompt, "Number to Add", 7)
IMPORTANT: Make a backup of your file, before using this macro. Excel wipes out the Undo stack any time that you run any macro that makes changes to the worksheet.
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 File
To 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.
Increase Numbers With Paste Special
Last updated: March 16, 2023 2:57 PM