Software

Use Paste Special to perform calculations while pasting in Excel

Use Excel's Paste Special feature to calculate multiple values against a data range, at the same time. You can make the process even more efficient with keyboard shortcut combinations.

You probably know that you can add, subtract, multiple, and divide in Microsoft Excel using Paste Special. It's a simple process. Enter the number you want to add, subtract, multiply by or divide by and press [Ctrl]+C to copy that value to the Clipboard. Then, select the values you want to change and choose Paste Special from the Paste option in the Clipboard group. (In Excel 2003, you'd choose Paste Special from the Edit menu.) In the resulting dialog, you click Add and then click OK. Excel adds the value you copied to the Clipboard to every value in the selected range. Using this simple technique you can also subtract, multiple, and divide large ranges of values by the same value.

What you might not know is that you can copy multiple values to the Clipboard. For instance, let's suppose you want to add the values 1, 2, and 3 to all the corresponding values in columns A, B, and C, respectively. Using Paste Special, you can turn a rather tedious task into a few clicks! First, you enter the values you want to copy to the Clipboard, that's values 1, 2, and 3. Then, do the following:

  1. Select the cells you want to copy to the Clipboard (A4:C4) and press [Ctrl]+C. Select the cells you want to change. That's A1..C2.
  2. Click the Home tab and choose Paste Special from the Paste option in the Clipboard group. (In Excel 2003, choose Paste Special from the Edit menu.)
  3. In the resulting Paste Special dialog, select Add.
  4. Click OK and Excel adds 1 to the selected values in column A, 2 to the selected values in column B, and 3 to the selected values in column C.

You can subtract, multiply, and divide using the same technique. Just be sure to specify the appropriate operator in step 3.

Furthermore, Excel doesn't complain when the two range dimensions don't match. For instance, you can copy two rows of values to the Clipboard to alternate the applied values. Or, you can copy four columns of values to the Clipboard even if you're changing only three columns. Excel will simply copy the extra value to the adjacent cell, relative to its position. For instance, if you enter 4 into D4, and select A1:C2, Excel will copy the value 4 to D1 (but not D2).

You shortcut fanatics will be glad to know that there are keyboard shortcuts for launching the Paste Special dialog and selecting the appropriate setting after you copy values to the Clipboard.

Operator

Keystroke Shortcut

Add

[Alt]+E, S, D, [Enter]

Subtract

[Alt]+E, S, S, [Enter]

Multiply

[Alt]+E, S, M, [Enter]

Divide

[Alt]+E, S, I, [Enter]

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
dhays
dhays

I use the Paste-Special to copy comments mostly. However the shortcut I use is a right click on the mouse. PS is among the many options brought up. Of course, one has to remember that Excel will not paste if someother operation has been performed after the copy (from the clipboard yes, but not any other way).

allysonstewart
allysonstewart

Hi, Susan Great explanation about paste special and the keyboard shortcuts. This feature has saved me several times! I love the fact that Excel lets us paste data exactly the way we want on our spreadsheet. It's a hidden gem. Thanks for letting others know about it. Have a good one! Allyson Stewart

zimmerwoman
zimmerwoman

Queries from our acctg system on dept budget status come out with the opposite sign from what is intuitive for program staff(add to budget is credit, subtract from budget is debit). It is easy to pull down the query, paste-special multiply the entire data range by -1 and then create the report with a pivot table. Presto chango.

ssharkins
ssharkins

I like hearing how people actually use features!

jbenton
jbenton

Often, especially when data is pasted from other sources, what should be numbers or dates are stored as text. In order to give the nudge toward the correct format, copy an empty cell and Paste special, Add, Value to the whole area (doesn't affect true text or blanks)

david.hanshumaker
david.hanshumaker

This is quicker and easier than what I do to accomplish the same thing. I enter the value 1 into an empty cell, copy, then paste special, then multiply or divide to a range. This tip skips entering the 1 and deleting it when finished. Good tip.

Editor's Picks