Will Excel do the following stunt for me automatically without a macro?
(Google fu results in a lot of macros trying to accomplish this.)

I have a formula in some rows at the bottom of a spreadsheet. The formula is made entirely of relative cell references, plus some math. The formula is totaling information from higher rows, then performing a simple calculation to get the accuracy percentage of a set of numbers from some of the higher rows. The numbers come from testing a bunch of manufacturer's hardware products, and the accuracy is the results of our tests on each product. Each column is a different product.

When I add new products to this spreadsheet (inserting columns), the formulas do not get copied. There are blank spaces where the formulas should be. I want the formula to fill in those gaps automatically.

This seems to me to be a very common thing to want to have happen automatically. So common that I'm surprised it doesn't do what I expect it to do already.

I already know how to do this:
- Drag the little box to fill in the formulas for me. But that's an extra step which I sometimes forget.
- Instead of inserting blank columns, copy an existing column and say "paste" with "insert copied cells". I have reasons for wanting to insert blank rows instead of copying and pasting.

Can Excel automatically do what I'm talking about, without any further intervention from me? (i.e., not running a macro and not copy/paste/manual-drag-to-fill operations)

"forumula" sounds kind of baleful, like something from a gothic horror.

I don't think that there's any way to automatically add data to a cell without resorting to macros.

In similar situations, I tend to select the cell, copy it, select the entire row or column (by clicking on its header), and then pasting. If you want to be tidy, you can wrap the existing formula:

Odd, it seems perfectly happy to do it with other kinds of things, like formatting or conditional formatting. Why not formulas?

How is it that you want it to work? If you were doing conditional formatting, you'd still be applying a format, albeit a conditional one, to a bunch of cells that you'd selected.

Well, I suppose you could use the STYLE() function, but you still have to enter that in a cell, and there's no way to loop using Excel functions, so you're still talking about using a cell to run a single function, or, at most, a finite number of them.

Seriously, though, how are you wanting it to work? Pretend that you can just make up Excel functions and show us what you want to write.

When inserting a new column, you seem to want Excel to read your mind about which cells, of the new column are to be left blank, which should be copied from the adjacent column.

Ain't gonna happen unless you write a macro to do the dirty work.

This is starting to sound like you may need to create an excel form for data entry. Fill in the new values, hit the button. Excel processes this adding a column and filling it in.

Yup. And why not? It ain't rocket science. For example:



Where the "Total" row is a bunch of SUM functions. The sum functions are all identical except for their relative cell references, and I created them by putting one SUM under Fred and filling to the right.

If I insert a new "Marge" column between Jeff and Mary, how hard can it be for the program to automatically figure out that I want Marge's sales totaled up, too?

If, for example, I'd colored the April row green for spring, Excel is perfectly happy to copy that color definition into the inserted column. So why not the formula too?

It should be dead easy. The formulas are exactly adjacent, are identical, and their relative cell references line up. Excel is even smart enough to change the cell references in the Mary column so that they still give the correct total. I see no reason why it shouldn't automatically just fill in the formula in that row.

Sure, you can probably come up with an example where you wouldn't want to fill in the formula there. I think those situations would be in the minority, but even if it wasn't the default behavior, I'd still be happy with a secondary "Insert Column, fill any formulas" option on the menu.

Thing is, a lot of formula references are not so straight forward.

A simple copy right can yield bad results, not in this case, but these are more the exception than the rule.

Fine, so they should give me the option to do it when I know the case is simple.

It's such a common thing to want to do. I'm so surprised there isn't already a way to do it.

Well, that's why they provide macros.

Following up: In the end, I am using conditional formatting on the spreadsheet to make it obvious which cells on the spreadsheet have formulas. Then, if another user inserts some columns without copying the formulas, I see a hole where the formulas should have been.

I have to enable macros and then create a function inside a "Module" like this:


Then conditional-format the cells so that if =IsFormula(XX) returns True, then shade the cell light gray.

Ha, I typically want Excel to NOT autopopulate stuff when I insert columns and rows. The simpler the better.

Reading the MS Office questions on this BBS is a hoot. I though I knew the program, then I see how expert you all are.