Share via

Excel formula not expanding in tables

Crystal Cannon 40 Reputation points
2025-12-19T18:37:10.6766667+00:00

Hi, still having the same problem with formulas not expanding in excel table as new data is added. I've asked for help before and no advise has seemed to work. Now I'm wondering if this is a known issue that is being worked on?

I've even created new workbooks with new tables to see if the problem was related to one specific workbook. It is not. The problem persists over any workbook and any table.

I've gone to options, proofing, auto correct options, I've checked the table for any spaces and made sure all relevant cells were formatted correctly. I've

done everything recommended and nothing has helped.

So again, just wondering if I'm the only one or if this is a known issue. It's getting pretty frustrating. Thanks for any help you may be able to offer.

Microsoft 365 and Office | Excel | For home | Windows
Answer recommended by moderator
  1. riny 20,870 Reputation points Volunteer Moderator
    2026-01-06T09:51:42.16+00:00

    The column "Balance" does not contain one consistent formula. The first cell has a single number 180 and the cells below have formulas. Then Excel doesn't 'auto copy' the formulas down when you add a row at the bottom. The remedy is:

    1. to clear entire column called "Balance"
    2. enter the following in I2 =SUM(I1,-[@Withdraws],[@Deposits])
    3. note that the formula gets copied down all the way to the bottom
    4. add an new row and the formula shall copy automatically as expected.

    .

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Crystal Cannon 40 Reputation points
    2026-01-05T18:39:42.8766667+00:00

    Barry thanks for your patience. It's been a while. Sharing files is new to me. I'm still back in the dark ages apparently when you could just attach a file. So that's where some of the misunderstanding is stemming from.

    Let me try once more. If i'm in cell J74, I hit enter , the cursor moves to D75 and the table expands to that new row. But the formula that should have expanded to cell I75 does not. That cell has no formula in it.

  2. Barry Schwarz 5,431 Reputation points
    2026-01-02T02:09:43+00:00

    The only way I31 will change to J31 is if you copy, not move, the cell one column to the right. Do not copy the text of the formula from the formula bar.

    I am not aware of any way adding rows will change a column reference.

    On the other hand, copying the cell down one row will change the I31 to I32 which is consistent with what you describe as desired.

  3. Anonymous
    2025-12-19T19:22:31.7133333+00:00

    Hi Crystal Cannon, I understand how frustrating it is when something as simple as adding rows doesn’t behave the way you expect especially after you’ve tried every recommended fix.

    Turn on Excel AutoCorrect options for tables make the column a calculated column using structured references add rows inside the table ensure automatic calculation is enabled check for protection or mixed data types and if you’re using Power Query or suspect corruption rebuild the table or push calculations into the query.

    When you add a new row does the table border expand and do you see the overwrite prompt when you press F2 and Enter in the formula cell?

    Best regards

    Lucky

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.