Hello Excellers, and welcome back to some more formula fun and another #formulafriday blog post. Today, let’s look at how to calculate only some of your workbook formulas. Have you got a big workbook that takes a long time to calculate?. Excel workbooks can take a long time to calculate, depending on what formulas are used.. Several minutes may not seem a long time in the ordinary course of a workday, but if you want to open a workbook, change a few cells, and recalculate only those few cells, it is a LONG time.
But here is a great way to recalculate only a small number of cells in your Excel workbook instead of recalculating all formulas in the spreadsheet.
Let’s walk through an example. Although only a small sample data set, it will show you how to use this great little hack Excel hack.
Switch To Manual Calculations.
- First of all, let’s switch to manual calculations
- File | Options | Formulas
- Calculation Options | Workbook
- Calculations | Manual
Here Is The Theory Behind How This Formulas Method Works.
It’s really quite straightforward. We can press F2 then Enter on each cell that has to be calculated. This will force a single cell to calculate. Hmm so let try to calculate Columns E in our example dataset by changing the tax rate from 25% to 12%.
- Change the tax rate to 12%
- Select the cells that you want to recalculate
- Hit CTL+H to display the Find and Replace Dialog Box
- Type the = sign into the Find What box
- Type the = sign into the Replace With box
- Select the Options button and ensure that the Look In is set to Formulas and that match Entire Cell Contents is NOT selected (we are only looking to find the = sign)
- Click Replace all
- We can see that ONLY THE SELECTED CELLS ARE CALCULATED.
This is really useful if you have a few cells you want to test with some new calculations. So, we can test a few formulas without updating our whole worksheet and be stuck in a calculations loop. Have you used this method of calculating only some of your Excel formulas at all? Do leave a comment in the comments section below.
Do you want even more Excel formula tips?
If you want more Excel and VBA tips then sign up for my Monthly Newsletter where I share 3 Tips on the first Wednesday of the month and receive my free Ebook, 50 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series. Click on the link below
How To Excel At Excel – Formula Friday Blog Posts.
