Join me EVERY FRIDAY for #formulafriday and EVERY MONDAY for #macromondays on the http://www.howtoexcelatexcel.com blog.
Be Social & Let’s Connect
++Website http://www.howtoexcelatexcel.excel.com
++Twitter https://twitter.com/howtoexcelatex
++Pinterest http://www.pinterest.com/howtoexcelat
Read the corresponding Blog Post for this Excel Tip video below
http://www.howtoexcelatexcel.com/formulas/formula-friday-calculate-only-some-of-your-workbooks-formulas/
Have you Got This Excel Issue?
When you are waiting for an Excel workbook to recalculate, a number of minutes may not seem a long time in the normal course of a workday. But, if you want to open a workbook, change a few cells then recalculate. Well, it can fell a LONG time. Especially if you are testing a new formula or calculation method.
Here Is A Solution
But here is a great way to refresh a formula on only a selected number of cells in your Excel workbook, instead of recalculating all of the formulas in the spreadsheet.
Let’s walk through an example. This is only a small sample data set, but it will show you how to use this great little hack Excel hack. I have a sample data set below. Very simple sales volumes and their pre and post-tax values with a specified tax rate in Cell I2.
-First, switch to manual calculations
-File | Options | Formulas
-Calculation Options | Workbook
-Calculations | Manual
The Theory Behind This Method Of Calculation
Did you know that in Excel if you press F2 then Enter on a cell, only that single cells recalculates?. Yes, this will force a single cell to calculate. So certain cells can be calculated on an Excel worksheet. So, what if we combined this with a way to select specific cells. How about finding cells in a range (that we specify) by using Find & Replace.
So, let’s try to calculate Column E ONLY in our example dataset only 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
….ONLY THE SELECTED CELLS ARE CALCULATED.