In Excel, can you calculate just the selected cells?
Jennifer has a huge spreadsheet that takes forever to calculate. After calculating the entire workbook, the totals on SheetA have not updated. This short VBA macro will allow her to calculate only the selected range.
Table of Contents
(0:00) Problem: Entire workbook does not recalc
(0:55) Add Manual & Automatic Calculation to QAT
(1:12) Explanation of Manual Calculation Mode
(1:56) Open VBA Editor add use Selection.Calculate
(2:35) Saving Personal.XSLB
(2:47) Adding Macro to QAT in Excel
(3:20) Calculating selection in Excel
(3:50) Broken Workbooks
(5:00) Wrap up with Wally & Nancy
Jennifer has a huge spreadsheet that takes forever to calculate. After calculating the entire workbook, the totals on SheetA have not updated. This short VBA macro will allow her to calculate only the selected range.
Table of Contents
(0:00) Problem: Entire workbook does not recalc
(0:55) Add Manual & Automatic Calculation to QAT
(1:12) Explanation of Manual Calculation Mode
(1:56) Open VBA Editor add use Selection.Calculate
(2:35) Saving Personal.XSLB
(2:47) Adding Macro to QAT in Excel
(3:20) Calculating selection in Excel
(3:50) Broken Workbooks
(5:00) Wrap up with Wally & Nancy
Transcript of the video:
Learn Excel from MrExcel Podcast, Episode 2374. How To Calculate Only the Selected Cells.
This is a really unusual question.
Jennifer has a huge workbook, lots of sheets, lots of SUMIFs, formulas; it's taking four minutes to recalculate.
But she noticed that after the four minutes, the totals at the end of the first sheet, Sheet A, have not updated and have to recalculate again, so now it's taking eight minutes to do everything.
All right.
I'm going to show you a three-line macro that you can add to your Personal Macro Workbook.
If you've never used a Personal Macro Workbook before, go back and watch Video 2373.
There'll be an eye up here in the top right-hand corner popping up right now that you can go watch that.
All right, so here's what I have.
I just have a simple little sheet, lots of RANDBETWEENs here, and then totals at the right-hand side.
And of course, on the formulas tab, if we calculate now, it calculates...
everything, right?
So we're going to come into Calculation Options, we're going to right-click on Automatic, and say Add to Quick Access Toolbar.
Come back in, right-click on Manual, Add to Quick Access Toolbar.
Anytime that you're going to use Manual Calculation mode, you need to have these two check boxes up here, so you're aware if you're in Manual Calculation mode or not.
In Manual Calculation mode, you can change things, you can enter data and nothing will update.
See, none of those formulas recalculated, because we're in Manual Calculation mode.
It doesn't recalculate until you press F9 or Calculate Now, and then everything calculates.
In my case, it's quick, it doesn't matter.
But in Jennifer's case, every time she presses Calculate Now, it's going to take another four minutes.
So I'm going to work with Jennifer to figure out why those totals aren't updating, and how to make the spreadsheet faster.
But for right now, we just want to make life simpler and give Jennifer a way to update just these items.
On the Developer tab, you should have the Developer tab if you follow the instructions in the last video, click Visual Basic, View Project Explorer, which is CTRL+R, find your Personal.
xlsb.
If everything is minimized like this, click on modules, and you should have a module there with the Hello Macro that we recorded in 2173.
At this point, we don't need that Hello Macro anymore, so you can just select all those characters and delete.
We're going to create a new macro, three lines, it's really easy. Sub CalcSelection.
And what we're going to do is say Selection.Calculate, like that.
That's all you need. Click on Personal, and click Save.
And then, you can File, Close and Return to Microsoft Excel or Alt+Q.
Now we need an easy way to run that macro.
So up here, right-click Customize Quick Access Toolbar.
There's a list of popular commands, and the macro you just created, it's not in there.
You have to come to the dropdown and choose macros, and then find Calc.Selection, and add that to the Quick Access Toolbar. That's a goofy little icon.
The icons in here aren't much better.
I don't know, maybe we'll use the green check mark. Click Okay, whatever you want there.
Click Okay. All right.
So now what we should be able to do is calculate just this range.
I select that range and hit Calc.Selection, see, and only those cells updated.
And at this point, these cells are incorrect, so this is the state that Jennifer is finding yourself in after doing that four-minute calculation.
To update just the totals without causing the entire workbook to recalculate, select the totals, and then up here, Calculate Selection.
I've only seen two workbooks in the last 30 years that were big enough to get to the point where Excel couldn't fully recalculate. I'm sure there's more.
They are monster workbooks, right?
And Jennifer, congratulations for having one of those, right? I'm sorry you're in that position.
Hopefully we'll figure it out.
Excel calculates sheets alphabetically, and is really interesting; your left most sheet is the first one to get calculated, and then it's doing all those sheets on the right. And why doesn't it come back here?
I don't know.
I mean, that's a question that maybe Charles Williams or someone on the Excel team would know. I don't, all right?
But the situation is, your totals aren't getting updated.
And now, thanks to this macro, we have a fast way to update those...
anything.
Just calculate one section of the spreadsheet, so now you don't have to wait four minutes if you just need a part of it calculated.
At some point though, you're going to have to recalculate the whole thing, and that is Formulas, Calculate Now.
F9 is the shortcut for that, or I guess you could right-click and add to Quick Access Toolbar and Calculate Now.
All right.
Hey, I want to thank Jennifer for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel
This is a really unusual question.
Jennifer has a huge workbook, lots of sheets, lots of SUMIFs, formulas; it's taking four minutes to recalculate.
But she noticed that after the four minutes, the totals at the end of the first sheet, Sheet A, have not updated and have to recalculate again, so now it's taking eight minutes to do everything.
All right.
I'm going to show you a three-line macro that you can add to your Personal Macro Workbook.
If you've never used a Personal Macro Workbook before, go back and watch Video 2373.
There'll be an eye up here in the top right-hand corner popping up right now that you can go watch that.
All right, so here's what I have.
I just have a simple little sheet, lots of RANDBETWEENs here, and then totals at the right-hand side.
And of course, on the formulas tab, if we calculate now, it calculates...
everything, right?
So we're going to come into Calculation Options, we're going to right-click on Automatic, and say Add to Quick Access Toolbar.
Come back in, right-click on Manual, Add to Quick Access Toolbar.
Anytime that you're going to use Manual Calculation mode, you need to have these two check boxes up here, so you're aware if you're in Manual Calculation mode or not.
In Manual Calculation mode, you can change things, you can enter data and nothing will update.
See, none of those formulas recalculated, because we're in Manual Calculation mode.
It doesn't recalculate until you press F9 or Calculate Now, and then everything calculates.
In my case, it's quick, it doesn't matter.
But in Jennifer's case, every time she presses Calculate Now, it's going to take another four minutes.
So I'm going to work with Jennifer to figure out why those totals aren't updating, and how to make the spreadsheet faster.
But for right now, we just want to make life simpler and give Jennifer a way to update just these items.
On the Developer tab, you should have the Developer tab if you follow the instructions in the last video, click Visual Basic, View Project Explorer, which is CTRL+R, find your Personal.
xlsb.
If everything is minimized like this, click on modules, and you should have a module there with the Hello Macro that we recorded in 2173.
At this point, we don't need that Hello Macro anymore, so you can just select all those characters and delete.
We're going to create a new macro, three lines, it's really easy. Sub CalcSelection.
And what we're going to do is say Selection.Calculate, like that.
That's all you need. Click on Personal, and click Save.
And then, you can File, Close and Return to Microsoft Excel or Alt+Q.
Now we need an easy way to run that macro.
So up here, right-click Customize Quick Access Toolbar.
There's a list of popular commands, and the macro you just created, it's not in there.
You have to come to the dropdown and choose macros, and then find Calc.Selection, and add that to the Quick Access Toolbar. That's a goofy little icon.
The icons in here aren't much better.
I don't know, maybe we'll use the green check mark. Click Okay, whatever you want there.
Click Okay. All right.
So now what we should be able to do is calculate just this range.
I select that range and hit Calc.Selection, see, and only those cells updated.
And at this point, these cells are incorrect, so this is the state that Jennifer is finding yourself in after doing that four-minute calculation.
To update just the totals without causing the entire workbook to recalculate, select the totals, and then up here, Calculate Selection.
I've only seen two workbooks in the last 30 years that were big enough to get to the point where Excel couldn't fully recalculate. I'm sure there's more.
They are monster workbooks, right?
And Jennifer, congratulations for having one of those, right? I'm sorry you're in that position.
Hopefully we'll figure it out.
Excel calculates sheets alphabetically, and is really interesting; your left most sheet is the first one to get calculated, and then it's doing all those sheets on the right. And why doesn't it come back here?
I don't know.
I mean, that's a question that maybe Charles Williams or someone on the Excel team would know. I don't, all right?
But the situation is, your totals aren't getting updated.
And now, thanks to this macro, we have a fast way to update those...
anything.
Just calculate one section of the spreadsheet, so now you don't have to wait four minutes if you just need a part of it calculated.
At some point though, you're going to have to recalculate the whole thing, and that is Formulas, Calculate Now.
F9 is the shortcut for that, or I guess you could right-click and add to Quick Access Toolbar and Calculate Now.
All right.
Hey, I want to thank Jennifer for sending that question in, and I want to thank you for stopping by.
We'll see you next time for another net cast from MrExcel
