Microsoft Excel Tutorial: Make workbook calculate faster by toggling off the ForceFullCalculation setting.
Welcome back to MrExcel! Today, I've got an incredible tip that can make your Excel workbooks run dramatically faster. Have you ever heard of ActiveWorkbook.ForceFullCalculation = False? Probably not, because it's a setting that's rarely documented. But it can make a huge difference in calculation speed, as shown in this example sent by Matt from Chicago, a member of the MrExcel message board.
Matt provided us with a file containing 7,000 rows and 27 columns, which is just a fraction of the size of his client's actual files. With numerous complex formulas, including XLOOKUPs and SUMIFS based on the same table, even simple tasks like inserting rows can take forever. Watch as I demonstrate the sluggish performance when I try to insert a couple of rows – it takes ages!
Now, here's Matt's game-changing tip. By using a little-known VBA setting, you can speed up these calculations instantly. Open the VBA editor with Alt+F11, then use Ctrl+G to access the Immediate Window. Type ActiveWorkbook.ForceFullCalculation = False, and watch the magic happen. When we try inserting those same rows again, it's much faster!
However, Matt also warns of two important caveats. First, enabling this setting stops Excel from tracking cell changes for smart recalculation, which isn't ideal for sharing with your boss or client. Second, while enabling the setting is immediate, turning it off requires a restart of Excel for the change to take effect. Despite these drawbacks, if you're working on a large workbook and need to cut down on delays, this trick is definitely worth trying.
A huge thanks to Oaktree aka Matt from Chicago for sharing this incredible tip! If you found this video helpful, give it a thumbs up, and don't forget to subscribe for more Excel tips and tricks. We'll see you next time on another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Problem Statement: Workbook with many formulas calculates slowly
(0:12) Workbook with many SUMIFS and XLOOKUP
(0:41) Twenty Seconds to Insert Row
(1:03) 101 Seconds to Delete Two Rows
(1:13) Go to Immediate Window in VBA
(1:23) Change ActiveWorkbook.ForceFullCalculation to False
(1:33) Five seconds to insert rows and 7 seconds to Undo
(1:54) Why not to use ForceFullCalculation All the Time
(2:25) Wrap-up
(2:40) Like, Subscribe, Ring the Bell
This video answers these common search terms:
Speed up Excel calculations
Excel VBA speed tip
ActiveWorkbook.ForceFullCalculation
Improve Excel performance
Fast Excel calculations VBA
Excel calculation optimization
Reduce Excel lag time
Excel VBA tricks for speed
Boost Excel processing time
Excel large file performance boost
Welcome back to MrExcel! Today, I've got an incredible tip that can make your Excel workbooks run dramatically faster. Have you ever heard of ActiveWorkbook.ForceFullCalculation = False? Probably not, because it's a setting that's rarely documented. But it can make a huge difference in calculation speed, as shown in this example sent by Matt from Chicago, a member of the MrExcel message board.
Matt provided us with a file containing 7,000 rows and 27 columns, which is just a fraction of the size of his client's actual files. With numerous complex formulas, including XLOOKUPs and SUMIFS based on the same table, even simple tasks like inserting rows can take forever. Watch as I demonstrate the sluggish performance when I try to insert a couple of rows – it takes ages!
Now, here's Matt's game-changing tip. By using a little-known VBA setting, you can speed up these calculations instantly. Open the VBA editor with Alt+F11, then use Ctrl+G to access the Immediate Window. Type ActiveWorkbook.ForceFullCalculation = False, and watch the magic happen. When we try inserting those same rows again, it's much faster!
However, Matt also warns of two important caveats. First, enabling this setting stops Excel from tracking cell changes for smart recalculation, which isn't ideal for sharing with your boss or client. Second, while enabling the setting is immediate, turning it off requires a restart of Excel for the change to take effect. Despite these drawbacks, if you're working on a large workbook and need to cut down on delays, this trick is definitely worth trying.
A huge thanks to Oaktree aka Matt from Chicago for sharing this incredible tip! If you found this video helpful, give it a thumbs up, and don't forget to subscribe for more Excel tips and tricks. We'll see you next time on another netcast from MrExcel!
Buy Bill Jelen's latest Excel book: MrExcel 2024 Igniting Excel
Table of Contents
(0:00) Problem Statement: Workbook with many formulas calculates slowly
(0:12) Workbook with many SUMIFS and XLOOKUP
(0:41) Twenty Seconds to Insert Row
(1:03) 101 Seconds to Delete Two Rows
(1:13) Go to Immediate Window in VBA
(1:23) Change ActiveWorkbook.ForceFullCalculation to False
(1:33) Five seconds to insert rows and 7 seconds to Undo
(1:54) Why not to use ForceFullCalculation All the Time
(2:25) Wrap-up
(2:40) Like, Subscribe, Ring the Bell
This video answers these common search terms:
Speed up Excel calculations
Excel VBA speed tip
ActiveWorkbook.ForceFullCalculation
Improve Excel performance
Fast Excel calculations VBA
Excel calculation optimization
Reduce Excel lag time
Excel VBA tricks for speed
Boost Excel processing time
Excel large file performance boost
Transcript of the video:
This is crazy as a setting I've never even heard of.
The ActiveWorkbook.ForceFullCalculation = False makes this workbook dramatically faster.
Let's take a look.
Hey, shout out to Oaktree from the MrExcel message board, Matt from Chicago.
He sent in this example with an amazing way to speed up this calculation. He has a file here, 7,000 rows, 27 columns.
This is one tenth the size of one of his actual client files.
A lot of expensive formulas, some XLOOKUPs and SUMIFS that are based on the same table.
He has a good reason for doing that.
And he notes if he just tries to do something simple like insert a few rows, I'll do Alt+I, R and we'll just watch the blue spinning circle there.
It takes forever to add a couple of rows to this Excel table.
And Matt has a setting in VBA that he says he can find documented nowhere.
That will make this be instantaneous. Let's let this finish.
Alright, so there we go. I will undo.
Alright, so here's Matt's suggestion. VBA, that's Alt+F11.
Press CTRL G for the immediate window.
We are going to set active workbook force full calculation equal to true. That setting takes effect immediately.
We'll close VBA and we'll try to insert the exact same two rows, so Alt+IR Much, much faster.
And here let's even undo that with Ctrl+Z Again, dramatically faster. ActiveWorkbook.ForceFullCalculation.
Okay, now Matt passes along two important gotchas here.
One, if you set it to true, which we just did, tells Excel to stop keeping track of which cells changed when it applies to smart recalculation algorithm.
That's something you probably don't want enabled most of the time.
Like before you send this on to your boss or to the client.
And the other problem, toggling from False back to True happens immediately.
But going backwards from True to False requires you to restart Excel and reopen the file for it to take effect.
But if you're simply trying to work in the workbook, rather than have one minute delay for inserting a couple of rows, having just a few seconds, it's probably worth it to turn this on and turn it back off again.
It's ActiveWorkbook.ForceFullCalculation, set that equal to true and your workbook might speed up dramatically.
Well hey, thanks to Oaktree, Matt in Chicago for sending that in. Thanks to you for stopping by.
We'll see you next time for another netcast from 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.
The ActiveWorkbook.ForceFullCalculation = False makes this workbook dramatically faster.
Let's take a look.
Hey, shout out to Oaktree from the MrExcel message board, Matt from Chicago.
He sent in this example with an amazing way to speed up this calculation. He has a file here, 7,000 rows, 27 columns.
This is one tenth the size of one of his actual client files.
A lot of expensive formulas, some XLOOKUPs and SUMIFS that are based on the same table.
He has a good reason for doing that.
And he notes if he just tries to do something simple like insert a few rows, I'll do Alt+I, R and we'll just watch the blue spinning circle there.
It takes forever to add a couple of rows to this Excel table.
And Matt has a setting in VBA that he says he can find documented nowhere.
That will make this be instantaneous. Let's let this finish.
Alright, so there we go. I will undo.
Alright, so here's Matt's suggestion. VBA, that's Alt+F11.
Press CTRL G for the immediate window.
We are going to set active workbook force full calculation equal to true. That setting takes effect immediately.
We'll close VBA and we'll try to insert the exact same two rows, so Alt+IR Much, much faster.
And here let's even undo that with Ctrl+Z Again, dramatically faster. ActiveWorkbook.ForceFullCalculation.
Okay, now Matt passes along two important gotchas here.
One, if you set it to true, which we just did, tells Excel to stop keeping track of which cells changed when it applies to smart recalculation algorithm.
That's something you probably don't want enabled most of the time.
Like before you send this on to your boss or to the client.
And the other problem, toggling from False back to True happens immediately.
But going backwards from True to False requires you to restart Excel and reopen the file for it to take effect.
But if you're simply trying to work in the workbook, rather than have one minute delay for inserting a couple of rows, having just a few seconds, it's probably worth it to turn this on and turn it back off again.
It's ActiveWorkbook.ForceFullCalculation, set that equal to true and your workbook might speed up dramatically.
Well hey, thanks to Oaktree, Matt in Chicago for sending that in. Thanks to you for stopping by.
We'll see you next time for another netcast from 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.
