Reading Time: minutes remaining

When to Use Automatic vs Manual Calculations in Excel

Jason Morrell

by Jason Morrell 
May 10, 2017

Select Dynamic field

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

Switch to manual calculations


1.  Click the Formulas tab

2.  Click Calculation Options on the right side. 

3.  Choose Manual.

Choosing manual calculations in Excel

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.

Jason Morrell blog signature
About Jason Morrell

About the author

Jason Morrell

Jason loves to simplify the hard stuff, cut the fluff and share what actually works. Things that make a difference. Things that slash hours from your daily work tasks. He runs a software training business in Queensland, Australia, lives on the Gold Coast with his wife and 4 kids and often talks about himself in the third person!


more from

Office Mastery logo
Read More
Excel Screen: A Beginners Tour
Read More
5 Excel Functions for Beginners: Simplify Your Formulas and Release the Magic


{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}