Advanced Hack: Add References to Cells in Bulk

There are times when you want a set of cells to reference a parameter. For example, you’re budgeting and the result needs to be dynamic. You can easily set this up when cells contain relative formulas. In the top cell you then add the reference to the parameter and you copy this formula down. So far this is easy.

But what can you do when your cells contain different formulas or hard values. And when on top of that the amount of cells that need the parameter reference is too big to change by hand?

By luck I discovered a method to add cell references in bulk to formulas. You might know the Multiply feature that appears in the Paste Special menu. With this paste special option you can multiply one or multiple cells by the number copied. I used to think this would multiply the selected cells by the copied value. And that the result can only be a pasted value. But I was wrong. I will first show how the Multiply Operation in the Paste Special menu works and then reveal the trick to easily add a parameter to a set of cells.

How does the Multiply Paste Special operation work?

The Paste Special Multiply Operation works as follows. You copy the cell that contains the number you want to multiply other cells with. After copying this cell, select the destination cells with your mouse. Then open the Paste Special menu and perform the multiplication.

Note: After copy the cell and making the selection, you can open the paste special menu by:

• pressing Ctrl + Alt + V
• right clicking the selection -> click Paste Special
• in the home tab, under the Paste operation click the arrow with more options -> choose Paste Special.

With the Paste Special menu open, select Multiply and leave all other settings. Press OK and voila. All selected numbers are now multiplied by the copied value.

How to Add Cell References

We just learned how to use the Paste Special Multiply operation to multiply cells by a value. Using a similar approach you can multiply cells by a formula. On the worksheet is a cell to reference. Let’s call this the Parameter. Imagine you want to multiply your Base Values by the Parameter value (and reference it). To do this we first need a cell that references the parameter. In below example it’s called the Parameter Reference. This cell references the parameter directly, and locks both the row and column (using $-signs).

To add a Reference to the Parameter:

• Copy the Parameter Reference
• Then select the Base Values
• Open the Paste Special screen -> paste Formulas -> select the Multiply Operation
• Now press OK

And just like that, all the Base Values now have a reference to the Parameter! The amazing part is, the operation makes good use of parenthesis. After doing the operation the calculations are still correct.

Conclusion

In this post I showed the Paste Special Multiplication operation. In a similar way you can use the Add, Subtract and Divide operation that you find in the Paste Special Dialog box.

With the technique shown, you are now well equipped to add cell references to cells. Even when these cells have different formulas or hard values. Has this helped you? Or do you use use a similar technique in other useful ways? I’d love to hear from you in the comments!