How to Remove Calculated Column Formula from a Table? RRS feed

  • Question

  • I have a table with a calculated column. Is it possible to remove the underlying formula from the calculated column, while leaving the column and existing formulas in the column intact? So, when I add a new row to the table, it will not automatically calculate that column? I do not want to change the formula or delete the column, I just do not want to have the column automatically calculated for new rows. If this is possible, how is it done?


    Friday, August 29, 2014 4:49 PM


All replies

  • Hi,

    If you want to remove underlying formula and keep the value in the Excel cells, we can try the following steps:

    Choose the column which you want> Copy> Paste> Paste special> Values 



    George Zhao
    TechNet Community Support

    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    • Marked as answer by George123345 Thursday, September 4, 2014 8:32 AM
    Monday, September 1, 2014 2:30 AM
  • That was not an answer.

    1. it would remove formula from existing cells
    2. it won't prevent automatic calculations for new rows.

    Monday, March 12, 2018 10:51 AM
  • How about asking it this way … create a "Structured table" in excel, then apply a formula using structured references.  Ensure you have a few rows in the table … then change one of the cells in that column and Excel will ask you if you want to update all the values in that column with this new formula?

    Although this is an awesome feature in Excel, and has been around for quite a while, there are times when you no longer want the same formula in each cell of the column.  So the original question is: "How do you remove the calculated column"?  Meaning, how do I get Excel to stop asking me to update all the other cells in the column, and stop highlighting the cells of the column with different formulas?

    If anyone can provide an answer to that question, That would make both Mike and myself quite happy.


    Monday, December 17, 2018 8:41 PM
  • I figured it out. Unfortunately, the options in the context menu disappears once the cell is reverted back to a regular cell with no formula.  

    I had to right-click the cell and go to -> table -> restore cell (or similar wording)  

    Tuesday, February 12, 2019 11:58 AM
  • Had to figure this one out myself...

    The underlying formula will automatically be removed when you clear all values and formulas in the entire column. You can do this by highlighting the entire column (excluding table headers or Totals row), then go to Home -> Editing -> Clear -> Clear Contents, or just hit the Delete key on your keyboard.

    Obviously this will wipe out all your data in the column, which you may still want. To remove the underlying formula while preserving the data in that column, cut the entire column's contents and paste somewhere outside the table.  Then, cut/copy and paste that same data you want back into the same column. Cutting out the data removes the underlying formula, then pasting gives you back your original data, except now there is now underlying formula.

    Love, peace, and chicken grease

    Wednesday, March 18, 2020 2:52 AM