Search code examples
excelcalculated-columns

How do you change the default calculated column formula in an Excel list table?

I have a list with a column that did a lookup to another sheet in the workbook. The original formula looked like this: =VLOOKUP([@[Last Name]]&" "&[@[First Initial]],LookupLastF,9,FALSE).

Later the lookup column was moved, so I had to change the formula to lookup column 10 instead of 9: =VLOOKUP([@[Last Name]]&" "&[@[First Initial]],LookupLastF,10,FALSE). I had to manually copy the formula through the column, because there were exceptions -- i.e. some items overwrote the formula with manually entered values.

The problem is every time a new row is inserted into the table, the old formula is copied to the row instead of the corrected formula. So a default based on the original formula must still exist somewhere. How do I change it to the new formula?

Solution

  • You have to delete the content of the whole column and then (re-)apply the formula.

    Mixing formula and static values within one column isn't a good idea.

    Use three columns instead: calculated value, manual value, result value (where a formula shows the manual value if exists and if not the calculated value)