Excel Formula Display Help

September 22, 2008 9:46 AM

I have an excel file full of formulas that span multiple sheets and rows. I would like to be able to go into a formula and see the actual data used in the formula rather than just the cell references. So, for simplicity's sake, imagine A1=1,000, A2=2,000, A3=A1/A2 When I go to cell A3 and press "F2" or "cntrl`" the formula with the references shows up "=A1/A2". What I would like to see is "=1,000 / 2,000" (what you would get if you wrote =A1 &" / "& A2) I know that I can do this sort of with the watch window or the evaluate formula but I am looking for a quick way to roll down a sheet and see the actual math that is being done without having to scroll all over the place or flip sheets. Hopefully this makes sense. If not, I can further clarify. No one at my office thinks that it is possible so I might just be out of luck but I live in a world of hope...
posted by GrumpyMonkey to Computers & Internet (5 answers total)

Why not display the reference data adjacent to your calculation?

View the calculation:
A1: = Sheet1!A1 + Sheet2!A1
View input 1:
B1 = Sheet1!A1
View input 2:
C1 = Sheet2!A1

I dont think/know if specifically what you are asking for exists (a tool to display the variable substitution only).
posted by SirStan at 10:19 AM on September 22, 2008

Insert a column and concatenate thusly:

=a1 & "/" & b1
posted by A Terrible Llama at 10:48 AM on September 22, 2008

Response by poster: that is pretty much what I am doing today but the formulas are pretty messy and all over the place so the clutter of writing out each cell value gets overwhelming.

An actual example is:
=Sheet1!C285-IF(Sheet2!E281>0,MIN(Sheet1!E278,Sheet1!E281),Sheet1!E278)+Sheet3!E283-Sheet1!D286
which becomes when it is written out:
=12000-IF(0>0,MIN(2884.25925925926,0),2884.25925925926)+15000-24115.7407407407

I am beginning to think that the tool does not exist either.
posted by GrumpyMonkey at 10:48 AM on September 22, 2008

If that doesn't work, try this:

= " ' " & a1 & "/" & b1
posted by A Terrible Llama at 10:49 AM on September 22, 2008

This may not be at all what you are looking for, but if you are looking for this info to debug formulas, the following may help. I have found myself wanting a similar tool when the number of variables in the formula got to be such that I couldn't read the formula anymore. I was able to fix it by renaming the sheets and the cells themselves with variable names that expressed what they contained so that a formula might come out looking like:

= Printing!Total - IF( Printing!InHouse > Printing!Outsourced, MIN( Printing!Toner, Printing!lInk ), Printing!Maintenance ) + OfficeSupplies!TotalPaper - Printing!Postage

Using names Sheets and cells has gone a long way to making Excel formulas way easier to read and debug for me.
posted by chocolate_butch at 11:19 AM on September 22, 2008