Describe how you can control Excel recalculating Formulas. Discuss the Automatic, Automatic except for data tables, and Manual options.

Question

What will be an ideal response?

Answer 1

Excel automatically recalculates any formula when the input cells to that formula are changed. This process works seamlessly when the workbook is small and few formulas are involved. However, when the workbook increases in size-think several worksheets with thousands of rows with multiple formulas for each row-this process slows noticeably as Excel recalculates tens of thousands of formulas. One way to solve this problem is to change how Excel handles recalculation. You can choose from the following options:• Automatic-All dependent formulas are recalculated every time a change is made to their input cells.• Automatic except for data tables-Automatic recalculation is done for all dependent formulas except for formulas within data tables created with the Data Tables feature.• Manual-No recalculation is done except when manually requested by the user.?To manually recalculate the contents of the workbook, press the F9 key or click the Calculate Now button in the Calculation group on the Formulas tab. You can define calculation options on the Formulas tab in the Excel Options dialog box, or by clicking the Calculation Options button in the Calculation group on the Formulas tab. Another way to speed up a large workbook is to reduce the number of iterative calculations used in performing what-if analyses involving goal seeking. You can set this value using the Enable iterative calculation check box on the Formulas tab in the Excel Options dialog box.