Mastering Value Manipulation: Adding and Multiplying Data Using Excel's Paste Special Feature

Mastering Value Manipulation: Adding and Multiplying Data Using Excel’s Paste Special Feature

Quick Links

You might copy and paste data often in your spreadsheets. But did you know that you can use Excel’s paste feature to perform simple calculations ? You can add, subtract, multiply, or divide in a few clicks with paste special in Excel.

Maybe you have prices that you want to increase or costs that you want to decrease by dollar amounts. Or, perhaps you have inventory that you want to increase or decrease by unit amounts. You can perform these types of calculations quickly on a large number of cells simultaneously with Excel’s paste special operations.

Access Paste Special in Excel

For each of the aforementioned calculations, you’ll open the Paste Special dialog box in Excel. So, you’ll start by copying the data and then selecting the cell(s) that you’re pasting to.

To copy, you can press Ctrl+C or right-click and select “Copy.”

Then, do one of the following to access paste special.

Click the Paste drop-down arrow in the ribbon on the Home tab. Select “Paste Special.”
Right-click the cells that you’re pasting to and select “Paste Special” in the shortcut menu.

Add, Subtract, Multiply, or Divide Using Paste Special

For basic numbers, decimals, and currency, you can choose from All, Values, or Values and Number Formats in the Paste section of the window. Use the best option for your particular data and formatting .

You’ll then use the section labeled Operation to add, subtract, multiply, or divide.

Let’s look at a simple example of each operation to see how it all works.

Add and Subtract with Paste Special

For this example, we want to add $100 to each of the amounts for our jacket prices to accommodate an increase.

We enter $100 into a cell outside of our data set and copy it. You might already have the data in your sheet or workbook that you need to copy.

Then, we select the cells that we want to add $100 to and access Paste Special as described. We choose “Add” in the Operation section and click “OK.”

And there we go! All of the cells in our selection have increased by $100.

Subtracting an amount works the same way.

Copy the cell containing the amount or number that you want to subtract. For this example, we’re reducing the prices of our jackets by $50.

Select the cells to paste to and open Paste Special. Choose “Subtract” and click “OK.”

Just like with addition, the amount is subtracted from our cells, and we can move on to selling more jackets.

Multiply and Divide with Paste Special

For the multiplication and division examples, we’re going to use numbers instead of currency. Here, we want to quadruple the amount of inventory for our jackets because we received a shipment.

We enter the number 4 into a cell outside of the data set and copy it. Again, you might have the data that you need to copy elsewhere already.

Select the cells to paste to, open Paste Special, select “Multiply,” and click “OK.”

And there we have it! We just quadrupled our numbers in one fell swoop.

For our final example, we need to divide our inventory numbers in half due to missing merchandise. Copy the cell containing the number or amount to divide by. For us, the number is 2.

Select the cells to paste to, open Paste Special, select “Divide,” and click “OK.”

And just like that, our inventory has decreased by half.

Be sure to switch the operation back to “None” when you’re finished.

The next time you need to add, subtract, multiply, or divide a large group of cells in Excel by the same amount, remember this trick using paste special. And for related help, see how to copy and paste in Outlook with attention to formatting .

Win Solutions