1

I have a table with around 2k rows, for which I originally needed any formulae copied down into new rows, when they are added. To achieve this, I created a table from the data as many threads stated the table would auto-expand when creating new rows and copy the formula from the previous row down into the new cell. However this is only working intermittently for me. Some columns get their formulas copied down, some don't.

I have both 'Extend data range formats and formulas' and 'Enable AutoComplete for cell values' both checked in the options menu.

This is driving me mad so any help with this would be greatly appreciated :)

Thanks, Justin

Update:

It seems as though the issue is down to the table not auto-expanding when a new row is either created by typing or pasting values. When any cell from the current bottom row is 'dragged' (replicated) into the row below, the table auto-expands correctly, thus copying the formulae down. This seems like a bug.

The table edge is shown by the small blue border in the bottom right hand cell of the table, this gradually moves down as new rows are created, however as stated, when the new rows are created by either typing in a new row, or pasting a value into a new row, this does not move, and as a result the new data is actually outside the table, and therefore does not inherit the forumlae from the row above.

7 Answers 7

3

This generally happens to me when formula is not the same in all cells of a column. It should be fixed if you overwrite your formula in the column and select "overwrite all cells in this column with this formula" from context menu.

4
  • Thanks. I have tried that and unfortunately has not changed anything. I'm not sure it is relevant, but the table doesn't seem to be auto expanding as the little blue triangle in the bottom right corner of the table does not move down a cell when a new row is added. Mar 25, 2015 at 16:26
  • is options/proofing/autocorrect options/autoformat as you type/included new rows and columns in table checked? Mar 25, 2015 at 16:41
  • Yep it is. Curiously, it seems the main reason is that when pasting or typing a value into a new row cell does not trigger the table to auto-expand however, dragging the current bottom row's value down into the new row does, and in that case the formula is copied correctly. I'll put that as a note in my original question. Mar 25, 2015 at 16:45
  • 2
    This solution worked for me. Edited the formula in the first row and hit enter without changing anything, saw the tool-tip to populate the formula to all columns, and clicked it. After this the auto-expansion worked properly because it could intuit which formula to use. Thanks for the quick solution! Oct 20, 2016 at 13:02
1

So it seems as though the main reason for the forumlae not being copied down is as stated above; the table was not auto-expanding when new rows were added unless they were created by dragging down and duplicating the rows above.

The formulae being copied down were variations on the same VLOOKUP, and the lookup value used in these formulae was in the column being edited when creating a new row (typed or pasted into). There appears to have been some sort of problem with the formatting in that column which caused the table to not expand as it was deciding that the new row was not intended to be in the table. Once i made sure the formatting of this column was correct, everything started working correctly.

0

I had this problem. I found a solution by converting the table back to ranges and then back into a table. I think it was due to manually resizing the table.

0

I had this same or similar issue - it was solved by converting to range and back to table as another user pointed out.

0

Quick response to this one - I had exactly this issue and none of the above solutions worked - Excel seemed to think there was text in some of the rows below the table (even thought they were all blank) - I had to delete the rows from the one after the table to the very bottom of the worksheet to resolve this

0

It may be a referencing issue. For instance, if you are using table reference such as =[@YOUR_HEADER]. This will work fine for copy and paste but not for inserting or adding rows. Change your reference to =[@[YOUR_HEADER]] and you should get the results you are looking for.

-1

I had a similar error in Excel 365 where certain formulas would not copy down. I don't understand the reason why, but a solution I found was to just create a new column and retype the formula.

1
  • That's not really a solution. That's just a workaround.
    – zx485
    Jul 30, 2021 at 23:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .