5

I have an excel table that I set up some time ago. Two of the columns have formulas that in places have been typed over with values and in others have had the formula updated.

I'm at the bottom of the original table now and when I add a new row it auto-fills the original formula I used, not the updated one.

I have previously been able to work around this problem in other tables by deleting the formula everywhere in the column then putting the correct formula in the top cell. That is not as easily possible here since I have put manually values in a number of places. (I can do it in a complicated way.)

Does anyone know how to reset the "default" formulas in an excel table?

I'm using Excel 2010. Thanks.

Improve this question

5 Answers 5

Reset to default
3

Ah ha, found the simple solution! Had the same problem(s). Old formulas and lack of formulas in some columns were autofilling new rows in table.

There is a little helper that popped up while working in my table that says "Overwrite all cells in this column with the formula."

To trigger that helper popup, put cursor in a preferred formulated cell, then put cursor in formula bar, then press enter or click green check to finish 'editing' the formula. The helper popup should appear.

Improve this answer
2
  • Please add further details to expand on your answer, such as working code or documentation citations.
    – Community Bot
    Commented Sep 7, 2021 at 18:49
  • 1
    What version of Excel are you running?
    – Tim Joy T-Square Consulting
    Commented Sep 8, 2021 at 6:08
6

Here is my workaround solution:

  1. copy and paste-values from the table column that needs to be fixed to a blank column not adjacent to the table.
  2. delete all the values from the table column
  3. put the preferred formula the first cell in the column. The table should auto-fill the table column with this formula.
  4. paste back the values from the temporary column into the table.

This is equivalent to turn-it-off-and-turn-it-on-again. It worked for me.

Improve this answer
1

I solved the problem with an easy way (no need to create new columns or like so):

  • Copy the formula (with the correct code).
  • Select the entire column, then click on "Home" tab, click on Clear option (Clear All).
  • Paste the code.
Improve this answer
0

do a quick check if any rows are hidden and hence you are not deleting the formula in that columns; how will it otherwise remember old formulas?

"That is not as easily possible here since I have put manually values in a number of places. (I can do it in a complicated way.)" --> so how do you do that? having manually enter/override some values and still have formula be applicable to the column in general? (well, i can think of having extra dummy columns where one will do a formula and other override and this column having an OR based on those columns i guess)

Improve this answer
1
  • 1. Inserting values instead of the formulas was just a matter of typing over the formulas. This was done to handle anomalies (partial days in this case) without the use of helper columns. 2. Unhiding the rows doesn't make a difference because where-ever I type the new formula, it is only in that cell/cells. If I add a new row to the table is auto-fills the original forumla.
    – Tim Joy T-Square Consulting
    Commented Aug 10, 2017 at 0:31
0

If the first row of the column contains the new formula, then highlight all the cells from the second row of the column to the bottom of the table. Then Home-->Flash Fill-->Down. The flash fill has now been overwritten with the new formulas.

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .