Transcripts for the above video clip:

ShiftF9

In Excel you can specify how you want calculations to run – automatically or manually. On the worksheet we have a calculation, if I change the top number to a 2, the answer automatically changes. If we go to another sheet and change the top number to a 3, the same thing happens and the answer automatically changes. What we can do in Excel if you are working with very large spreadsheets, rather than recalculations happening every time you touch a cell, you want Excel to recalculate manually when you tell it to. You can achieve this by switching to the “Manual calculation mode”. Click on the Open button in the top left hand corner of the screen. A screen drops and you must choose “Excel Options” and under “Formula”, you’ll see there are Calculation Options. At the moment it is set for “Automatic except for data tables”. You must switch it over to “Manual” and click OK. What this means is that if I now change the top number to 2 – you’ll see nothing happens to the total. However if I push my F9 key, the calculation happens. If I go back to another sheet and change the top number to a 4 and then push the F9 key, you’ll see the total is changed to 8 which is correct. If I now go to the worksheet shown and change the second figure to 2 and on the second sheet change the second number to a 4, in both cases the total does not change. If I now push the F9 key you’ll see that both worksheets are now correctly totaled. So if you push F9 Excel manually recalculates the entire spreadsheet. That is normally the way you want your spreadsheet to react. Sometimes the spreadsheet is so large that when you push F9 it takes a long time because Excel goes through every worksheet to recalculate. There is a trick one can do. If I change the third number in the worksheet shown to 2 the total is now incorrect at 5 and on the other weeksheet I change the third number to 4 – you’ll see again the total is incorrect. If I now go to a worksheet and instead of pushing F9 I hold the shift key down and then push F9, you’ll see the total is correct. However if you go to the other worksheet, you’ll see it is still incorrect number because the recalculation only happened on the first sheet. If you now push shiftF9 on the worksheet now shown, you’ll see only that sheet recalculates.