How to recalculate only one sheet in Excel to avoid waiting while the whole spreadsheet recalculates.
- For updated video clips in structured Excel courses with practical example files, have a look at our MS Excel online training courses . You can even try the Free MS Excel tips and tricks course.
- To see if this video matches your skill level (see the suggested skill score below) do our free MS Excel skills assessment.
- If you are based in South Africa look at the live courses we offer in Johannesburg and Cape Town.
- - 02:55
- - Skill Level: 4
- - What's my skill level
Recalculate only one sheet in Excel
In Excel you can specify how you want calculations to run – automatically or manually. You can also recalculate only one sheet at a time.
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.