Table Formulas Not Expanding

Brass Contributor

Morning all - need assistance with table formulas not expanding.  I've spent a few hours troubleshooting and changing the various settings under Options one at a time, as well as changing cell formats in the table itself, but nothing has worked.  Short version - this table helps my supervisors automate tracking of employee attendance.  column A and B are ID and Name, columns C-F are formulas.  When tabbing across the last row to the point the table successfully creates a new row below, only the formula in column F copies down and applies to the new row. 


I've turned all these settings on and off individually with no luck - under File > Options >:

-Formulas > Formula AutoComplete

-Proofing > AutoCorrect > AutoFormat As You Type > Automatically as you work > Fill Formulas in tables to create calculated columns 

-Advanced > Enable Autocomplete for cell values > Automatically Flash Fill


I've even copied the entire tab to a fresh workbook.  In the new workbook, with the user list as the only sheet, the formulas copy perfectly in C-F when adding a new row to the table.  I'm attaching the de-identified copy for review, but it doesn't show the same behavior as the main file - which of course i can not post since it houses employee information.


Does anyone have any tips for troubleshooting this issue?



6 Replies


Try the following:

  • Clear the cells in columns C to F in all data rows of the table except the first one.
  • Select C2, press F2 then Enter. The formulas should be copied to all table cells in column C.
  • Do the same for D2, E2 and F2.
  • Now test creating a new table row.
Hans - thanks as always for the help! Performed the steps as described, but the formulas still do not apply down the entire column. Even attempted two other items:
1. Clear all formulas, redefine the area as a range, redefine as a table and do the same - still no luck.
2. Create fresh table from blank sheet. Still same issue occurs.

Is it possible there is yet another setting not appropriately selected, or settings conflicting with one another? Seems to be at the level of the workbook, at not at the level of the sheet or table.


I'm stumped - this behavior is controlled by the second setting you mentioned in the first post:

  • Proofing > AutoCorrect > AutoFormat As You Type > Automatically as you work > Fill Formulas in tables to create calculated columns.

Perhaps the workbook has become corrupted...

i was beginning to wonder that, but really hope not... i don't want to have to start this thing from scratch again, it was a beast to build over a few years. I have seen notes of differing autofill auto correct auto complete settings conflicting with one another, but very few mentions.


And since i built this thing myself, i'll only have so much company/IT support available to root cause it.  But will keep digging and if all else fails here, maybe get lucky with a contact in my company.  not giving up yet, but thanks.  And open to any ideas...

Final update here Hans - ended up rebuilding the sheet, tab by tab. Copied all user tabs one by one into a new workbook, then the "template", then recreated the user list (the problem tab) from scratch in the new workbook. that worked so i'm making the same assumption that it was a somehow corrupted file causing the formatting error. Thanks again, as always!


A lot of work, but it paid off. Well done!