Excel is set up to refresh calculations automatically and for most people that is perfect.
But there are situations where you NEED to switch to refreshing calculations manually (on-demand) just to stay sane.
In this short post, you'll see some reasons for switching from automatic to manual calculations as well as where to find the setting.
1. How on earth can you run out of rows in Excel?
Did you know that each Excel worksheet has 1, 048, 576 rows?
So imagine my surprise when one day I met someone who ran out of rows!
And not only that, this person ran out of rows every 10 -11 days!
Here's what was happening.
Special software fed live energy prices into a spreadsheet every second. The data was then monitored and power was purchased and sold automatically to wholesalers for a cent or two profit per unit, thousands of times each day.
And every few days the rows ran out!
By no means is this normal. In fact, hardly anyone reaches that point.
It's not just the sheer volume of data, but when a spreadsheet gets beyond a certain size it becomes frustratingly S-L-O-W. Depending on the complexity of the spreadsheet, this may occur with as few as 10,000 rows.
2. Why do large spreadsheets run slow?
Excel has a default setting that refreshed the worksheet (i.e. recalculates every formula) whenever any change is made.
Even when you make a small change, you may have to wait a few seconds, or even a few minutes for Excel to complete its processing.
That's when you should turn AUTOMATIC CALCULATIONS off.
3. How to switch from automatic to manual calculations in Excel
You have the option to switch off automatic calculations and control things manually. This speeds things up considerably and gives you more control.
To switch to a manual calculation:
1. Click the File tab.
2. Choose Options in the left sidebar.
3. In the Excel Options dialog, choose Formulas on the left sidebar.
4. In the Calculation Options section, choose Manual.
Switch to manual calculations
Alternatively,
1. Click the Formulas tab
2. Click Calculation Options on the right side.
3. Choose Manual.
To refresh your spreadsheet (recalculate manually on demand)
1. Click the Formulas tab.
2. Click Calculate Now (on the right side of the ribbon)
... or press F9 (which is a general key to update / refresh the workbook).
4. What next?
I hope you found plenty of value in this post. I'd love to hear your biggest takeaway in the comments below together with any questions you may have.
Have a fantastic day.
About the author
Jason Morrell
Jason Morrell is a professional trainer, consultant and course creator who lives on the glorious Gold Coast in Queensland, Australia.
He helps people of all levels unleash and leverage the power contained within Microsoft Office by delivering training, troubleshooting services and taking on client projects. He loves to simplify tricky concepts and provide helpful, proven, actionable advice that can be implemented for quick results.
Purely for amusement he sometimes talks about himself in the third person.
SHARE
