Microsoft Excel Logo

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

Right-click the cell and pick Copy

Advertisement

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.

Right-click and pick Paste Special

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.

RELATED: How to Paste Text Without Formatting Almost Anywhere

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

Paste Special options

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.

Copy the cell with the number you want to add

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

Select Add before hitting OK

Advertisement

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

Amount added using Paste Special

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.

Copy the cell with the number you want to subtract

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

Select Subtract before hitting OK

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

Amount subtracted using Paste Special

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.

Copy the cell to multiply

Advertisement

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

Select Multiply

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

Amount multiplied using Paste Special

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.

Copy the cell to divide

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

Select Divide

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

Amount divided using Paste Special

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.

Sandy Writtenhouse Sandy Writtenhouse
With her B.S. in Information Technology, Sandy worked for many years in the IT industry as a Project Manager, Department Manager, and PMO Lead. She learned how technology can enrich both professional and personal lives by using the right tools. And, she has shared those suggestions and how-tos on many websites over time. With thousands of articles under her belt, Sandy strives to help others use technology to their advantage.
Read Full Bio »

The above article may contain affiliate links, which help support How-To Geek.