It sometimes it is really useful to show your Excel formulas on your worksheets. This gives an insight into the logic of the worksheet. It also is a great way to check your formulas to ensure they look correct. Excel has a built-in option for this already if you want to toggle between a normal view of your worksheet as well as show your formulas. Follow the instructions below.

1. Formula Tab
2. Formula Auditing
3. Show Formulas

 

 

 

You can also quickly toggle between these two views by hitting CTRL+’

 

 

 

 

 

As you can see we have the cell values and the formulas. This method gives you one or the other view.

Let’s get a bit greedy now and look to have both the values AND the formulas on the same worksheet. I have found this beneficial when documenting a spreadsheet solution for a third party or writing a user manual to accompany a spreadsheet.

It takes a bit of VBA, but not much, just a bit, so let’s give it a go. This is done by creating a User Defined Function or a UDF.

What Is A User Defined Function?

I will start with the definition of a User Defined Function in Wikipedia.

“A user-defined function (UDF) is a function provided by the user of a program or environment, in a context where the usual assumption is that functions are built into the program or environment”. Whoa, what does that mean?.

Excel has a lot of built-in functions as you may or may not know at this stage of your Excel and VBA journey. In most cases these suit the job at hand, and even if you need to combine or nest a few functions within each other you get the job done.

If you cannot find a built-in function in Excel to do what you want it to you can BUILD YOUR OWN. Yes, that’s right you can build your own function.*Happy Dance*

Yo just need a little bit Excel VBA code.

So here is the code………

 

 

 

 

 

 

How To Install The Code.

1. Hit ALT + F11
2. Click on ThisWorkBook in the Project VBA Window
3. Insert A Module Into The Project
4. Enter the above code into the Module
5. Hit ATL+F11
6. Save the workbooks

Using The Code

1. Enter the formula =showformula(Range) in a worksheet cell that you want to display the formula in
2. Your results will be as below

 

 

 

 

 

Want To Learn More VBA? NO better place to start than here. With Chandoo’s VBA Classes

 

 

 

 

What Next?. Want To Learn Even More About Macros?

I have some really useful Excel Macros for you to try. You can check out my full list of Macro Mondays blog posts, and the code is always free for you to copy at the end of the blog post.

If you want more Excel and VBA tips then sign up for my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.