10

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.

18

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.

3
  • I used this routine - I'm getting an error Variable not defined What should be the variable declaration for getformula?
    – Prasanna
    Apr 8 '15 at 19:02
  • @Prasanna GetFormula is not a variable, it's a function. What version of Excel are you using?
    – Indrek
    Apr 8 '15 at 19:26
  • Excel 2013. Excuse my ignorance - i've started learning VBA
    – Prasanna
    Apr 8 '15 at 19:52
4

Perhaps worth noting that as of Excel 2013, there's a native function for this: FORMULATEXT.

From support dot office dot com article on FORMULATEXT function:

Description

Returns a formula as a string.

Syntax

FORMULATEXT(reference)

The FORMULATEXT function syntax has the following arguments:

  • Reference Required. A reference to a cell or range of cells.

Remarks

  • The FORMULATEXT function returns what is displayed in the formula bar if you select the referenced cell.
1
  • 1
    Nice. Built in is the way to go. Unlike Indreks solution, this shows an error if the field is not a formula, you can easely check before with this: =IF(ISFORMULA(B1);FORMULATEXT(B1);B1)
    – Peheje
    Apr 23 '19 at 7:10
2

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.

1

An alternative might be using LibreOffice.

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

0

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

2
  • 1
    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. Jul 18 '14 at 3:12
0

I know verry little about programming and my answer is not elegant. As a future engineer I needed a function in Excel that could display to me the formula. The problem I found in Indrek solution is that his function returns to me a formula like this: "C9*C9*pi()" and I wanted the cell's names in the display of the formula (C9) to be changed to the values of those cells; and that would automatically change whenever those numbers are changed in the original C9 cell.

My code sort of works with very basic formulas. I never found something like this anywhere else, It might help someone or not. Someone might be able to improve this to a decent level. As I said, it works for me and I'm a practical guy with little skills in programming.

The first function isn´t mine at all. It was retrieved from:

https://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array

Function IsInArray(ar, item$) As Boolean
    Dim delimiter$, list$

    ' Chr(7) is the ASCII 'Bell' Character.
    ' It was chosen for being unlikely to be found in a normal array.
    delimiter = Chr(7)

    ' Create a list string containing all the items in the array separated by the delimiter.
    list = delimiter & Join(ar, delimiter) & delimiter

    IsInArray = InStr(list, delimiter & item & delimiter) > 0
End Function

Function GETFORMULA(cell)

Dim alfabeto As String
Dim alfabetos() As String
Dim formula As String
Dim celda As String
Dim cutter As String
Dim cutterarray() As String


'The formula is taken from the cell
formula = cell.formula


'And alphabet is declared to be compared with the formula
alfabeto = "A,B,C,D,E,F,G,H,I,J,K,L,M,O,P,Q,R,S,T,U,V,W,X,Y,Z"


'An array is declared with the elements of the alphabet string
alfabetos = Split(alfabeto, ",")


'Replacing common functions with a symbol, otherwise the code will crash
formula = Replace(formula, "LOG", "#")
formula = Replace(formula, "SQRT", "?")
formula = Replace(formula, "PI", "ñ")

'MsgBox (formula)
Debug.Print formula

'Symbols to identify where the name of a cell might end are declared
cutter = "*,/,+,-,^,(,)"

'An array is declared with the symbols from the string that has been just declared
cutterarray = Split(cutter, ",")

Dim nuevaformula As String


'For the i position in the lenght of the formula
Dim index As Integer

For i = 1 To Len(formula)
    Debug.Print "Para i iterativo=", i
    Debug.Print "Se tiene el digito", Mid(formula, i, 1)



        'if the i element is not a letter, then add it to the chain of characters
        If IsInArray(alfabetos, Mid(formula, i, 1)) = False Then
        Debug.Print "Que es un numero"
        nuevaformula = nuevaformula + Mid(formula, i, 1)
        Debug.Print nuevaformula


        'if the element is a letter, then it´s necessary to get the name of the cell, found all the numbers
        'of the cell until a symbol to cut appears, like a: "*","/","+","-"
        Else
        Debug.Print "Encontramos una letra"
        'Empezamos a buscar más números
        'Numbers in the cell name are going to be find to isolate the cell number

            For s = 2 To 7
            celda = Mid(formula, i, s)
            Debug.Print "Incrementamos una posición quedando", celda

                If (i + s - 1 = Len(formula)) = False Then



                    'if a symbol to cut is not found in the last increment the last number hasn´t been reached
                    'and it´s necessary to keep loking for it
                    If IsInArray(cutterarray, Right(celda, 1)) = False Then
                    celda = Mid(formula, i, s)
                    Debug.Print "En el incremento no se encontró cutter. La i correcta es", i + s - 1


                    Else

                    'if it´s found the name of the cell is
                    celda = Mid(formula, i, s - 1)
                    Debug.Print "Se encontró un cutter la celda es", celda

                    'the search cycle has to be broken
                    Debug.Print s, i
                    i = i + s - 2
                    Debug.Print "Daremos salto a i=", i
                    Debug.Print celda
                    nuevaformula = nuevaformula + CStr(Range(celda).Value)
                    Exit For
                    End If
                Else
                Debug.Print "se alcanzó la máxima cantidad de iteraciones posibles"
                celda = Mid(formula, i, s)
                Debug.Print "La celda encontrada fue", celda
                nuevaformula = nuevaformula + CStr(Range(celda).Value)
                Debug.Print nuevaformula
                nuevaformula = Replace(nuevaformula, "#", "LOG10")
                nuevaformula = Replace(nuevaformula, "?", "raiz")
                nuevaformula = Replace(nuevaformula, "ñ", "pi")
                ISAAC = nuevaformula
                Debug.Print (nuevaformula)
                Exit Function
                End If


            'MsgBox (nuevaformula)
            Next s
        End If
Next i

nuevaformula = Replace(nuevaformula, "#", "LOG10")
nuevaformula = Replace(nuevaformula, "?", "raiz")
nuevaformula = Replace(nuevaformula, "ñ", "pi")

GETFORMULA = nuevaformula
Debug.Print (nuevaformula)
End Function
-2

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

2
  • Doesn't work for me.
    – tohuwawohu
    Jun 24 '14 at 11:39
  • This will not work.
    – Alex M
    Sep 18 '19 at 23:58
-2

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.

2
  • 4
    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
  • This is the exact state of affairs the question is attempting to solve.
    – Alex M
    Sep 18 '19 at 23:59
-3

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

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