5
1
I have a large Excel Table (44 columns, 1000+ rows), but one of the columns has been accidentally turned into a Calculated Column (See Here). The result being that now when the users try to add new rows to the table (by dragging the bottom-right corner handle downwards), it inappropriately fills the new rows of this column with this formula, resulting in spreadsheet errors.
I have tried clearing the contents of every cell in this column that I can find that has the formula, but it still treats it as a Calculated Column, instead of a normal one.
How can I turn this off? I can employ VBA if needed, however, I cannot get rid of either Calculated Columns or AutoFill for the whole table because we use both extensively.
Additionally, how can I best keep users from accidentally doing this again in the future? They should only be entering data in the first 10 columns, not Formulas, but naturally they don't always follow instructions...
1Have you tried copying the entire column and re-paste it over itself using
Paste values
? – techturtle – 2013-03-20T14:45:15.430Yes, that worked! Thanks! Any insights on the second part? – RBarryYoung – 2013-03-20T14:56:40.830
Added my suggestion as an answer so you can accept it, and I included some thoughts about preventing this problem again. – techturtle – 2013-03-20T15:37:40.857
I've updated my answer below. Can't guarantee this method would prevent accidentally creating Calculated Columns, but it will stop them from entering formulas. – techturtle – 2013-03-20T19:09:53.710