Automatic vs Manual Calculations In Excel
Assign a 'primary' menu

Automatic vs Manual Calculations in Excel

Automatic vs manual calculations in Excel

Did you know ... ?

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.

Live energy prices were fed into the system every second, which were eventually fed into his spreadsheet. Custom software analysed the data and automatically purchased or sold power to wholesalers many times each day (a bit like like stocks and shares) in order to make a profit for the company. 

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.

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.

Consider Manual Calculations instead

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

Figure 01: Switch to manual calculations

Alternatively, click the Formulas tab and you’ll find Calculation Options on the right side.

Now, the spreadsheet will only update when you say so.

The Update/Refresh key is F9, or you can click Calculate Now on the right side of the Formulas tab.

Did this clear something up for you? If so, please share on your socials and/or post a brief comment below.

Likewise, if something didn't make sense or you have a question, pop it into the comments below. I'll be waiting ...

Cheers. Here's to your learning and success. Enjoy the rest of your day.

Jason Morrell blog signature
Jason Morrell blog signature
30 Excel Power Tips


Discover the shortcuts and tricks used by the pros

Office Mastery is the digital arm and official blog of Two Rivers Software Training
Copyright © 2018 Two Rivers Software Training. All Rights Reserved Privacy Terms Contact
Like us on facebook Follow us on Twitter Watch us on YouTube Connect with Jason on LinkedIn