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: https://www.mrexcel.com/products/latest/
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