Excel table: How can I edit the default column formula?

[deLockloire]

Hi,

I have an excel table, and some columns have a formula in it. Now usually, if you change the formula in the first row, excel will change if for you for all rows. However, the table has 7000+ rows so usually after a while I make the xlsx faster by copying most rows and pasting as values - thereby eliminating the unnecessary calculations. This way, I only have a couple of dozen rows that really has formula in it. But... this way, I can no longer change the column formula by simply editing the first row and whenever I add another row, the column in question gets populated with a wrong (old) formula, which I have to change manually. How can I change/edit the default formula for a column of an excel table? (I mean officially. I suppose, I could always replace the vaules back to formulas, but there should be a way to do this without resorting to trickeries and workarounds... or is there?)

Thanks,
deL

 

[doubledaffy]

I would just leave the formula you want in the first row. Then copy it down the column when you want to update the values. If you want to save calculation time you can then make all the cells starting in the second row into values. This way you can easily update the values and then turn them into static to save calculation time.

 

[deLockloire]

hey, thanks for the answer, however, unfortunately this doesn't work. once you have replaced some formulas into static values, changing the first row no longer has any effect on the other rows. but the excel must store the default formula somewhere, otherwise it couldn't fill the new cells with it. so, i suppose, it should be editable. other ideas?

i can solve this with workarounds, i'm just curious if anyone knows if the default formula is editable or not and if it is, where?

 

[bjurney]

I would change the calculation to Manual instead of automatic, and when you need to update the values from your formulas, you can press F9. That way it will run faster and you dont need to update your formulas all the time since they will always be there.

 

[doubledaffy]

That's a good way to do it too, but you have to remember to press F9 to calculate before you do things. Otherwise you can get some very interesting numbers.

Do you have the same formula in each row of the column, or does it change?

 

[deLockloire]

Do you have the same formula in each row of the column, or does it change?

same. it doesn't change

 

[juntjoo]

I know this is old, but I'm looking for this answer and we might as well properly conclude this thread with the answer if anyone has it?

 

[GilbW]

I haven't been able to edit the default formula, but it is possible to change it by deleting all cells in the row or column, then entering a new formula.
You can then change individual cells as required, and new rows or columns will be have the relevant cell filled with the new formula.

 

[slcy]

This looks like a bit of a necro, but this is the top result I got when searching for "excel table change default formula" so possibly still relevant if new traffic is coming here.

The logic that has worked for me is: the default column formula is set whenever you change all rows in that column in one action. This happens in 3 ways:

1. Base case: When a formula is entered into an empty cell in a table column, if all other cells in that column are the same formula or blank, it is extended to apply to all rows in that column.
2. When editing a formula that is the same for all rows in a column, the edits are extended to all rows.
3. Pasting a formula into all cells in the column. If the formula is the same for all rows, then the default formula is set to that. If it is not the same, then the default is reset to blank.

Also, default cell style for table columns seems to follow the same logic, just replace 'formula' with 'style'.

So taking a few different interpretations of the original question:

1. If I want all existing and future rows to be updated to the new formula, I can copy the first row formula down through the whole column and then re-paste as values all but the first row to reduce calculation time. (doubledaffy's first suggestion)
2. If I just want to remove an existing default formula for new rows, I can select the entire column and copy/paste as formulas. Since the column is not homogenous, this will reset the default formula to nothing. (not requested in this question, but relevant if you're stuck with an 'old' formula that you want to go away)
3. If I want to set the default formula to something new, but keep all existing data as is, I don't believe there's a one step solution. I would first copy the column contents to a new column. Then copy the new formula into all rows of the column (like in scenario 1) which will set the default, and add at least one new row. Finally, copy the backup values back into place. As long as this backup doesn't paste into every row, the default formula will remain in place and be used by new rows going forward.

Theoretically this third option could be put into a VBA routine, but if frequent updates to the default formula are required, perhaps a re-design is in order: abstract parts of the formula to be dependent on other columns which can change with the data, use multiple tables each with a different formula, etc.