Excel: What can cause manual calculation / F9 to stop recalculating?
What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?
I've Googled this problem and ruled out all the explanations I can find:
- Calculation settings are set to automatic.
- Formulas/Calculation Options for the tab is set to automatic.
- Manually recalculating doesn't work.
- Calculate in VBA doesn't do it.
- It's not a circular reference or numbers stored as text issue.
Normally calculating manually with F9 or switching calculation to automatic works as expected, but sometimes I have to use Ctrl + Shift + Alt + F9 to recalculate certain cells. That works as a short term workaround, but I need to fix the problem going forward to calculate normally. I've only ever seen the issue happen in specific cells in spreadsheets sent to me by one person. I've been unable to recreate the issue or figure out what is causing it.
I hope this is ok to ask here, I tried on StackOverflow and was told it was a question for SuperUser.
microsoft-excel microsoft-excel-2010 worksheet-function
edited May 27 '16 at 2:06
pun
4,83081853
asked Apr 8 '16 at 21:57
ELWELW
10112
|
show 2 more comments
What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?
I've Googled this problem and ruled out all the explanations I can find:
- Calculation settings are set to automatic.
- Formulas/Calculation Options for the tab is set to automatic.
- Manually recalculating doesn't work.
- Calculate in VBA doesn't do it.
- It's not a circular reference or numbers stored as text issue.
Normally calculating manually with F9 or switching calculation to automatic works as expected, but sometimes I have to use Ctrl + Shift + Alt + F9 to recalculate certain cells. That works as a short term workaround, but I need to fix the problem going forward to calculate normally. I've only ever seen the issue happen in specific cells in spreadsheets sent to me by one person. I've been unable to recreate the issue or figure out what is causing it.
I hope this is ok to ask here, I tried on StackOverflow and was told it was a question for SuperUser.
microsoft-excel microsoft-excel-2010 worksheet-function
edited May 27 '16 at 2:06
pun
4,83081853
asked Apr 8 '16 at 21:57
ELWELW
10112
Any similarities in the cells it happens to?
– Raystafarian
Apr 8 '16 at 21:58
I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally.
– ELW
Apr 8 '16 at 22:01
What if you copy that sheet to another workbook? Does it still happen?
– Raystafarian
Apr 8 '16 at 22:02
Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected.
– ELW
Apr 8 '16 at 22:05
I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module.
– Raystafarian
Apr 8 '16 at 22:05
|
show 2 more comments
What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?
I've Googled this problem and ruled out all the explanations I can find:
- Calculation settings are set to automatic.
- Formulas/Calculation Options for the tab is set to automatic.
- Manually recalculating doesn't work.
- Calculate in VBA doesn't do it.
- It's not a circular reference or numbers stored as text issue.
Normally calculating manually with F9 or switching calculation to automatic works as expected, but sometimes I have to use Ctrl + Shift + Alt + F9 to recalculate certain cells. That works as a short term workaround, but I need to fix the problem going forward to calculate normally. I've only ever seen the issue happen in specific cells in spreadsheets sent to me by one person. I've been unable to recreate the issue or figure out what is causing it.
I hope this is ok to ask here, I tried on StackOverflow and was told it was a question for SuperUser.
microsoft-excel microsoft-excel-2010 worksheet-function
edited May 27 '16 at 2:06
pun
4,83081853
asked Apr 8 '16 at 21:57
ELWELW
10112
What can cause manual calculation/F9 to stop recalculating some cells properly in Excel 2010 ?
I've Googled this problem and ruled out all the explanations I can find:
- Calculation settings are set to automatic.
- Formulas/Calculation Options for the tab is set to automatic.
- Manually recalculating doesn't work.
- Calculate in VBA doesn't do it.
- It's not a circular reference or numbers stored as text issue.
Normally calculating manually with F9 or switching calculation to automatic works as expected, but sometimes I have to use Ctrl + Shift + Alt + F9 to recalculate certain cells. That works as a short term workaround, but I need to fix the problem going forward to calculate normally. I've only ever seen the issue happen in specific cells in spreadsheets sent to me by one person. I've been unable to recreate the issue or figure out what is causing it.
I hope this is ok to ask here, I tried on StackOverflow and was told it was a question for SuperUser.
microsoft-excel microsoft-excel-2010 worksheet-function
microsoft-excel microsoft-excel-2010 worksheet-function
edited May 27 '16 at 2:06
pun
4,83081853
asked Apr 8 '16 at 21:57
ELWELW
10112
edited May 27 '16 at 2:06
pun
4,83081853
asked Apr 8 '16 at 21:57
ELWELW
10112
edited May 27 '16 at 2:06
pun
4,83081853
edited May 27 '16 at 2:06
pun
4,83081853
edited May 27 '16 at 2:06
pun
4,83081853
4,83081853
asked Apr 8 '16 at 21:57
ELWELW
10112
asked Apr 8 '16 at 21:57
ELWELW
10112
asked Apr 8 '16 at 21:57
ELWELW
10112
10112
Any similarities in the cells it happens to?
– Raystafarian
Apr 8 '16 at 21:58
I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally.
– ELW
Apr 8 '16 at 22:01
What if you copy that sheet to another workbook? Does it still happen?
– Raystafarian
Apr 8 '16 at 22:02
Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected.
– ELW
Apr 8 '16 at 22:05
I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module.
– Raystafarian
Apr 8 '16 at 22:05
|
show 2 more comments
Any similarities in the cells it happens to?
– Raystafarian
Apr 8 '16 at 21:58
I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally.
– ELW
Apr 8 '16 at 22:01
What if you copy that sheet to another workbook? Does it still happen?
– Raystafarian
Apr 8 '16 at 22:02
Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected.
– ELW
Apr 8 '16 at 22:05
I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module.
– Raystafarian
Apr 8 '16 at 22:05
Any similarities in the cells it happens to?
– Raystafarian
Apr 8 '16 at 21:58
Any similarities in the cells it happens to?
– Raystafarian
Apr 8 '16 at 21:58
I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally.
– ELW
Apr 8 '16 at 22:01
I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally.
– ELW
Apr 8 '16 at 22:01
What if you copy that sheet to another workbook? Does it still happen?
– Raystafarian
Apr 8 '16 at 22:02
What if you copy that sheet to another workbook? Does it still happen?
– Raystafarian
Apr 8 '16 at 22:02
Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected.
– ELW
Apr 8 '16 at 22:05
Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected.
– ELW
Apr 8 '16 at 22:05
I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module.
– Raystafarian
Apr 8 '16 at 22:05
I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module.
– Raystafarian
Apr 8 '16 at 22:05
|
show 2 more comments
2 Answers
2
active
oldest
votes
A. If you use Macro functions or some specific CELL function, they do not recalculate when you use F9. That is per user manual.
B. If you do any other action during the calculation, it stops. This includes cursor-down and such things. Basically, any key you press (and any button you click) stops the recalculation; including ALT-TAB to switch windows.
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
add a comment |
Check whether the Cell Format for the cell containing the formula that won't calculate to ensure it is NOT set to "Text". If it is, change it to General.
Then edit the cell and exit the cell. The formula should now resolve.
I recently encountered the same issue and this seems to happen only for cells of format Text. When it does occur, I haven't found a way for the formula to resolve without editing and exiting the cell. Copying and pasting the cell, whether pasting Formula, Format, or even Value still produces the unresolved formula.
answered May 26 '16 at 23:26
MJAMJA
11
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
no, if it is set to 'Text', everything would look exactly as he types (so4would look4and not4.000000), and the numbers could not be used for formulas.
– Aganju
May 28 '16 at 1:45
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1063211%2fexcel-what-can-cause-manual-calculation-f9-to-stop-recalculating%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
A. If you use Macro functions or some specific CELL function, they do not recalculate when you use F9. That is per user manual.
B. If you do any other action during the calculation, it stops. This includes cursor-down and such things. Basically, any key you press (and any button you click) stops the recalculation; including ALT-TAB to switch windows.
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
add a comment |
A. If you use Macro functions or some specific CELL function, they do not recalculate when you use F9. That is per user manual.
B. If you do any other action during the calculation, it stops. This includes cursor-down and such things. Basically, any key you press (and any button you click) stops the recalculation; including ALT-TAB to switch windows.
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
add a comment |
A. If you use Macro functions or some specific CELL function, they do not recalculate when you use F9. That is per user manual.
B. If you do any other action during the calculation, it stops. This includes cursor-down and such things. Basically, any key you press (and any button you click) stops the recalculation; including ALT-TAB to switch windows.
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
A. If you use Macro functions or some specific CELL function, they do not recalculate when you use F9. That is per user manual.
B. If you do any other action during the calculation, it stops. This includes cursor-down and such things. Basically, any key you press (and any button you click) stops the recalculation; including ALT-TAB to switch windows.
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
answered May 27 '16 at 1:59
AganjuAganju
8,47731335
8,47731335
add a comment |
add a comment |
Check whether the Cell Format for the cell containing the formula that won't calculate to ensure it is NOT set to "Text". If it is, change it to General.
Then edit the cell and exit the cell. The formula should now resolve.
I recently encountered the same issue and this seems to happen only for cells of format Text. When it does occur, I haven't found a way for the formula to resolve without editing and exiting the cell. Copying and pasting the cell, whether pasting Formula, Format, or even Value still produces the unresolved formula.
answered May 26 '16 at 23:26
MJAMJA
11
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
no, if it is set to 'Text', everything would look exactly as he types (so4would look4and not4.000000), and the numbers could not be used for formulas.
– Aganju
May 28 '16 at 1:45
add a comment |
Check whether the Cell Format for the cell containing the formula that won't calculate to ensure it is NOT set to "Text". If it is, change it to General.
Then edit the cell and exit the cell. The formula should now resolve.
I recently encountered the same issue and this seems to happen only for cells of format Text. When it does occur, I haven't found a way for the formula to resolve without editing and exiting the cell. Copying and pasting the cell, whether pasting Formula, Format, or even Value still produces the unresolved formula.
answered May 26 '16 at 23:26
MJAMJA
11
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
no, if it is set to 'Text', everything would look exactly as he types (so4would look4and not4.000000), and the numbers could not be used for formulas.
– Aganju
May 28 '16 at 1:45
add a comment |
Check whether the Cell Format for the cell containing the formula that won't calculate to ensure it is NOT set to "Text". If it is, change it to General.
Then edit the cell and exit the cell. The formula should now resolve.
I recently encountered the same issue and this seems to happen only for cells of format Text. When it does occur, I haven't found a way for the formula to resolve without editing and exiting the cell. Copying and pasting the cell, whether pasting Formula, Format, or even Value still produces the unresolved formula.
answered May 26 '16 at 23:26
MJAMJA
11
Check whether the Cell Format for the cell containing the formula that won't calculate to ensure it is NOT set to "Text". If it is, change it to General.
Then edit the cell and exit the cell. The formula should now resolve.
I recently encountered the same issue and this seems to happen only for cells of format Text. When it does occur, I haven't found a way for the formula to resolve without editing and exiting the cell. Copying and pasting the cell, whether pasting Formula, Format, or even Value still produces the unresolved formula.
answered May 26 '16 at 23:26
MJAMJA
11
edited May 28 '16 at 1:37
answered May 26 '16 at 23:26
MJAMJA
11
answered May 26 '16 at 23:26
MJAMJA
11
answered May 26 '16 at 23:26
MJAMJA
11
11
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
no, if it is set to 'Text', everything would look exactly as he types (so4would look4and not4.000000), and the numbers could not be used for formulas.
– Aganju
May 28 '16 at 1:45
add a comment |
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
no, if it is set to 'Text', everything would look exactly as he types (so4would look4and not4.000000), and the numbers could not be used for formulas.
– Aganju
May 28 '16 at 1:45
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
A text is not a formula, and is not supposed to be calculated. Even if it looks like a formula.
– Aganju
May 27 '16 at 1:57
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
@Aganju - that is my point. If the format is set to Text, the OP's described symptoms would occur. I edited my post to make it clearer to change Format from Text to General.
– MJA
May 28 '16 at 1:39
no, if it is set to 'Text', everything would look exactly as he types (so
4 would look 4 and not 4.000000), and the numbers could not be used for formulas.– Aganju
May 28 '16 at 1:45
no, if it is set to 'Text', everything would look exactly as he types (so
4 would look 4 and not 4.000000), and the numbers could not be used for formulas.– Aganju
May 28 '16 at 1:45
add a comment |
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1063211%2fexcel-what-can-cause-manual-calculation-f9-to-stop-recalculating%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Any similarities in the cells it happens to?
– Raystafarian
Apr 8 '16 at 21:58
I've only found it in one portion of one worksheet. Specifically, it's just the iferror formula referencing cells on the same worksheet which seem to be recalculating normally.
– ELW
Apr 8 '16 at 22:01
What if you copy that sheet to another workbook? Does it still happen?
– Raystafarian
Apr 8 '16 at 22:02
Thanks. Copying the tab to a new workbook and manually calculating seems to calculate as expected.
– ELW
Apr 8 '16 at 22:05
I'd imagine either the book is corrupted or there's some VBA somewhere in the book or sheets module.
– Raystafarian
Apr 8 '16 at 22:05