Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

7 Answers 7

up vote 13 down vote accepted

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.

share|improve this answer

An alternative might be using LibreOffice.

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

share|improve this answer

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.

share|improve this answer

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

share|improve this answer
    
Doesn't work for me. –  tohuwawohu Jun 24 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]

enter image description here

share|improve this answer
    
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 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 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.

share|improve this answer
3  
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 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")
share|improve this answer
1  
Unless I'm misunderstanding you, this has nothing at all to do with what I'm asking. –  nhinkle Oct 9 at 20:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.