My table does not auto-insert all the formulas when a new row is inserted

Question

Hi,My excel sheet has 5 columns with formulas. I converted it into a "Table" so that those formulas auto-fill whenever I insert a new row (between rows).&nbsp;&nbsp;Upon inserting a new row, only 4 out of 5 columns will auto-fill the formula. I've checked and all the necessary columns are included in the "Table".&nbsp;Can anyone advise why this is happening?

detlef_lewin · Answer

reemav&nbsp;You have somehow broken the table autofill.Delete all formulas in the column except one. Go to that cell with the formula and press F2 and ENTER.&nbsp;

reemav · Answer

Hi Detlef Lewin,I've tried doing the above but it still does not work..Also tried redoing the entire excel sheet from scratch and realised the table autofill gets broken when i try to "lock" those cells and "protect" the excel sheet.Not sure why..

detlef_lewin · Answer

reemav&nbsp;Take a look at some of the suggested solutions:https://www.google.com/search?q=protect+ecel+sheet+with+a+table&nbsp;

chadl52 · Answer

Worked perfectly for me. My original problem was that I had a pre-existing spreadsheet w/ formulas that I turned into a table. I found that any column w/ existing formulas wouldn't auto-fill but any new column I created and entered a new formula, it would work. the F2+Enter workaround saved me a ton of legwork getting all my existing formula columns to behave as desired. Other threads on the same topic had mediators answering that Excel tables don't do that, period. Glad I tried one more search and found this solution. Thanks much!!!

yelhadidy · Answer

Hi Reemav,I am facing the same problem with my tables. What I noticed is that whenever the formula references the column name instead of the letter/number reference, it is automatically filled whenever a new row is added.&nbsp;Try to use the cell references as the column names and it should work as long as you're referencing cells from the same row. I am still trying to figure out how to do it for references in different rows.

Forum Discussion

My table does not auto-insert all the formulas when a new row is inserted

8 Replies

Resources