Discover Why You Change A Cell In Excel But The Forumlas Do Not Calculate With This Excel Tutorial

Page content

Strategy: Someone put the worksheet in manual calculation mode. Try hitting F9 to calculate, as shown in Fig. 469.

• Pressing F9 will recalculate all cells that have changed since the last calculation, plus all formulas dependent on those cells in all open workbooks.

• For quicker calculation, use Shift+F9. This will limit the calculation to the current worksheet.

• For thorough calculation, use Ctrl+Alt+F9. This calculates all formulas in all open workbooks, whether Excel thinks they have changed or not.

• Finally, there is the Ctrl+Shift+Alt+F9. This will rebuild the list of dependent formulas and then do a thorough calculation.

Additional Details: Manual Calculation mode can be changed for a workbook. Go to Tools – Options – Calculation to see the various calculation options, as shown in Fig. 470.

Gotcha: Before you go back to Automatic mode, ask the person who created the worksheet why it is in Manual Calculation mode. Sometimes you will find a spreadsheet with tens of thousands of calculations that takes 30-45 seconds to calculate. This is very frustrating when the system pauses for 45 seconds after every single data entry. If you have a lot of data entry to do, a standard strategy is to use Manual Calculation mode. In this mode you can make several changes and then hit F9 to calculate.

Summary: Be aware that Excel offers a manual calculation mode. If you have a spreadsheet that takes too long to calculate after every data entry, you might consider using Manual Calculation mode temporarily, doing the data entry, and then switching back to Automatic Calculation mode.

Commands Discussed: F9 to calculate; Tools – Options – Calculate

Images

Fig. 469

Fig. 470

References and Additional Resources

If you’re looking for more tips and tutorials, check out 91 Tips for Calculating With Microsoft Excel. This collection of easy-to-follow guides shows how to customize charts and graphs, different ways to make complex spreadsheets easier to update, and even how to play games like Craps in Excel.

Other Resources:

Microsoft Excel Official Site, https://office.microsoft.com/en-us/excel/

Bill Jelen, Microsoft Excel 2010 In Depth, Available from Amazon.com.