Show the formula of a cell

Excel gives us the option to show all formulas of the cells of a sheet instead of its result, however it is likely that on some occasion you need to display only the formula contained in a specific cell.

To show formula of a cell in Excel we will create a (UDF) that will have as an argument the reference of the cell for which we want to know the formula.

Create the user-defined function

To start we must open the Visual Basic Editor and create a Code Module where we will place the following function:

Public Function MostrarFormula(Celda As Range)
MostrarFormula = Celda.Formula
End Function

The output in the Visual Basic Editor is as follows:

Show the formula of a cell

We must save the file as a macro-enabled Excel workbook so that we can use the newly created function.

Show the formula of a cell

The next step is to use the function and for this I have placed the formula in cell A1:

=SUMA(1, 2, 3, 4, 5)

Now in cell C1 we will use our VBA function as follows:

=MostrarFormula(A1)

By indicating the address of cell A1 as an argument of our function, Excel will obtain the value of its property and it will display it in cell C1:

Test User Defined Function (UDF)

Cell C1 will always give us the formula that has been written in cell A1. Notice how changing the formula in cell A1 will automatically reflect the change in cell C1:

Showing the formula contained in a cell

Show functions in Spanish

In the previous examples you can notice that the function it returns the name of the function in English. The only way to get the names of functions in Spanish is to use the property instead of property as follows:

Public Function MostrarFormula(Celda As Range)
MostrarFormula = Celda.FormulaLocal
End Function

If you make this small change to the UDF function, you will get the name of the Excel functions in Spanish.

Leave a Comment