To sum or average a row or column of values, you enter a SUM() or AVG() function that evaluates the appropriate values. It couldn’t be simpler. However, it’s not quite so easy to change a range of values by the same value. For instance, suppose you want to multiply all of the values in a range by the value 100. You could enter and copy a formula in another column, but that works only if it doesn’t matter where the results are.
If you need to adjust the actual values, to accommodate existing formulas and functions, you might adjust each value manually, which would certainly be tedious and unnecessary. Fortunately, you can use a Paste Special option as follows:
- Select an empty cell (not adjacent to the existing range) and enter the value by which you need to adjust each existing value. For instance, to increase all the reorder values (column I in the worksheet below) by 5, you'd enter the value 5 in a blank cell.
- With that cell (K4) selected, press [Ctrl]+C to copy the value (5) to the Clipboard.
- Select the range you want to adjust, but don’t include a header cell in the selection. For instance, to adjust the reorder values in the previous worksheet, you'd select cells I2:I78.
- From the Edit menu, select Paste Special. (In Excel 2007, click the Home tab, click the Paste tool’s drop-down arrow, and choose Paste Special.)
- Choose Add in the Operation section.
- Click OK and Excel will add 5 to each value in the selection.
You can use this trick to adjust values by adding, subtracting, multiplying, or dividing a range of values by a single value. If you change your mind, simply press [Ctrl]+Z to undo the operation.
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.