Today I have a tip for you if you work with very large Excel models. Those, which take minutes or up to hours to calculate. You can calculate selected cells only.
A bit of theory of Excel calculations
Plainly speaking, Excel calculates permanently (with some exceptions) all changed cells and all dependent cells. You can switch the calculation mode to stop calculating in general in-between. This is called “Manual calculation”. Once you have done this, you can
- Recalculate all changed cells by pressing F9 on the keyboard,
- force a full recalculation of all formulas and functions by pressing Ctrl + Alt + F9 on the keyboard or
- only calculate the current worksheet by pressing Shift + F9.
Interest in more theory? In this article you can read more about the calculation modes.
So, what if you only want to recalculate a small amount of cells?
Switch to manual calculation mode first
Before you can use the technique to only calculate smaller parts of your Excel model, you have to switch Excel to manual calculation mode.
On the Formulas ribbon, go to Calculation Options and make sure that “Manual” is checked.
Please keep in mind: Excel now does not permanently re-calculate. That means, result might not be updated.
Calculate one cell only
Once you are in manual calculation mode it’s quite simple to only calculate one cell:
- Enter the cell you want to recalculation: Select the cell only and press F2 on the keyboard.
- Confirm by pressing Enter on the keyboard.
Calculate selected cells only
But what if you don’t want to recalculate one cell only, but rather calculate cells you have currently selected? You basically have two options: Use VBA (it’s simple, no need to insert a module) or use an Excel add-in.
Calculate selected cells by using VBA
The VBA editor has a feature called “Immediate”. It by default below your code and we can use it here.
- Select the cells you want to calculate.
- Open the VBA editor window by pressing Alt + F11 on the keyboard.
- Type into the Immediate field “Selection.Calculate” and press Enter on the keyboard.
If you want to repeat this, just place the mouse curser at the end of the “Selection.Calculate” and press Enter again.
Calculate selected cells by using an Excel add-in
Our Excel add-in “Professor Excel Tools” comes with more than 120 features. One of them is “Calculate Selection” and can be a real time-saver for you.
Just select the cells you want to calculate and click on “Calculate Selection” on the Professor Excel ribbon.
This function is included in our Excel Add-In ‘Professor Excel Tools’
(No sign-up, download starts directly)
More than 35,000 users can’t be wrong.
Other advice for such large Excel models
Well, the fact that your Excel model calculates so long might lead to the conclusion that Excel is not the right software for you in this case. So, please consider the following advice:
- Can you use another software (for example PowerBI or any tool / language more suitable, such as Python, R, etc.)?
- Probably, at this point, you have to stay with Excel, right? Then have you considered to use PowerQuery? Could it take over some of the heavier calculation tasks?
- If not, why don’t you take a look at this article of how to speed up Excel or my book “Speeding Up Microsoft Excel“?
Image by Free-Photos from Pixabay
