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
add comment

3 Answers

up vote 10 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
add comment

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
add comment

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

share|improve this answer
add comment

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.