Microsoft Excel Tutorial: Which values are not yet calculated in Manual Calculation Mode?
In August 2023, Microsoft is adding a new feature to Microsoft 365 Excel. The Format Stale Values will alert you when a cell needs to be recalculated. This is an application-level setting. When you turn it on, it will work for all open workbooks.
To download this workbook: Excel Stale Value Formatting - Episode 2613 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Excel Stale Value Formatting
(0:23) Excel Manual Calculation Mode
(0:36) Stale Value Markers
(0:56) On-Grid UI choices
(1:09) Customize Stale Value Formatting?
(1:47) Why are new values stale?
(3:14) Stale Value in Automatic Mode
(3:40) Excel interrupt calculation with Esc
(4:25) What's your opinion?
In August 2023, Microsoft is adding a new feature to Microsoft 365 Excel. The Format Stale Values will alert you when a cell needs to be recalculated. This is an application-level setting. When you turn it on, it will work for all open workbooks.
To download this workbook: Excel Stale Value Formatting - Episode 2613 Sample Files - MrExcel Publishing
Table of Contents
(0:00) Excel Stale Value Formatting
(0:23) Excel Manual Calculation Mode
(0:36) Stale Value Markers
(0:56) On-Grid UI choices
(1:09) Customize Stale Value Formatting?
(1:47) Why are new values stale?
(3:14) Stale Value in Automatic Mode
(3:40) Excel interrupt calculation with Esc
(4:25) What's your opinion?
Transcript of the video:
Well, I'll be interested to get your opinion on this new feature, Stale Value Formatting.
This just arrived today to my Insider's Beta.
I know they're rolling it out slowly and what I have here is I have a nice little spreadsheet with some input cells over here on the left-hand side. And then formulas to the right, formulas out here in the total.
And under Calculation Options, I'm going to switch to Manual and toggle on this brand new feature called Format Stale Values.
So the idea is you have a large spreadsheet, you're working in Manual Calculation Mode and it reminds you that some items have not been recalculated. So here I'll change Ed from 460 to 900.
I'll press Enter.
And you see that they mark all of these in Strikethrough and this cell down here in Strikethrough to say that they are not currently correct values.
If you select one of these, the little on grid UI says that it's a stale value and you can either calculate now or switch to automatic calculation.
You could also turn it off.
I'm not sure that that is a good option to calculate now and everything comes back.
Question number one, what if we don't want it to be Strikethrough? Too bad.
That's what it is right now. It's a version one feature.
If there's a lot of hue and cry, maybe Microsoft will give us some way to customize that, but for now, it's Strikethrough. And yes, I use Strikethrough a lot.
Ctrl+5 is one of my most used shortcuts. So do I like the fact that it's Strikethrough?
No.
Would I wish it was a different color Strikethrough? Yes.
Just give me some way to customize it. But I get it.
Right now they're just trying to roll it out and that's good.
Okay, now here's the thing that is a little mysterious to me and I don't quite understand about stale values, I guess.
So I'm just going to create a new item here for Jake, and we'll put in a value of 300 or something like that, and the auto complete is going to kick in here. It's going to fill in those formulas.
But what I don't quite understand is these numbers are all actually correct.
When it filled those in, it didn't just copy the values from here down.
So why is this being marked as stale? Because they're the correct numbers.
I'll press F9. And see nothing changed in this row, right?
Those were all correct. And even here, let's do the auto sum.
So Alt equals.
When I press Enter, that number is the correct number, but it still gets crossed out, which is strange to me.
That doesn't seem like it should be a stale value. It was just calculated.
So I don't know if that's a bug or if that's by design.
We'll have to check with the Excel team to see what they expected that to be.
I get it here. Let's put in 100 here.
And then a simple little formula plus 10 plus the number to the left of us.
I get it when I copy this across, those are all going to be 110.
And yes, those are all stale values.
But that first one, that first one shouldn't be stale. It was just calculated as I entered the formula.
So not quite sure the actual reasoning there.
And by the way, it is possible to have stale value formatting show up in automatic. Let's switch to another workbook.
Okay, I've switched to another workbook.
This one has a lot of formulas and I've switched back to Automatic Mode. Format Stale Values is still on.
So here I have some ran between at the top and when I press F9, those are going to recalculate of course, and then millions of formulas have to recalculate.
You can see the progress down here in the bottom.
I'm going to interrupt the recalc with the Escape key. So I press F9 and it's calculating up to 1831.
I press Escape, and even though I'm in Automatic Mode, I still get the stale value markers just to let me know that I was impatient and stopped the recalculation, although eventually it does recalc in the background and the stale markers go away.
So just be aware that even if you never go into Manual Calculation Mode, it's still possible to have this format, stale values show up.
I had an old coworker 25 years ago who got stung by this one time, stopped a long calculation and then didn't realize that it never finished calculating.
So stale values would've helped in that case.
All right, let me know what you think down in the YouTube comments below.
I want to thank Joe and the whole Excel team for giving us this great new feature. And I want to thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
This just arrived today to my Insider's Beta.
I know they're rolling it out slowly and what I have here is I have a nice little spreadsheet with some input cells over here on the left-hand side. And then formulas to the right, formulas out here in the total.
And under Calculation Options, I'm going to switch to Manual and toggle on this brand new feature called Format Stale Values.
So the idea is you have a large spreadsheet, you're working in Manual Calculation Mode and it reminds you that some items have not been recalculated. So here I'll change Ed from 460 to 900.
I'll press Enter.
And you see that they mark all of these in Strikethrough and this cell down here in Strikethrough to say that they are not currently correct values.
If you select one of these, the little on grid UI says that it's a stale value and you can either calculate now or switch to automatic calculation.
You could also turn it off.
I'm not sure that that is a good option to calculate now and everything comes back.
Question number one, what if we don't want it to be Strikethrough? Too bad.
That's what it is right now. It's a version one feature.
If there's a lot of hue and cry, maybe Microsoft will give us some way to customize that, but for now, it's Strikethrough. And yes, I use Strikethrough a lot.
Ctrl+5 is one of my most used shortcuts. So do I like the fact that it's Strikethrough?
No.
Would I wish it was a different color Strikethrough? Yes.
Just give me some way to customize it. But I get it.
Right now they're just trying to roll it out and that's good.
Okay, now here's the thing that is a little mysterious to me and I don't quite understand about stale values, I guess.
So I'm just going to create a new item here for Jake, and we'll put in a value of 300 or something like that, and the auto complete is going to kick in here. It's going to fill in those formulas.
But what I don't quite understand is these numbers are all actually correct.
When it filled those in, it didn't just copy the values from here down.
So why is this being marked as stale? Because they're the correct numbers.
I'll press F9. And see nothing changed in this row, right?
Those were all correct. And even here, let's do the auto sum.
So Alt equals.
When I press Enter, that number is the correct number, but it still gets crossed out, which is strange to me.
That doesn't seem like it should be a stale value. It was just calculated.
So I don't know if that's a bug or if that's by design.
We'll have to check with the Excel team to see what they expected that to be.
I get it here. Let's put in 100 here.
And then a simple little formula plus 10 plus the number to the left of us.
I get it when I copy this across, those are all going to be 110.
And yes, those are all stale values.
But that first one, that first one shouldn't be stale. It was just calculated as I entered the formula.
So not quite sure the actual reasoning there.
And by the way, it is possible to have stale value formatting show up in automatic. Let's switch to another workbook.
Okay, I've switched to another workbook.
This one has a lot of formulas and I've switched back to Automatic Mode. Format Stale Values is still on.
So here I have some ran between at the top and when I press F9, those are going to recalculate of course, and then millions of formulas have to recalculate.
You can see the progress down here in the bottom.
I'm going to interrupt the recalc with the Escape key. So I press F9 and it's calculating up to 1831.
I press Escape, and even though I'm in Automatic Mode, I still get the stale value markers just to let me know that I was impatient and stopped the recalculation, although eventually it does recalc in the background and the stale markers go away.
So just be aware that even if you never go into Manual Calculation Mode, it's still possible to have this format, stale values show up.
I had an old coworker 25 years ago who got stung by this one time, stopped a long calculation and then didn't realize that it never finished calculating.
So stale values would've helped in that case.
All right, let me know what you think down in the YouTube comments below.
I want to thank Joe and the whole Excel team for giving us this great new feature. And I want to thank you for stopping by.
We'll see you next time for another netcast for MrExcel.
If you like these videos, please down below, Like, Subscribe and Ring the Bell.
Feel free to post any questions or comments down in the comments below.
