show formulas in excel

G

Gord Dibben

Either preface them with an apostrophe or use a helper cell and a User Defined
Function.

Function ShowFormula(cell)
ShowFormula = "No Formula"
If cell.HasFormula Then ShowFormula = cell.Formula
End Function

usage is =ShowFormula(cellref)

First off.........save a backup of your workbook.

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there.

Save the workbook and hit ALT + Q to return to Excel window.

Enter the formula in a helper cell as explained above.


Gord Dibben MS Excel MVP
 
B

Bernard Liengme

Suppose you worksheet has 10 cells with formulas and 20 with text or numbers.
If you use CTRL+` )the key below ESC), then the 10 cell with formulas will be displayed as formula. You cannot have only some of these cells displaying formulas.

You can use a User-defined function such as

Function ShowFormula(Cell as Range) as String
ShowFormula = Cell.Formula
End Function
to display the formula of a specific cell using the syntax =ShowFormula(A1)

If you need help with VBA see David McRitchie's site on "getting started" with VBA

http://www.mvps.org/dmcritchie/excel/getstarted.htm

best wishes
 
Top