You may wish to display the script of a formula contained in a particular cell. This may be for audit purposes, training other users or to assist with building a spreadsheet model.
Would you like to learn more?
The Excel ribbon option '[File] | Options | Advanced | (options for this worksheet) Show Formulas in cells instead of their calculated results' will affect a particular worksheet so that it displays all of the formulas rather than the normal calculated values. This is an all or nothing option. It can not be applied to the contents of particular cells.
In the example above, the user defined 'FormText()' function is used in cells M24 and M25 to display the contents of N22 and M24.
|Function FormText(CellRef As Range, Optional RefIndicator As Integer) As String
' RefIndicator: 0 do not show, 1 show reference, 2 show absolute reference
Dim n As Integer, f As Integer
If IsNull(RefIndicator) = True Then
RefIndicator = 0
FormText = CellRef.Formula
If RefIndicator > 0 Then
FormText = "[" & CellRef.Address & "] " & FormText
n = 1
f = InStr(1, FormText, "]")
Do While n < f
If RefIndicator = 1 And Mid(FormText, n, 1) = "$" Then
FormText = Trim(Left(FormText, n - 1) & Mid(FormText, n + 1, 200))
If Mid(FormText, n, 1) = "," Then
FormText = Trim(Left(FormText, n) & " " & Mid(FormText, n + 1, 200))
n = n + 1
The macro shown here should be entered into a Visual Basic module, either in a specific workbook or in your (hidden) Personal.xls macro workbook. It can then be used like any other Excel function - either typed directly into a cell or selected from the Paste Function wizard.
A useful tool for checking cell contents is my 'SheetMap' spreadsheet. It creates a duplicate of a worksheet, labelling each cell according to the type of contents.
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file:xlformtext.htm||Page last updated Jan 12||© MeadInKent.co.uk 2013||CMIDX S3 P9 Y|