Start networking and exchanging professional insights

Register now or log in to join your professional community.

Follow

What is the function of "Calculate Now" in Microsoft Excel and what is the shortcut key of "Calculate Now"?

user-image
Question added by danish shafiq , Accountant , Anayat Fan
Date Posted: 2016/06/14
Ghada Eweda
by Ghada Eweda , Medical sales hospital representative , Pfizer pharmaceutical Plc.

The workbook calculation is set to Automatic on all the computers , that's the first thing we check. To check the setting, click the Formulas tab on the Excel Ribbon, and click the Calculate Options drop down.

formulacalculate00

Or, go to the Excel Options window, and click Formulas.

formulacalculate01

Force the Calculation

Even if the Calculation option is set for Manual, you can use a Ribbon command or keyboard shortcut to force a calculation. Click the Formulas tab on the Excel Ribbon, and click Calculate Now or Calculate Sheet. In the tooltip that is shown in the screen shot below, you can see that the shortcut for Calculate Sheet is Shift + F9.

formulacalculate03

We tried using the Calculation shortcuts, including Ctrl + Alt + Shift + F9 (that one recalculates everything, and starts your car).

formulacalculate04

However, none of the shortcuts had any effect on the non-calculating cells in this workbook. I also tried a macro that did a full calculation, and that didn't work either. Sigh.

Abdul Khalique
by Abdul Khalique , Finance Manager , Value Real Estate & Construction

Calculate Now button is to manually recalculate all open worksheets, including data tables, and update all open chart sheets.

Shortcut Key of Calculate Now is F9.

Shameer Nazir Madari
by Shameer Nazir Madari , Assistant Finance Manager , METAL AND RECYCLING COMPANY K.S.C. (PUBLIC)

When working on large spreadsheets in Excel 2013, particularly on a slow computer, the calculation process can take a long time to complete. An easy way to "cut the wait time" is to change the calculation mode in Excel. There are different types of calculation available, why an Excel user might want to change from one mode to another and some of the pitfalls to watch out for with manual calculation.

 

Types of Calculation Available in Excel

There are three types of calculation available in Excel. The default setting is "Automatic." In this mode, all formulas in the spreadsheet recalculate whenever any data is changed.

In small files, the time lag for recalculation is unnoticeable. For large files with many data tables, the time lag can feel enormous. For this reason, the "Automatic except for data tables" option can be useful. Data tables suck up system resources. Multiple data tables in a spreadsheet can take a few seconds each to refresh with every change made to the spreadsheet.

The "Manual" calculation holds all updates until you are ready for them. The shortcut key for updating the entire file is "F9," also called "Calculate Now." The downside of this option is calculation updates can be forgotten and incorrect data displayed.

Using the Ribbon to Change Calculation Modes

The fastest place to change the calculation mode is on Excel's Formula ribbon. In the Calculation grouping, on the right side of the ribbon, is a drop-down button for "Calculation Options." Choose an option that works best for the current file. There are two other important buttons in the Calculation grouping. "Calculate Now" updates the entire workbook. "Calculate Worksheet" updates only the current sheet, which is much faster in a large file.

Using the Menu to Change Calculation Options

For those who prefer using their menu to make the change, calculation modes are located in the Excel Options dialog box on the formula tab. This can be found in the File menu.

Microsoft considers the option under Manual calculation to "Recalculate workbook before saving" the fourth calculation mode, but it is unavailable from the Ribbon. Keeping the box checked is invaluable for those times when the Manual setting is forgotten.

For those who prefer to use the keyboard, "Alt-F" opens the File Menu, then "T" chooses Excel Options. Click the down arrow to navigate to Formulas in the dialog box. Finally, choose a calculation mode: "Alt-A" for Automatic, "Alt-D" for Automatic with Data Tables or "Alt-M" for Manual calculation.

Important Considerations about Calculation Modes

 

It is vital to remember that a change to the calculation mode affects all open files, regardless of how they were originally saved. Templates are the only exception to this; they hold the mode of calculation they were created with, until they are saved again. For example, if "Sheet 1" of File A is active but File B is also open, when the mode of calculation is changed in File A, it affects every sheet in both files. For this reason, if you plan to change the calculation mode, close any unnecessary files first.

QASIM KHAN
by QASIM KHAN , Admin & Finance Assistant , English Ville Academy

i agree with your answer Ghada Eweda

thank you Mr. Danish for your invitation. Well said Miss Ghada Eweda. 

Sarfaraz khan
by Sarfaraz khan , Environmental & Sustainability Manager , NEOM Kingdom of Saudi Arabi

I agree with ghada ewada , set all funtion of caculation once a time than it will be helpful during caculation.

manseer muhammed ali
by manseer muhammed ali , Accountant General , Royal Lighting L.L.C & Royal Furnishing LLC

i here by agree with Miss Ghada, i cant give a better answer than her

Giovanni Taborada
by Giovanni Taborada , Network Engineer , Amwaj

Hi, thanks for the invitation. Nothing to add.. Answers are already been added.

More Questions Like This

Do you need help in adding the right keywords to your CV? Let our CV writing experts help you.