Excel 2010 Table auto expand/formula copy not working

microsoft excelworksheet-function

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 🙂



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.

Best Answer

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.