# Off Topic > The Water Cooler >  >  Evaluate Formula - Dirty Little Excel Developer's Secret

## Whizbang

This thread (http://www.excelforum.com/excel-gene...n-formula.html) made me think of just how many people are surprised there is a tool in Excel that allows you to follow the calculation in a formula.  Just about everyone to whom I have ever mentioned the Formula Evaluation tool has responded with, "The what?"

Why is this such a hidden gem?  You would think that, when introduced to Excel formulas, the very first thing would be "... and here is the Evaluate Formula tool.  This is your very best friend.  Get to know it.  Consult it frequently.  Add it to your Christmas list. Write sonnets about it.  Name your first-born child after it."

Why is this tool not stressed as one of the most important tools to new users?  Is it because most Excel users are not "taught" anything, but merely open the file and get to work?  If so, why is this tool not more prominant in the ribbon/toolbar?

----------


## shg

I did a 4-hour "Intermediate Excel' class this week; more than half the participants had never heard of it.

----------


## Colin Legg

It's a good tool. There were some formulae where it would crash Excel, but they fixed those bugs (can't remember if they fixed it in XL 2003 or 2007).

----------


## shg

That used to happen to me, not infrequently, in Excel 2003. I can't say that it has or hasn't happened in Excel 2007, but you've got me curious wondering, Colin.

----------


## Colin Legg

Here's a thread with a formula which crashed Excel XP. It didn't crash Excel 2003 for me, but it did for another user. The formula seemed to be fine in Excel 2007.

http://www.xtremevbtalk.com/showthread.php?t=309076

----------


## NBVC

Definitely not fixed in XL2003.

Most of the time, it would crash on me if I used an array formula with an extra little =IF(F1="","",array_formula)... If I take out the IF(F1="","" part, and just ran the array formula it would work.

I haven't seen that problem in XL2007 yet.

I agree it is a great tool.. and often tell people to use it when they ask me how a formula of mine works....

----------


## Andrew-R

> I agree it is a great tool.. and often tell people to use it when they ask me how a formula of mine works....



It's not often I take a moral stance, but that's just wrong.  Where's the satisfaction in just putting the formula into the evaluation tool and have it show you how it's working?  When I de-construct one of your formula I want it to be hard work, I want little beads of blood on my forehead from sustained concentration, I want half a notepad page of scribbled intermediate workings out ... dammit, I want the sense of achievement that comes from knowing that I've worked it out for myself.

And if I want it that way then why should somebody else just have Excel take them by the hand and lead them through your formula like it was some kind of skippy-walk through a rose garden, eh?

I think it's best for everybody (i.e. me) if we never mention the formula evaluation tool ever again.

----------


## Mordred

Formula Evaluation Tool.   :Smilie:

----------


## NBVC

FYI, I might have worded that a little wrongly.  I will always explain my formulas if someone asks, but I often follow up by telling them to use the tool to see the formula work so that they can follow along with the explanation and test the results... and hopefully better learn.

----------


## Andrew-R

Sorry, NBVC, that was my twisted sense of humour at work again - I didn't mean to imply that you weren't willing to help.  It's generally safe to assume that 90% of what I post is intended as a joke ... except for my code, that's always a joke  :Smilie:

----------


## teylyn

I just wish Microsoft would make the evaluation window a bit bigger. Or make the thing resizable. Evaluating a Sumproduct with several arguments and a range of 1000 cells is no fun.

----------


## Bob Phillips

Never use (used) it!

----------


## teylyn

Figures. I guess the tool was only developed so that ordinary mortals can figure out the inner workings of the formulas people in your league produce so easily, seemingly just with a flick of the wrist.  :Smilie:

----------


## Bob Phillips

I just figure who needs anything more than F9.

----------

