# Can Excel show a formula and its result simultaneously?

I know that it's possible in Excel to toggle between displaying values and displaying formulas. I'm required to turn in assignments for a statistics class as a printed Excel sheet showing both the formula and the result. Right now the instructor makes us either copy the formula and paste it as text next to the computed value, or copy the value and paste it next to the formula. This is very inefficient, prone to error (if you change the formula or values after doing the copy-paste), and generally a waste of time.

Is there any way to have Excel show the formula and its value in the same cell? If not, is there any function which will display the formula from a referenced cell as plain text, e.g. `=showformula(A1)` which would print out `=sum(A2:A5)` instead of `25` (if those were the formula and value of cell `A1`)?

I'm using Excel 2010, but a general answer that works for any recent edition of Excel would be nice.

-

I'm not aware of any built-in functions for this. You can, however, create a user-defined VB function to accomplish what you want.

Press Alt+F11 to open the VBA editor, right-click in the Project Explorer and select Insert -> Module. Paste the following code:

``````Function GetFormula(cell)
GetFormula = cell.Formula
End Function
``````

Now you can use `=GetFormula(A1)` to show the formula of that cell.

-

An alternative might be using LibreOffice.

There is an extension named Display Formula and Value simultaneously. It should do what you need.

-

Here’s a way to automate what you were doing before.  It combines the best parts of LonelyKnight’s answer to his/her own question, Indrek’s answer, and Billis’s answer:

``````Sub Show_Formulas()
Dim rng As Range, cell As Range
row_offset = 10
col_offset = 0
Set rng = Range(Cells(1, 1), Cells(5, 7))

On Error GoTo ErrHandler
For Each cell In rng
cell.Offset(row_offset, col_offset) = "'" & cell.Formula
Next cell
Exit Sub

ErrHandler:
MsgBox "The cow jumped over the moon", , "Error!", Err.HelpFile, Err.HelpContext

End Sub
``````

For every cell in the specified range (here hard-coded as `A1:G5`), it copies the cell’s formula, protected by an apostrophe, to a cell a fixed offset away.  (See How do I add VBA in MS Office? for guidance on using VBA in Excel.)  You just have to remember to run this macro before you print.

Or, add a `PrintOut , , , True` statement just before `Exit Sub`, and this macro will print the sheet for you.  (You just have to remember to print using this macro.)  The fourth parameter to `PrintOut` is Preview, set to `True` to have Microsoft Excel invoke print preview before printing the sheet (thus giving you the option to cancel) or `False` (or omitted) to print the sheet immediately, unconditionally.

Or, if you’re really concerned about forgetting to run this, you can use `Private Sub Worksheet_Change` to update the displayable formulas any time you make any change in the worksheet.

-

You can create an additional column containing the formulas: =REPLACE(target_cell;1;1;"")

-
Doesn't work for me. –  tohuwawohu Jun 24 '14 at 11:39

The free add-in, FormulaDesk can do this for you, as well as displaying the formula in a more understandable way and pinpoint errors in the formula.

As you can see in the screenshot below, you can view the cell's final result, and original formula, and enhanced explorable display of the formula simultaneously, as you asked.

[Disclosure: I am the author of FormulaDesk]

-
Thank you for disclosing your affiliation. It does looks like you do have previous contributions on Stack Exchange, which is great, but I would suggest re-reading the self promotion policy just to be safe. You haven't done anything wrong, but oftentimes when users see somebody whose only answers on the site are promoting something they made, they flag it as spam. It would also improve this answer if you could include a screenshot as an example. Thanks! –  nhinkle Jul 18 '14 at 2:55
Thank-you for pointing this out to me. I've edited my answer to exactly answer your original question. –  Gareth Hayter Jul 18 '14 at 3:12

Here's a solution:

1. Put an apostrophe (`'`) at the beginning of the formula
2. Copy it to the row below or the column alongside it
3. Go back to the original formula and remove the apostrophe

This works.

-
This requires re-copying the formula every time you change it. When changing a spreadsheet frequently, it would be too easy for the copied formulas to get out of sync with the real formulas. –  nhinkle Oct 15 '14 at 20:22

Combine text with a value Suppose cell A1 contains the number 9999

``````="Total: "&TEXT(A1,"\$#,##0.00")
``````

This formula will display "Total: \$9,999.00"

Another example that uses the NOW function to display text with the current date / time

``````="Report printed on: "&TEXT(NOW(),"mmmm d, yyyy at h:mm AM/PM")
``````
-
Unless I'm misunderstanding you, this has nothing at all to do with what I'm asking. –  nhinkle Oct 9 '14 at 20:19