Evaluate: Excel’s Dirty Secret
As much as I hate to promote the use of Excel, I’ll grudgingly admit that it’s a useful application. And until you’ve all learned Python– it remains one of the most popular pieces of engineering software today. So you might as well learn how to squeeze every last drop of functionality from it.
I’ve written a few posts now on Excel functions you might not have heard of. However, there’s one function that I’ve abstained from sharing. This is because it deserves a post of its own; a function so easily abused that the only reason Microsoft keep it is to maintain compatibility with Excel 4.0- and even then, they have hidden it away.
I would like to introduce you all to the Evaluate function.
Evaluate(<text>) function is simple. It asks Excel to evaluate the text within the brackets as though you’d typed it in the formula bar yourself. As a few examples:
Evaluate("A1 * 3")returns triple the value in cell A1.
Evaluate(A1 & " (" & A2 & ")")runs whatever formula has been typed in cell A1 on whatever has been typed in cell A2; e.g. if A1 =
Sumand A2 =
1, 2, 3then it would show 6, the sum of 1, 2 and 3.
And that’s the power of Evaluate. It allows you to dynamically build functionality; to allow your spreadsheet to effectively build and adapt itself. The most practical applications centring on allowing the user to enter their own transformations, or building different expressions based on the data/user input.
In an attempt to stop people from accidentally birthing Skynet in Excel (and also because dynamic evaluation prevents Excel from being clever about how it does its calculations, making it less performant) Microsoft has effectively hidden away the Evaluate function.
To resurrect this function, we need to do some trickery. We must define a new named range to invoke the function. To do this open Define Name from the Name Manager (Formula > Define Name in more recent versions of Excel) and enter:
- Refers To:
Then press OK.
You can now use the Evaluate function! To test this type
1 + 1 in cell A1, and
=EvalLeft in cell B1. Lo-and-behold you’ll be presented with the number
2. Feel free to experiment. You can use string concatenation (
&‘ing together values from different cells or
"text") to build complex expressions, like my third example above.
For completeness I’ll just explain why this trick works. To keep compatibility with Excel 4.0 Microsoft had to leave Evaluate in. The compromise was that it would only be available as an undocumented Macro. This macro isn’t directly callable from your spreadsheet, however the name manager has access to it. Of course, if you had to type the text you wanted evaluated in the name manager every time it wouldn’t be all that useful. So instead we reference another cell, in this case we use the
Indirect function to reference the cell immediately to the left of the cell that EvalLeft is called in.
Before you get all excited, I am legally obliged to tell you that Eval is Evil. In Excel there aren’t really any security concerns, so we’ll skip that argument against dynamically constructed code- however it does mean that you have a level of indirection; anyone reading your code needs to work out what your Evaluate string construction is doing before they can work out what the resulting text then evaluates to. This can lead to some exciting debugging. So, let me just remind you that with great power comes great repercussions.