Changing the formula in a table from a specific row on in excel

Question

I have an Excel table with formulas. When I add a new row to the table, the formulas will automatically be added to the new row. This is normally desirable since it eliminate the need to write the formulas each time. Now I need to change the formula, but this formula should only affect the rows that will be added from now on and don't change the calculation of the rows above.

Is there any trick to that? Does anyone have any solution?

Answer 1

Change your formula so that it does a separate calculation based on whatever change you're trying to account for. Without seeing your data, I can't be more specific, but...

If you can move some part of the formula out of the formula, do that. So =[@Amount]*1.2 gets changed to [@Amount]*1.4, and that starts on 1/1/2023, you could create another table with the effective date and the rate, and change the formula to [@Amount]*VLOOKUP([@Date],tblRates,2,TRUE) for example.

How easy that is depends on what in your data changes to trigger the formula change.

Formulas in tables are fickle, at least in 2019 (the version I'm using). If you have any inconsistencies in the column, you could lose the ability for the formula to autofill in new rows. And even if you don't lose that, it's hard to predict which version of the formula will fill in.

Answer 2

You can try something like this where you test to see which row of the table you are currently in:

=IF(ROW()-ROW(Table1[#Headers])<=10,[@x]*2,[@x]*3)

So for the first ten rows the formula in column y is 2x, then after that it's 3x.

Answer 3

Go to File > options > proofing then click on AutoCorrect Options.. button. in Auto correct window go to Auto format as you type tab an uncheck as show in picture.

this is prevent excel to automatically change other table rows.