# Is there a way to express a complex Excel formula in terms of final (non-formula, value-only) cells?

I have a very complicated Excel spreadsheet (formulas accessing cells with other formulas across multiple worksheets) that in the end computes a single cell (output) with input from several other cells (parameters). Is there an automated way (an Excel macro or a standalone tool) that could start at the output cell and recursively unroll the computations until it expressed the formula in terms of parameter cells directly?

Clarification

In the comments there was a suggestion of Evaluate Formula tool. While I could not find how to activate it in Excel 2008 for Mac, from its description it sounds like it allows the user to step through evaluation of a cell. That's not what I need. What I need is a way to convert the formula in a given cell that may reference other cells containing formulas, to an equivalent formula expressed in terms of final cells (those that contain values, but not formulas).

Here's a simple example. Let

• `A1` contain `= B1 + C1`
• `B1` contain `= B2 * B2`
• `C1` contain `= C2 * C2`
• `B2` contain `1`
• `C2` contain `2`

Evaluate Formula would allow me to step through the calculation of `A1` to get to the final value of `5`. What I need is a tool that would unroll `A1` to the formula `= B2 * B2 + C2 * C2` without actually evaluating it.

• Does “Evaluate Formula” do what you want? If not, can you please explain a bit more clearly (perhaps by giving an example) what you do want? – Scott Feb 27 '13 at 1:22
• @Scott - Thanks for the suggestion, but it doesn't look like that's what I need. To answer your question, I need a tool that would "unroll the computations until it expressed the formula in terms of parameter cells directly". I thought this sentence is pretty clear, but I added an explicit example of what I need and of what I don't need, and an explanation for why Evaluate Formula is probably not the right solution for my problem. – George Skoptsov Feb 27 '13 at 4:32
• Good example. My second guess would have been that you wanted `= (1*1) + (2*2)` for that situation; thanks for clearing that up. You can sort-of use “Evaluate Formula” for this, using the “Step In” function, but you would need to take a million screen shots along the way, and assemble the results manually. (This composite image, which is heavily edited for cosmetic purposes, illustrates the process.) That’s clearly not practical. – Scott Feb 27 '13 at 18:36
• I think boost pro does that, but I have not tried it and it is not free (there is a free trial though). Not sure if it's available on Mac either (does not look like it). – assylias Feb 27 '13 at 19:31
• @George, this website is looking kind of out-of-date now, but I think there are several commercial solutions of this sort. Bascially, you develop your spreadsheet formulas "natively", and then run a tool to convert them to code, which you can then compile and call from anywhere, including Excel itself: calc4web.com – jtolle Dec 15 '14 at 13:26

# The problem

You can't do this with Evaluate Formula because this isn't the purpose of the function. That's why it is called evaluate, it is for evaluating the formulas. What you want is some kind of unpacking. This is a bit special need so it isn't implemented as a tool in Excel, but there are solutions if you create some Visual Basic functions/macros.

Create a VBA code module (macro) as you can see in this tutorial.

1. Press Alt+F11
2. Click to `Module` in `Insert`.
3. Paste code.
``````Function CellFormula(Target As Range) As String
CellFormula = Target.Formula
End Function
``````

Then enter the following to a cell: `=CellFormula(A1)`

This will tell the formula of the cell. The only problem with this code is that it only works for one level. If you want to unpack the contained cells formulas too, then you need a more complex code with recursion.

# The solution

It was a long journey but I created a VBA macro for you that implements this function. I don't state that this code will work for every formula, but it will work in most/some of them. Also, I don't state that this code will generate formulas that is equivalent with the originally entered code or will give the same result as the original.

## Source code

``````Option Explicit

Function isChar(char As String) As Boolean
Select Case char
Case "A" To "Z"
isChar = True
Case Else
isChar = False
End Select
End Function

Function isNumber(char As String, isZero As Boolean) As Boolean
Select Case char
Case "0"
If isZero = True Then
isNumber = True
Else
isNumber = False
End If
Case "1" To "9"
isNumber = True
Case Else
isNumber = False
End Select
End Function

Function CellFormulaExpand(formula As String) As String
Dim result As String
Dim previousResult As String
Dim cell As Range
Dim stringArray() As String
Dim arraySize As Integer
Dim n As Integer
Dim trimmer As String

Dim c As Integer 'character number
Dim chr As String 'current character
Dim tempcell As String 'suspected cell's temporaly result
Dim state As Integer 'state machine's state:
Dim stringSize As Integer

result = formula
previousResult = result
state = 0
stringSize = 0

For c = 0 To Len(formula) Step 1
chr = Mid(formula, c + 1, 1)
Select Case state
Case 0
If isChar(chr) Then
state = 1
tempcell = tempcell & chr
ElseIf chr = "\$" Then
state = 5
tempcell = tempcell & chr
Else
state = 0
tempcell = ""
End If
Case 1
If isNumber(chr, False) Then
state = 4
tempcell = tempcell & chr
ElseIf isChar(chr) Then
state = 2
tempcell = tempcell & chr
ElseIf chr = "\$" Then
state = 6
tempcell = tempcell & chr
Else
state = 0
tempcell = ""
End If
Case 2
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
ElseIf isChar(chr) Then
state = 3
tempcell = tempcell + chr
ElseIf chr = "\$" Then
state = 6
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 3
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
ElseIf chr = "\$" Then
state = 6
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 4
If isNumber(chr, True) Then
state = 4
tempcell = tempcell + chr
Else
state = 0
stringSize = stringSize + 1
ReDim Preserve stringArray(stringSize - 1)
stringArray(stringSize - 1) = tempcell
tempcell = ""
End If
Case 5
If isChar(chr) Then
state = 1
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case 6
If isNumber(chr, False) Then
state = 4
tempcell = tempcell + chr
Else
state = 0
tempcell = ""
End If
Case Else
state = 0
tempcell = ""
End Select
Next c
If stringSize = 0 Then
CellFormulaExpand = result
Else
arraySize = UBound(stringArray)
For n = 0 To arraySize Step 1
Set cell = Range(stringArray(n))
If Mid(cell.formula, 1, 1) = "=" Then
trimmer = Mid(cell.formula, 2, Len(cell.formula) - 1)
If trimmer <> "" Then
result = Replace(result, stringArray(n), trimmer)
End If
End If
Next
If previousResult <> result Then
result = CellFormulaExpand(result)
End If
End If
CellFormulaExpand = result
End Function

Function CellFormula(rng As Range) As String
CellFormula = CellFormulaExpand(rng.formula)
End Function
``````

To make it work, just create a macro (as I described it in the beginning of the answer) and copy-paste the code. After this, you can use it with `=CellFormula(A1)` where `A1` can be any kind of 1x1 cell.

## Cases it works

I created some examples so you can see it in action. In this case, I demonstrate the use with strings. You can see it works perfectly. The only little bug is that somewhy the algorithm changes the semicolons to commas. After you replace them (as I did in this example), you get the correct output. Here, you can see how it works with numbers. Now, we face the first problem that the algorithm doesn't care about the mathematical operation sequence, that's why the red number is 6 when it should be 10. If we put the sensitive operations (like addition and subtraction) into parenthesis, then the given formula entered back will give the same output as you can see in the green number in the bottom that says 10. ## Cases it doesn't work

This algorithm is not perfect. I only tried to implement the most common uses, so it can be improved by adding more features that handle other cases like ranges.
As you can see in this example, I used `SUM()` with a range as a parameter. Since the algorithm decrypts the cells content from top to down, it starts with the replacement of the `SUM()` parameters than later with anything else. Therefore, the `:` stays in its place while around it everything is replaced, so new cells are replaced near to it, who will change the meaning of it. Thus the output will be wrong. So in this case, you can only use this macro to study the original formula. • Thanks for putting the effort into this. I understand the limitations, but I think this is closest to what I requested that can be reasonably done. – George Skoptsov Mar 8 '13 at 13:55

Given your usage of the word "trace" in your question, I would assume you are familiar with the `Trace Precendents` and `Trace Dependents` functions in the Formula Auditing section of Excel?

Here is a brief explanation of each of the formula auditing tools from dummies.com:

Understanding Excel 2010's Formula Auditing Tools

• Yes, I'm aware of these tools -- they draw dependency arrows (which fails miserably when the worksheet is large or the dependency is to a cell from a different sheet). I need more than knowing dependencies -- I need a way to produce the actual expression. Regarding the use of word 'trace' -- @killermist edited my original title - rather gratuitously and incompentently. – George Skoptsov Mar 7 '13 at 23:49
• If you are comfortable with VBA, you could unroll the entire function by iterating through all the precedents with the function described here. – user1936123 Mar 8 '13 at 2:00

Today we'd just use `FORMULATEXT()` in the fairly obvious way, but the trick missed back then, and today for those with earlier versions (like my in-laws with 2007), is to use a differnt Excel 4 macro command, `GET()`, inside a Named Range.

One of the MANY things it could do was to read a formula as text, exactly as `FORMULATEXT()` does now. Then it's a matter of just combining strings. A bit of `IF()` tossed in to test if there is something to obtain, and one's homefree.