How to remove Legacy Formula in Excel Table that are pasted automatically?

[Edouard Chandor]

Hi all,

I am trying to get rid of some legacy formulas in one of my Excel table. When trying to remove the content in the table or pasting everything as values, I can still find the XML tag <calculatedColumnFormula> with the old formula in the XML for some of the columns in that table so when extending the number of rows of that table the formula gets pasted automatically. I've tried to untick the Autocorrect option but no luck.

Is there any easy solution?

Thanks,

Edouard

[#2]

Hi Edouard,

Interesting question. IMHO, as soon as calculatedColumnFormula tag appeared in Table.xml you can't remove it within UI.

Tag appears if you enter the formula into the any cell of empty column or if you copy the formula into all cells.

The solution could be if you create new column (doesn't matter what will be AutoCorrect option "Fill formulas..."), move your data here from "old" column (e.g. as copy/paste values) and remove it after that. After that you may add formulas to cells, but don't expand them on entire column.

After that adding new rows to the table shall not add formula in that column.

[#3]

Hi Edouard,

When you create a formula in a Table then it gets stored in that column.  I like to think of it as being "stored" in the column header.

That way even when you delete all rows, the formulas don't get wiped out, even if they seem to disappear.

If you want to clear out the existing formula from a Table column then you need to highlight the entire column in the table  (Ctrl+Spacebar) then press delete.

If you need to keep existing data then as @Sergei Baklan suggests you should paste as values to new column first