Multiply inconsistent cells with a sensitivity percentage
Occasionally you will need to make a quick change to a set of cells. Normally you would create new cells and, for example, add 10% to the sales items below, but we need to quickly do it.
The Paste Special Multiply will allow you to do this.
To follow along go to 15.2
If YouTube doesn't work, click here
As shown below I need all the Sales cells (B2 to B5) to have 10% added to their existing number so that 300 becomes 330, 150 becomes 165 etc. The steps to follow would be:
- Type 1.1 in any cell (B9 below)
- Copy it
- Highlight that cells that you want to change (red box below)
- Go to the HOME tab and click on Paste Special
The normal Paste Special dialogue box will appear as shown below. Most people are familiar with the top part (paste as values etc) but they don’t know what the operations do.
The option we need is multiply.
What this will do is take the cell we have copied (B9) and place it individually into each of the highlighted cells with a multiply in front of it. The net result is that Excel is forced to do the calculation. So where the cell said 300, it now changes to 300*1.1, but it can’t leave this formula in the cell so it completes the calculation hence 330. The benefit is that it does it quickly to all the cells.
You will note that it also takes the format of the copied cell. You can change this by using some of the normal paste special options e.g. values.
Changing a formula with Paste Special
A very useful aspect of the above trick is that you can actually change the formula in a number of cells in one go.
In the example below we have links (B24 to E24) to the inputs cells but they aren’t in order and they are horizontal, not vertical.
We want to multiply each of these cells with the contents of cell B21 to add a sensitivity. The problem is that due to the lack of consistency, we would need to manually go into each cell and type ‘*$B$21’. With only 4 cells this is doable, but what happens if it is 1 000 cells?
We can follow the same steps as with the 1.1 number but there is one thing you need to understand.
In the previous example we typed 1.1 in a cell. In this example we need to find a blank cell and type =$B$21.
As shown below, the cell itself (D21) shows a 1, but the formula shows the formula =$B$21. Most people assume that when you copy, it looks at the cell output (the 1) but it is in fact looking at the formula $B$21.
So if we follow the same steps (copy the cell D21, highlight B24 to E24, click HOME, Paste Special, choose multiply, and click OK)
You will see that added to the previous formula of =B2 is ‘*$B$21’. If we chose Divide it would be =B2/$B$21.
This is a very useful way to add What If capabilities if you have an inconsistent spreadsheet (built by someone else?) but you need to quickly change the formula.