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: For other ways 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.

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

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.

Macro: Add Specific Number to Cells

This macro adds a specific number to all the selected cells. In this example, the Num variable is set to 7, to make it easy to add one week to cells that contain dates.

NOTE: Make a backup of your file, before using this macro.

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

Add Number to Cells With VBA

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

NOTE: 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 the default, if you usually add a different number.

IMPORTANT: Make a backup of your file, before using this macro.

Video: Add Number With Macro or Paste Special

To see the steps for using Paste Special, and the steps for modifying the Add Numbers macros, watch this video tutorial.

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