3

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.

2

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 | |
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. 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 Aug 10 '17 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.