Change Cell Values Using Paste Special
Q. I have a price list stored in a worksheet, and I need to increase all prices by 5 percent. Can I do this without reentering all the prices?
Excel provides two ways to accomplish this. The "traditional" technique goes something like this:
- Insert or find a blank column near the prices.
- In that column's first cell, enter a formula to multiply the price in that row by 1.05.
- Copy the formula down the column.
- Select and copy the entire column of formulas
- Select the original prices, and choose Edit, Paste Special.
- In the Paste Special dialog box, select Values to overwrite the original prices with the formulas' results.
- And finally, delete the column of formulas.
The other, more efficient approach also uses the Paste Special dialog box. To increase a range of values (prices, in this example) by 5 percent:
- Enter 1.05 into any blank cell.
- Select the cell and choose Edit, Copy.
- Select the range of values and choose Edit, Paste Special.
- Choose the Multiply option and click OK.
- Delete the cell that contains the 1.05.
Search for Tips
Browse Tips by Category
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks