Normally in Excel, if you want to do some calculations on values in a cell or range of cells, then it’s fairly easy to do using a formula. However the way to do it would be to write the formulas into adjacent cells, convert the answers to values and then copy and paste the values to overwrite the cells you want to update.
Well did you know there’s any easier way??
As an example, let’s say that you have a product list from a supplier that has the NET price of all of the products in their catalogue. You need to work out the RRP which is the supplier’s price plus VAT at 20% to add to your own (or your client’s) website.
Here’s how to do it:
- Into any blank cell enter the value 1.20. This is the “multiplier” that will increase the prices by 20%.
- Select the cell containing your multiplier and copy the value to the clipboard by pressing Ctrl-C.
- Select the range of cells that you want to change, in this example it is the values in column B
- We then use the paste special option. Right click and select Paste Special to display the Paste Special dialog box.
- Select ‘Multiply’ and click OK.
And there you have it! Your values are instantly updated. You can then delete the cell containing your multiplier.
Note: this Paste Special dialog box will also perform a addition, subtraction and other mathematical operations so you can use this technique for a variety of other calculations aswell.