When to Use Automatic vs Manual Calculations in Excel

When to Use Automatic vs Manual Calculations in Excel

AUTHOR:  JASON MORRELL 

Jason Morrell

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

Alternatively, 

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?

Please feel free to post any questions you have into the comments below. And before you leave this page, I want to give you one quick little action item! 

ACTION ITEM: Comment below with ONE thing you picked up from this post and share how it has helped, or will help, you. It's good to share your successes because it encourages others and provides an instant boost.

Enjoy the rest of your 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!

SHARE

Suggested Posts

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