Knowledge Base Articles By Category
- 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.
VBA Code Required:
- Place the following code in a standard module of the workbook you wish to use it in.
Code:
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 & "}" Else showformula = rng.Formula End If End Function
- 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!
Donations
Recent Forum Posts
Rate this article