How can I turn a Calculated Column back into a data column in an Excel Table?

Question

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...

Solution 1

The fastest way I've found to remove large numbers of formulas is to select the column, copy it, and then paste it over itself using the Paste Values function.

As far as preventing users from repeating this again, the only way I know would be to use the Protect Sheet and Protect Workbook features. Whether these can be adapted to your particular scenario would really depend on what you are trying to protect and what you still need to allow the users to do. Generally, however, you can limit the users to editing only certain ranges, columns or rows by locking all the other cells. There are options to allow users to still insert new rows, if desired. Unfortunately, I don't know of a way to lock the sheets so that users can enter data but can't add formulas.

You could use Data Validation to add an input message reminding the users not to enter formulas. It wouldn't stop them, but it would at least remind them constantly. Data Validation can also be used to force them to enter a specific data type, but it wouldn't stop a formula that provided the correct data type as its result.

---

UPDATE:

You can explicitly reject formulas in Excel by using the Worksheet_Change event, like so:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Range("C:C"), Range(Target.Address)) Is Nothing Then
        If Range(Target.Address).HasFormula Then
            Range(Target.Address).Value = ""
            MsgBox "You may not enter formulas on this sheet!"
        End If
    End If
End Sub

In this example, the range checked for formulas is the entirety of column C. You could define any range you want to check, and then blank out the formula, change it to something else, warn the user, etc.

Solution 2

TechRepublic has:

Function IdentifyFormulaCellsUsingCF(rng As Range) As Boolean  
IdentifyFormulaCellsUsingCF = rng.HasFormula
End Function

which can be used to highlight (say yellow) and format (say “DO NOT USE FORMULA”) cells with formulae.

Does not prevent entry of formulae but does provide a more immediate warning than say Find > Formulas.

Comments

[RBarryYoung]

Yes, that worked! Thanks! Any insights on the second part?

[techturtle]

Added my suggestion as an answer so you can accept it, and I included some thoughts about preventing this problem again.

[techturtle]

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.