In a Table - Change a calculated formula in a table and make that the new "default" formula for that column?

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
118
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a table with a number of calculated columns. I would like to change the formula in one column, and want to keep the formula for the previous entries, but when I enter a new row (which automatically expands the table) then I would like the new formula to be the default which is filled in automatically.

When I try this with the new formula in the row above, the old formula is automatically filled into the new row and I have to copy my new formula from the row above and paste it into the cell.

Any thoughts on how I change the default?

Thanks!
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

GilbW

New Member
Joined
Jan 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I haven't been able to edit the default formula, but it is possible to change it by deleting all cells in the row or column, then entering a new formula.
You can then change individual cells as required, and new rows or columns will be have the relevant cell filled with the new formula.
 

Smokeyham

Board Regular
Joined
Feb 1, 2006
Messages
118
Office Version
  1. 365
Platform
  1. Windows
I haven't been able to edit the default formula, but it is possible to change it by deleting all cells in the row or column, then entering a new formula.
You can then change individual cells as required, and new rows or columns will be have the relevant cell filled with the new formula.
Thanks for the response.

The issue I have is that I want to keep the formulas in the previously entered rows, so deleting them does not meet the need.

I have also tried leaving a blank row, creating new formulas and then deleting the blank row above to incorporate the row with the new formulas into the named table, but all that happens is that the new row updates with the old formulas.
 

GilbW

New Member
Joined
Jan 19, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
@Smokeyham,
If you want to keep the current formulae: add a new row, type in the new formula, which will populate the entire row, cut and paste the formulae you wish to keep from the old row, then delete the old row.
I know it would be better if you could edit the default formula for future cells, but it seems there is no way to do that 🤨
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top