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.

Editor's Picks