Excel users often find themselves dragging colored box references when moving a formula in an asymmetric worksheet. The all-too-frustrating reality, however, is that when a formula references the same cell multiple times, you have to drag each reference in the formula.
Unfortunately, there is no shortcut to select all instances of a cell reference within one formula (i.e CNTRL + Select). Instead, there are three alternative solutions: (1) add/delate rows/columns in a copy worksheet, (2) find & replace, and (3) restructure the worksheet as a database.
Click here to download the Excel I use in this article to follow along!
Don’t forget, you can get the free Intro to Data Analysis eBook to ensure strong fundamentals and smooth progression in all your data endeavors.
Add/Delete Cells, Columns, Rows in a Copy Worksheet
After some experimenting, in my opinion this solution has the fewest use cases. In order for it to work, you must be able to delete/add cells, rows, or columns to adjust the reference without impacting other parts of your formula.
Since it requires copying the worksheet, it’s usually not faster than dragging the references one-by-one or using find & replace — unless you only need to adjust the target cell once. I only use this technique when I have a large formula with an isolated target cell to adjust quickly.
The methodology is to copy the relevant worksheet, add and delete arrays (cells, columns, rows) around your target cell to adjust the references as you would like, and copy the new formula into the original worksheet. Let’s look at an example. Imagine you have the following setup:
Your formula consists of a simple nested IF statement that calculates a logic centered around cell C3, whose reference has three iterations. You would like to copy and paste this formula into the cell under the formula for target 2 (cell F15). However, target two is 2 cells below target one in the array. If you were to copy/paste, you would not be aligned to the new target (22).
The temp worksheet modification technique would suggest we create a copy of this sheet and delete the two cells below target 1. The modification step would look like this:
As shown in the video, once you have your new formula, you can copy it into the original worksheet, providing you with this setup in the original worksheet:
In some cases, you can delete entire rows or columns, which would be much faster than individual cells, as shown above. In this example, that would not be possible because we would loose the reference to cell D3 (1709)
The efficiency of this tactic depends on the specific structure of your worksheet and formula. While I don’t think it’s the most effective of the three techniques under most circumstances, the reality is that it all depends on your analysis!
Keep this in mind as part of your toolkit. In a case where the formula has 10 references to the same cell, and simply deleting a row would modify the reference, you’ll be glad you know it.
Find & Replace
This is the most common solution because it is the most intuitive. However, in order for it to be faster than dragging each reference, your formula needs to have at least five references, and you need to use the keyboard shortcuts to find & replace.
I ran a test on multiple formula sizes to be sure when it’s faster to use replace rather than drag the references. In general, once you have to drag more more than five cells, it’s faster to use find & replace. This depends on how fast you are with shortcuts, but for most people, 5 references is the threshold.
So how does it work? You need to access Home > Editing > Find & Select > Replace, the shortcut for which is CTRL + H on windows (control + H on Mac). Simply type in the cell you want to replace and the cell location you want to substitute, then hit “Replace.”
For example, it looks like this on Mac:
Careful Not to Change Desired References
Find & Replace will update the entire worksheet. If you have multiple formulas referencing a same cell, and you only want to move the references in one formula, then you have two options:
- Create a copy of the worksheet, then run the Find & Replace function. You can then copy the new formula to the original worksheet.
- Perform the first formula adjustment by dragging each box, then copy and paste the new formula in relevant cells. This only works if you want to change the references in one repeated structure to a second repeated structure.
Restructure the Worksheet as a Database
A third and final alternative to grab and dragging formula references is probably the most important, but the least convenient. Instead of looking for a shortcut, it’s often more useful to re-think the way your analysis is formatted and try to restructure it to Excel’s nature.
Because Excel is a worksheet program, it gives the user an immense amount of flexibility. At its conception, it was supposed to be a database program, but its flexibility has pushed many users to mold Excel to their thinking patterns. The best way to approach setups in Excel is to return to basics: data tables.
As often as possible, you should try to create data tables in Excel to handle your analyses rather than ambiguous structures like the one we’ve shown in our example. What is a data table? It’s nothing more than a structure where the far left column is the unique ID of the table, and the other columns describe that unique ID.
Using our example, we could adapt our analysis to look like the following:
As you can see, by restructuring the exercise as a database, we can write a formula to be dragged down the table. Instead of just the two output cells, we have an output following the original logic for EACH day cell. This means we could extend the original exercise as far as we want without having to grab and drag any cell references.
The challenging part about this alternative is that it requires a good amount of forethought and good knowledge of Excel formulas. Taking our example, the dragged formula is:
=IF(D20>INDEX($C$20:$D$29,(MATCH(CEILING(C20,5),$C$20:$C$29,0)),2),VLOOKUP(CEILING(C20,5)-4,$C$20:$E$29,3,FALSE),IF(D20<INDEX($C$20:$D$29,(MATCH(CEILING(C20,5)-1,$C$20:$C$29,0)),2),VLOOKUP(CEILING(C20,5)-4,$C$20:$E$29,3,FALSE),D20))This investment of time is worth it if you plan to execute the analysis on a continual basis, or if you think you will need to repeat the analysis in the near future. If you just want to move a few cell references, then stick with find & replace or add/delete arrays. They will do the job just fine.
Conclusion
Unfortunately, there is no Excel shortcut to grab and drag all instances of a cell reference in a formula. However, there are three alternatives, each with an optimal use case:
- Add/subtract arrays in a copy worksheet: more than 5 instances of a cell reference and the reference is well-isolated.
- Find & Replace: more than 5 instances of a cell reference and the reference is not well isolated, or you have more than one multiple-iteration reference to modify.
- Convert ambiguous structure to a database: you plan to apply the formula to much more data in the future.
