Join me EVERY FRIDAY for #formulafriday and EVERY MONDAY for #macromondays on the [ Ссылка ] blog.

Be Social & Let's Connect
++Website [ Ссылка ]
++Twitter [ Ссылка ]
++Pinterest [ Ссылка ]

Read the corresponding Blog Post for this Excel Tip video below
[ Ссылка ]


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.

свернуть