[ad_1]

Excel Tip – How To Freeze Your Excel Formula

In this article I want to talk you through not creating a formula but how to manage a formula which is unfinished, but which you do not want to lose when you have to pause your train of thought, or are interrupted by a co worker or colleague.

I know that I have many times been in the middle of a complex formula and been disturbed by a colleague, or need to move away for Excel to do something else. I am sure you have sometimes been deep in thought working through your logic of using Excel to solve your business questions or queries by using a sometimes quite complex formula to then have to start all over again because Excel does not save half created formulas.

There is a quick and easy way to keep your half or partly written formula as it stands, as if you try to just hit return or enter Excel is will not co-operate. If you hit the ESC key, well, you lose all your hard formula work which can be frustrating and annoying.

So, when ever you are in your formula, all you need to do is just put either ‘ or ” in front of the = sign. I usually use the ” out of habit but that’s my choice. If you use the ‘ character the you cannot see this in front of your formula so it looks a bit neater, but you can see it in the formula bar once it is activated.

Excel treats this part of your formula now just like a text string that is partly entered and has no trouble saving the formula no matter what stage of analysis you are at. It is a bit like in VBA where you can make a macro comment which will never be executed by Excel so long as your commentary is preceded by an apostrophe. In the case of VBA the commentary turns green, which is not the case in the Excel worksheet but the formula will be saved if you enter either ‘ or ” before it.

In my example below I have a half written formula

=INDEX(A2:A500),MATCH(1,(

If I try to save this partial formula I cannot do so, but adding in the ” as below

“=INDEX(A2:A500),MATCH(1,(

I can save my work and go back to the formula at a later time, and just remove the ” to work on the formula again.

I use this tip all of the time and have reduced the amount of time I spend on trying to recreate complex formulas and my train og logic and thought when I have been disturbed. I hope you find this tip useful. I know I did.

[ad_2]

Source by BJ Johnston