Thread: Fill/drag feature as an Excel function? Or, a *relative* "=" function?

I am not an Excel expert, so forgive me if some of my terminology is confusing/incorrect.

One of the most widely used basic features of Excel is the fill/drag feature. I am wanting to use this feature, but would like to implement it as an Excel function. What do I mean? See the example below.

• Suppose column A is filled with 100 numeric data entries, A1 through A100.
• Suppose that cell B1 will *eventually* be used for a function (e.g. - "=A1^2").
• Suppose that I want the cells B2 through B100 to have the same formula as cell B1, except replacing "1" with [their row #] (e.g. - if B1 is "=A1^2", I would want B2 to be "=A2^2", and so forth).

Usually I would use the drag/fill feature to drag cell B1 down to cell B100. Works great. However, what if I want to set up cells B2 through B100 beforehand so that they will equal whatever gets entered into B1 (in a drag/fill sense, so that they equal B1's formula, except using their own row #).

I know there are keyboard shortcuts one can use instead of dragging; that is not what I am looking for. The idea is that I will have a worksheet that involves a series of drag/fills all based on a formula from a single cell, and that I may want to go back and change that initial formula time and time again (but I don't want to keep having to re-dragging the necessary rows/columns every time the initial formula changes).

In brief, my problem really just boils down to this:

I would like to first type an Excel function into cell B2 that "copies" cell B1 (i.e. - like how the Edit -> Copy/Paste usually works), so that later when I type a formula into B1, the analogous formula instantly occupies cell B2.

e.g. - If I type "=A1^2" into B1, I want B2 to become "=A2^2".
Later, if I change B1 to "=1+A1^3", I want B2 to become "=1+A2^3". Etc.

Another way to articulate what I want is that I simply want a *relative* version of the "=" (it's an *absolute* "=", as is). What's funny is that most of Excel's other features are, by default, *relative*, except for "=". Is there some sort of "=RelEqual([cell])" function I don't know about?

Excel already has this functionality with their drag/fill feature; I'm shocked that I can't find an Excel function for this feature ... it seems like a pretty basic thing to ask for. I'm sure this "VBA" I've read about could do the trick, but VBA sure seems like overkill (and, I'm not in a position to use it).

Thanks in advance for the help!

Excel doesn't do that. It's fast and easy to just double-click the fill handle.

Thanks for the quick reply shg. So there really is no way to do this? I'm shocked. It seems right up Excel's alley...

It may be "fast and easy" to first enter a formula and then set another cell to be relatively equal to it (via dragging, special clicking/typing shortcuts, or via Copy&Paste), but what it you can't or don't want to do it in that order?

The example I gave doesn't fully describe my situation. In reality, I have long complicated Excel sheets that require lots of dragging/filling & copy/pasting all based on a just a few initial formulas that I input. I often need to go back and start a brand new Excel sheet from scratch that is totally the same except that those few initial formulas changed. Super annoying.

Here's another example of why I would like to do this: I sometimes create Excel sheets for others where they only need to input certain data and then other cells automatically get filled in for them (I've found this useful for reporting income, taxes, and other things; I create such sheets for those I know who are by in large technology-inept). This usually works great, but if one of the pieces of data that the user needs to input is a formula of some type, then I usually run into this same problem ... excel can't "equal" or "copy" formulas in a relative way unless you ask it to do so in real-time (in which case, it is the default way it copies data, and copy/pasting an absolute piece of data becomes cumbersome ... very strange).

I've used Excel for over 20 years, and the utility of what you describe never crossed my mind.

One could write VBA code to automatically copy a formula down following entry, but expect it would be more fruitful to look at the workbook design and see if there isn't a better approach.