• 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


    Acess data in shared onedrive file

    I am going to spend an afternoon tinkering with creating a flow so see if there is something I can do from that end. Silver lining, its nice to find something...

    samdthompson Today, 01:01 AM Go to last post
    Ken Puls

    Acess data in shared onedrive file

    Damn. I suspect that we're stuffed here. With using the owner's email, that means that you would need to provide their credentials to connect to that...

    Ken Puls Today, 12:53 AM Go to last post

    Acess data in shared onedrive file

    Yip, the file path is different. It shows the owners email rather than mine.

    I have also tried accessing as web URL, plain excel file but...

    samdthompson Today, 12:29 AM Go to last post

    Automate some steps in IE, with VBA

    hello everybody,
    i`m here to search for some help, if is possible.

    i was trying by myself to scrap some vba code to automate some steps...

    polonic Yesterday, 09:34 PM Go to last post

    Help With SumIfs

    Your formula in column O3 was very close. The problem was that your named ranges were different lengths. WeekNo went from A2:A69 while Worker and TimeTaken...

    hbgurley Yesterday, 08:53 PM Go to last post