Excel, how to display values in cell formula inste

Question

When you click a cell in Excel, you see a formula like =A1+B1. That is the formula with the cell references. I want to see the formula with the cell values, for example: =10+20.

A1 =10           //Cell A1 has the value 10
A2 =20           //Cell A2 has the value 20
C2 =A1+B1        //Cell C2 has the value A1 + A2

D2 =10+20        //I want Excel to derive this.

I don't want the formula displayed in the cell or anything. I still want C2 to display 30. I just want it so that if I click on C2, I can see =10+20 in the formula bar.

Alternatively, if C2 displayed a comment where it showed the calculations (so clicking on C2 would display the comment "=10+20", that would work too.

Answer 1

Here's a trick that answers the first of your questions:

In the formula bar, inside the formlula select the cell reference, eg A1, that you want to see the value of... and press F9. Hey presto, the highlighted part of the formula is evaluated: =10+A2.

Still in the formula bar, now do the same for A2, and you'll see =10+20 The value shown in the cell is still 30, but it no longer derives from A1 and A2, because the values in those cells have been resolved in your formula. Press ENTER to accept the evaluated formula.

You can use F9 to evaluate any part of a formula to see what Excel will return - if it can - and you can do it several times in a longer formula to check one part after another. NB! The part you select/highlight in the formula bar must be a 'complete' phrase, as it were, that is capable of evaluation, eg complete sets of parentheses, all arguments in a formula, etc.

This is most useful when a longish formula returns an error and you want to find which part is causing the problem... I use it all the time. Very helpful when finding a single dud value in an array formula, etc, or displaying the values in a range. NB! length limitations apply...

Finally, be sure to hit Esc once you've viewed the evaluated components of your formula so the formula returns to what it was to start with. Otherwise, your formula simply becomes a series of numbers or strings, and no longer functions as a formula. If you forget this, then Undo/Ctrl+Z to restore your formula.

To answer your second question: there is an Excel function N("...") that allows you to add a comment as a text string INSIDE the formula, not in a separate Comment Box.

For example: =SUM($B$2:$B$21)+N("Total sales for all 20 sales reps.")

=N("any text string") always evaluates to 0 (use the above tip to confirm this), thus adding 0 doesn't alter your answer, but allows individual formulae to carry their own little Rem statements around with them. N(3) evaluates as 3, , while N("3") evaluates as 0, btw.

Just for fun you can protect/alter your sheets by adding N() statements into formulae like this: =SUM($B$2:$B$21)/N(IF($F$3="Go",1,"1"))

where $F$3 (some innocuous cell, text colour white) must have the word "Go" entered into it to stop the formula being divided by 0. You could make it divide by some random number, etc, rendering the results meaningless... or at least slowing someone down.

HTH

Answer 2

It is not possible, but if the reason is that you quickly want to identify the values, you can use the "Trace Precedents" that will draw an arrow to the related fields within 1 sheet without the user needing to identify cells and ranges all the time.
Perhaps it helps if this was your purpose...
You find this functionality in the "Formulas" tab of the ribbon.

Answer 3

That's not how Excel works; if you have a formula in a cell, that is what is going to show up in the formula bar.

EDIT

You can use VBA (from Tools > Macros > Visual Basic Editor), however, to write code that will update the contents of the C cells with the the values of A + B, like this:

Private Sub HideFormula()
    Dim lastrow As Long, r1 As Long

    ' Get the last row in the worksheet
    lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

    For r1 = 1 To lastrow

        ' If A & B aren't blank, make the formula of cell C equal to A + B.
        If Sheet1.Range("$A$" & r1).Value <> "" And _
            Sheet1.Range("$B$" & r1).Value <> "" Then

            ' In the example, C2 = A1 + B1, so offset C by one
            Sheet1.Range("$C$" & (r1 + 1)).Value = _
                "=" & Sheet1.Range("$A$" & r1).Value & "+" & _
                Sheet1.Range("$B$" & r1).Value

        End If

    Next

End Sub

EDIT 2

If you want to replace the contents of a C cell with the values in the formula, you can use the .Formula value to find its formula, and go from there:

Private Sub ReplaceFormulaWithValues()
    Dim lastrow As Long, r1 As Long
    Dim temp As String, arTemp

    ' Get the last row in the worksheet
    lastrow = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row

    For r1 = 1 To lastrow

        ' If A & B aren't blank, make the formula of cell C equal to A + B.
        If Sheet1.Range("$C$" & r1).Value <> "" Then

            ' Get the formula for the current C cell
            temp = Replace(Sheet1.Range("$C$" & r1).Formula, "=", "")

            ' Create an array by splitting the formula on the + sign
            arTemp = Split(temp, "+")

            Sheet1.Range("$C$" & r1).Value = _
                "=" & Sheet1.Range(arTemp(0)).Value & "+" & _
                Sheet1.Range(arTemp(1)).Value


        End If

    Next

End Sub

Answer 4

You may use CONCATENATE to get the desired results. For your case, enter the function as the following:

D2 =concatenate("= ",A1," + ",A2)