Single cell formula changes after reopening spreadsheet.

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
115
Office Version
  1. 2019
Platform
  1. Windows
Lads,

I have formula applied to entire column e.g. =A1+B1. When I change formula in one cell I need and hit enter this new formula automatically applies to the rest of column. Then I hit backspace and formulas on rest of column goes back to normal and my just edited cell has got the new formula which works ok until I save spreadsheet & reopen it and my edited formula changes back to match entire column. I have tried absolute cell reference e.g. =$A$1+$B$1 on this one cell I need to alter and it does the same after reopening sheet.

Is there any way around this issue? Thanks.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hey strongman86,

OK, if we go by your example, =A1+B1 then any time A1 or B1 change you will get a new result. This is by design. You are asking to add A1 and B1, and just like in real life, the result will change.

Try to set the automatic calculations to manual, and see if this helps. But this means you will be forced to issue a recalculate each time you need to, and before saving the workbook.

I feel there is a design issue here, where what you are trying can be done more efficiently with more knowledge of MS-Excel. Possibly if you can post more details we could provide better answers.
 
Upvote 0
Here's example: Column D has got formula applied add adjacent cells from A & B. When I change lets say cell D4's formula manually after saving and reloading it changes back to A4+B4. Turning automatic calculations off is not and option as I need them to be turned on all the time. Thanks.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]=A1+B1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]=A2+B2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]60[/TD]
[TD]50[/TD]
[TD][/TD]
[TD]=A3+B3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]50[/TD]
[TD]60[/TD]
[TD][/TD]
[TD]=A4+B4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]80[/TD]
[TD]66[/TD]
[TD][/TD]
[TD]=A5+B5[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]44[/TD]
[TD]33[/TD]
[TD][/TD]
[TD]=A6+B6[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]45[/TD]
[TD]36[/TD]
[TD][/TD]
[TD]=A7+B7[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]66[/TD]
[TD]44[/TD]
[TD][/TD]
[TD]=A8+B8[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]33[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]=A9+B9[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Formula was applied in first cell of the column and excel automatically dragged it down for me.
 
Upvote 0
Is it a table i.e. it was a range of cells and you made it a table by selecting the range and clicking Insert - Table?

excel automatically dragged it down for me
Excel doesn't automatically drag down formulas without you taking an action either manually or via VBA. Explain more.
 
Last edited:
Upvote 0
This is existing spreadsheet I'm working with and it is connected to ''query from MS Access Database''.
I'm no expert, but I think it's something to do with external data properties. See link. https://drive.google.com/open?id=1F1y6nO0wWGG9z5EFHXZFQjBktOG83G30. I have manually added more columns that i'm working with and looks like cell formatting is automatically applied.
 
Upvote 0
Excel likes to put data queries into tables. When you are in one of the cells that is reverting a formula, is the "Design" tab visible in the ribbon?

You can convert the table to a normal cell range by choosing "Convert to Range" on the Design tab. You probably want to try this first on a copy of the worksheet.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

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
Back
Top