• User Defined Function To Show Formulas In A Cell

    Macro Purpose:
    • Function to show the formula in another cell as text.

    Examples of where this function shines:
    • In Excel's View options (Tools|Options|View), you can check a box to show all formulas on the worksheet as formulas, rather than their resulting values. The problem is that this is worksheet level, and sometimes that may not be desireable. This function allows you to show the formulas on the worksheet if you desire.
    • As an example of a practical use for this function, I have used it throughout this site to show what formulas I use in my examples.

    Macro Weakness(es):
    • None identified at this time.

    Versions Tested:
    This function has been tested extensively with Excel 97 through Excel 2010, and shouldn't have an issue in any version of Excel from 97 onwards.

    Note that in Excel 2013 and higher, you can simply enter =FORMULATEXT(A1) to get the same results using native formulas.

    VBA Code Required:
    • Place the following code in a standard module of the workbook you wish to use it in.

    Function showformula(rng As Range)
    'Author          : Ken Puls (www.excelguru.ca)
    'Function Purpose: To show a range's formula in a cell
        If rng.HasArray = True Then
            showformula = "{" & rng.Formula & "}"
            showformula = rng.Formula
        End If
    End Function
    How to use the code:
    • Enter the formula in a worksheet cell in the format =SHOWFORMULA(Range)
    • The results will show similar to what is below

    The End Result:
    • You can see both formula results and actual formulas on the same page.

    Try it For Yourself!
    A completed file with the formula already in place is ready for you to take for a spin!


    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!


    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts

    Bob Phillips

    Index/match with multiple match criteria

    When I said Q1, I meant Quarter 1, cell E31, not cell Q1.

    It most definitely does match your last upload, C8:C30 is the date which I parse,...

    Bob Phillips Yesterday, 06:44 PM Go to last post

    Consolidate paired data sets

    hi all,

    I have a sheet which contains data sets (3 rows per data set), see Product Level sheet of attachment. I want to consolidate and total...

    GLewis14 Yesterday, 02:46 PM Go to last post

    Please help!

    What is the criteria for counting the quantity. Would be helpful if you provided a sample workbook showing what you have and what your expected results...

    alansidman Yesterday, 06:21 AM Go to last post

    Please help!

    I am trying to count two separate pieces of data which appear in the same excel column. The columns will have a date followed by a quantity so look something...

    cprop76 Yesterday, 05:52 AM Go to last post

    does excel 2002 have page layout>themes and colors

    using a great vertex spreadsheet, but to change the colours it says to use page layout>themes and page layout>colors
    I assume my old version...

    anon125 Yesterday, 01:38 AM Go to last post