Contextures

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!

paste special command

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.

  • This technique changes the values in the selected cells
  • The original values are replaced by the new values

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

  • In an empty cell on the worksheet, type the number 10 -- that's the amount we want to add to each cell
  • Select that cell, and copy it -- use keyboard shortcut Ctrl + C
  • Next, select cells where you want to add that amount -- cells A2:B6
  • Next, use Ribbon commands or keyboard shortcut to open the Paste Special dialog box:
    • Ribbon Steps:
      • On the Ribbon, click the Home tab
      • At the left end of the tab, in the Clipboard group, click the arrow below the Paste button, to see all of the paste options
      • At the bottom of the paste options, click Paste Special
    • Keyboard shortcut keys -- Ctrl+Alt+V

paste special command

Paste Special Dialog Box

In the Paste Special dialog box, follow these steps:

  • In the Paste section, at the top left, select All or Values
    • In the screen shot below, the All option is selected
    • If the copied cell has any formatting, or comments, etc., those would be pasted too
  • Below that, in the Operation section, click the Add option
  • To apply the paste special settings, click the OK button (or press Enter)

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

paste special command

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:

  1. Macro 1 adds a specific number to selected cells.
  2. Macro 2 prompts you to enter a number, then adds that amount to selected cells.

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.

Video Timeline

  • 00:00 Introduction
  • 00:33 Manually Add Number
  • 01:23 Macro 1: Add Specific Number
  • 02:07 Macro 2: Prompt for Number
  • 02:44 See the Macro 2 Code
  • 03:22 Modify the Macro Code
  • 04:14 See the Macro 1 Code
  • 04:34 Macro Buttons
  • 04:52 Get the Sample File

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.

  • So in a blank cell, I'll type the number 10 and press enter, and then I'll copy that cell.
  • So I've selected it. And on the Home tab, I'll click Copy.
  • Then I'll select all the cells where I want to add that number.
  • Now, click this arrow below the Paste button and go to Paste Special
  • Under Operation, click Add, and OK.

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.

  • So if I select all of these days right now, it's from the first to the 7th of July
  • Click Add 7, and it just adds seven to every date.
  • So you've automatically moved everything to next week.

It will also add 7 to numbers.

  • So if I select these cells and click Add 7, it does it very quickly.
  • There's no cell where you have to go and type a number and then clear it out later

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.

  • So here, if I select all of these cells and I'd like to add a hundred to them, I click this button.
  • The input box appears and asks me which number I'd like to add.
  • The default I've got set up is seven because usually I'm working with dates and I want to add a week,
  • But I can type any number here. So I'll type 100, click OK.
  • And it changed us those cells, but left everything else as is

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 to see one, I'll right- click on this, click Assign Macro, and it shows the macros in this workbook
  • And the one that we're using on this button, when I click Edit, it takes me into the code for that button.

So we've got some variables set up

  • The range that it's going to work on is the selection.
    • So only the cells that you have selected will be changed.
  • Then we figure out how many rows and columns are in that selection.

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.

  • There's also a default number 7.
    • So if you usually add 10 to things, you could change that default to 10
    • And then you just have to click OK, or change the number for those times when you want to add a different number
  • Then we create an array that's based on the size of the selection.
  • So going by the number of rows and columns in your selection, it loops through everything.
    • So it loops through each row and each column in that row and picks up the number that's there
    • and adds seven to that, or whatever number you've put in that input box.
  • And then it takes all of those new numbers and puts them into the selected range.
    • So the selected range value becomes that array that we created with the increased numbers.

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.

  • So you could change that if you're not usually working with dates and want to add a week
  • If you like to add a hundred to things or 10.5, whatever number makes sense for you, you can put here as the value for that variable.

Macro Buttons

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.

paste special command

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.).

paste special command

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.

  • You can change that default number, if you usually add a different number. Look for the following line in the macro code, and change the number there:
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.

More Tutorials

Increase Numbers With Paste Special

AutoFill Examples

Get Started With VBA

Copy VBA Code to Your Workbook

 

 

Last updated: March 16, 2023 2:57 PM