how to display the formula of a cell in another cell in excel

Question

I have the following formula in cell g4:

=xirr(D3:D4,B3:B4)

this displays the value 1523% in cell g4

what I want to appear in cell g5 is this:

xirr(D3:D4,B3:B4)

But I don't want the formula to evaluate in cell g5. I just want to see the formula. And I do not want to have to write it out. I am hoping I can copy and paste for multiple cells like this that I want to do.

here is my spreadseet I am looking for a solution for excel or google spreadsheets, but I am hoping it can be applied to both.

The simplest solution would be a [vba] or [google-spreadsheet-api] UDF that returned the formula from the cell via the [Range.Formula property](https://msdn.microsoft.com/en-us/library/office/ff838835.aspx) (or equivalent). — , Jan 20 '16 at 01:20
OK, fair enough. In that case, there is no native worksheet formula to do this. — , Jan 20 '16 at 02:23

score 1 · Answer 1 · answered Jan 20 '16 at 02:04

1

I found this handy from excel help

Switch between displaying formulas and their values on a worksheet
Press CTRL + ` (grave accent).

then I copied the cells I wanted nto notepad and removed the = at the start and replaced it with ' this gave me something like 'xirr(D3:D4,B3:B4) and then I pasted this into excel or google spreadsheets.

answered Jan 20 '16 at 02:04

HattrickNZ

4,373
15
54
98

Did you need to add the 'tick' as the *Prefix Character* ? Without the equals, a formula is just text. – Jan 20 '16 at 02:25
good point, `'` this is only needed if you want to show the = sign in the formula `'=xirr(D3:D4,B3:B4)` but otherwise `xirr(D3:D4,B3:B4)` will do without the `'` tks – HattrickNZ Jan 20 '16 at 02:45

score 0 · Answer 2 · 2016-01-20T03:13:30.910

The solution is going to differ from excel-vba to google-spreadsheets-api but the method will be similar. In VBA, the result can be returned with either the Range.Formula property, Range.FormulaR1C1 property, Range.FormulaLocal property or Range.FormulaR1C1Local property after checking the cell's Range.HasFormula property.

The following is a User Defined Function¹ (aka UDF). See the footnote for implementation.

Function showFormula(rng As Range, _
                     Optional xlRefStyle As Variant, _
                     Optional bPFX As Boolean = False, _
                     Optional bLOC As Boolean = True)

    If IsMissing(xlRefStyle) Then
        xlRefStyle = Application.ReferenceStyle
    ElseIf xlRefStyle <> 1 Then
        xlRefStyle = xlR1C1
    End If

    If rng.Cells(1, 1).HasFormula Then
        Select Case xlRefStyle
            Case xlA1
                If bLOC Then
                    showFormula = _
                      Replace(rng.Cells(1, 1).FormulaLocal, Chr(61), IIf(bPFX, Chr(61), vbNullString))
                Else
                    showFormula = _
                      Replace(rng.Cells(1, 1).Formula, Chr(61), IIf(bPFX, Chr(61), vbNullString))
                End If
            Case xlR1C1
                If bLOC Then
                    showFormula = _
                      Replace(rng.Cells(1, 1).FormulaR1C1Local, Chr(61), IIf(bPFX, Chr(61), vbNullString))
                Else
                    showFormula = _
                      Replace(rng.Cells(1, 1).FormulaR1C1, Chr(61), IIf(bPFX, Chr(61), vbNullString))
                End If
        End Select
    Else
        showFormula = vbNullString
    End If

End Function

Example of a SUMIFS formula in B4.

^{showFormula UDF syntax}

On a system with German regional settings (and a DE-DE language version of Excel) the above would show as SUMMEWENNS(B1:B3; A1:A3; "a").

^{showFormula UDF syntax in DE-DE system}

¹ _{A User Defined Function (aka UDF) is placed into a standard module code sheet. Tap Alt+F11 and when the VBE opens, immediately use the pull-down menus to Insert ► Module (Alt+I,M). Paste the function code into the new module code sheet titled something like Book1 - Module1 (Code). Tap Alt+Q to return to your worksheet(s).}

could not get this to work in excel, not sure why. when I was typing `showFormula` in the cell it was not showing up as a function to be used. — HattrickNZ, Jan 20 '16 at 02:59
9 times out of 10 the UDF has simply been put into the wrong place (e.g. right-click the worksheet name tab and View Code). See above for instructions on incorporating it to an Excel workbook. — , Jan 20 '16 at 03:05

how to display the formula of a cell in another cell in excel

2 Answers2