Home > Skills > Data Entry > Paste Special Change Numbers With Paste Special Use the Paste Special command to automatically add, divide, or multiply specific amounts to numbers on a worksheet.

Introduction

Without using formulas, you can add, subtract, multiply or divide all the numbers in a range by a specific amount. For example, in a price list, all the prices could be increased by $1.50, or the prices could be decreased by 5%.

This makes it easy to change the amounts on a worksheet, all at once, by using the Paste Special command on the Excel Ribbon. Just type a value into a cell, then copy that cell, and apply its value to other cells.

NOTE: This technique overwrites the original numbers, so be sure to make a backup copy of your file, before changing the numbers with Paste Special.

Thanks to T. Valko for suggesting this page!

Increase Numbers With Paste Special

Watch this short video to see the steps for adding or multiplying with Paste Special. Written instructions are below the video.

Add Amount to Selected Numbers

You may want to increase all the numbers in a range by a set amount. For example, in this price list, all the prices should be increased by $1.50. The following technique makes it easy to increase the prices, all at once.

1. In a blank cell, enter the amount of the increase. In this example, 1.50 was entered in cell D2

   

2. Copy the cell which contains the increase amount.
3. Select the cells which contain the amounts that you want to increase.
• Here, cells B2:B5 are selected.
4. Right-click one of the selected cells, and click Paste Special
5. Optional - In the Paste section, click Values, if you don't want to copy the formatting from the copied cell (D2)
6. In the Operation section, click Add, then click OK.
   • 

On the worksheet, each of the selected numbers is automatically increased by $1.50

Multiply Numbers by a Percentage

You may want to increase all the numbers in a range by a set percentage. For example, in this price list, all the prices should be increased by 5%. The following technique makes it easy to increase the prices, all at once.

To increase by a percentage, multiply by (1 + the percentage as a decimal).

• In this example, the increase is 5%
• Written as a decimal, 5% is 0.05
• The multiplier is 1+ 0.05, which is 1.05

Follow these steps to increase the current prices:

• In a blank cell, type the amount of the increase.
  • In this example, 1.05 was entered in cell D8
• Copy the cell that contains the increase amount.
• Select the cells that contain the numbers you want to increase.
  • In the screen shot below, cells B8:B11 are selected.

• Right-click on one of the selected cells, and click Paste Special
• In the Paste Special dialog box, in the Paste section, click Values
• In the Operation section, click Multiply
• Click OK, to apply the multiplication to the selected numbers.
• Each of the selected numbers is automatically increased by 5%

Subtract Amount from Numbers

You may want to decrease all the numbers in a range by a set amount. For example, in this price list, all the prices should be reduces by $1.25. The following technique makes it easy to decrease the prices, all at once.

• In a blank cell, enter the amount of the decrease. In this example, 1.25 was entered in cell D3
  • 
• Copy the cell that has the decrease amount.
• Select the cells that have the amounts you want to decrease.
  • Here, cells B2:B5 are selected.
• Right-click one of the selected cells, and click Paste Special
• Optional - In the Paste section, click Values, if you don't want to copy the formatting from the copied cell (D3)
• In the Operation section, click Subtract, then click OK.
  • 

On the worksheet, each of the selected numbers is automatically decreased by $1.25

Divide Numbers With Paste Special Watch this short video to see the steps for dividing numbers with the Paste Special command. Written instructions, and the full video transcript, are below the video.

Divide Numbers With Paste Special

Without using formulas, you can quickly divide a set of numbers, by using the Paste Special command. In this example, numbers are divided by 1000, and the result is a permanent change to the numbers.

1. In a blank cell, enter the amount by which you want to divide. In this example, 1000 was entered in cell F3
2. Copy the cell which contains the division amount.
3. Select the cells which contain the amounts that you want to divide. In the screen shot below, cells C2:C11 are selected.
4. On the Ribbon's Home tab, click the arrow on the Paste button, then click Paste Special

   

5. In the Options section, click Divide, then click OK.
6. Each of the selected numbers is automatically divided by 1000.
7. To complete the change, you can format the numbers, if necessary, and delete the amount typed in cell F3.

Video Transcript - Divide Without Formulas

Back to Video

Here is the full transcript of the video: Divide Excel Numbers Without Formulas.

Introduction

Hey, this is Sarah from Contextures.com.

Today, we're going to take a look at a feature where you can permanently change numbers by adding, subtracting, multiplying, or dividing, without using a formula.

In my table here, you can see I've got some quantities, all in the millions. What I want to do is divide those numbers by 1000.

Type Value on Worksheet

So, the first thing that we're going to do is to add that value, over here in the, in the worksheet. So I'm just going to type 1000 and next I want to copy that value. So I can either do that with Ctrl + C or just head here, can see it's copied.

Use Paste Special

Next step, we'll select those numbers we want to divide by the value, and we're going to head towards the ribbon for the Paste drop down, so we see the Paste special. When we select here, you'll see those operations that I was talking about earlier.

So we have add, subtract, multiply, and divide. Today, we'll be using divide. So we'll click there and OK. And now just like that, you can see those numbers have all been permanently changed to be divided by 1000.

Clean Up the Sheet

A little bit messy, so we're going to clean this up really quickly by just going to the home ribbon, to the number section and selecting Comma style. Here, you can see they've all been aligned and the decimal places are all set to two.

Last step, will just be to delete that 1000, and we're good to go. Just a reminder. This is a permanent change to these values. It's not like a formula where you can adjust it later.

Back to Video

Get the Sample File

• To try these examples, download the Change Excel Numbers with Paste Special sample file. The zipped file is in xlsx format, and does not contain any macros.

More Data Entry Tutorials

Data Entry Tips

Fix Copy & Paste Problems

Fill Blank Cells

Add Number to Multiple Cells

Convert Text to Numbers