Show formulas in Excel

Show formulas in Excel

Show formulas:

Excel offers Show formulas feature by which you can show all your formulas available in excel as text. This property derives at window level hence we will toggle it with the help of ActiveWindow object:

  • Navigate Formulas tab
  • Locate Formula Auditing group as shown below
  • Select Show Formulas and notice the Sheet all formulas will be shown under respective cell. To disappear toggle the same button

Let’s put code to achieve automation in Excel. Point to be noticed will be following VSTO (Visual Studio Tools for Office) as example hence following prerequisites are recommended for C# or VB.Net code examples:

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Excel 2010 or above
  • Create a ribbon designer and put button

C# code example:

private void btnShowHideFormula_Click(object sender, RibbonControlEventArgs e)
{
	//get window reference
	excel.Window oWindow = Globals.ThisAddIn.Application.ActiveWindow;

	//Toggle display formula
	if (oWindow.DisplayFormulas == false)
		oWindow.DisplayFormulas = true;
	else
		oWindow.DisplayFormulas = false;
}

VB.Net code example:

Private Sub btnShowHideFormula_Click(sender As Object, e As RibbonControlEventArgs) Handles btnShowHideFormula.Click
	'Bind window reference object
	dim oWindow as excel.Window
	oWindow = Globals.ThisAddIn.Application.ActiveWindow()

	'Check and toggle show formula
	If oWindow.DisplayFormulas=false Then
	   oWindow.DisplayFormulas=True
	 Else 
		oWindow.DisplayFormulas=False
	End If
End Sub
Public Sub ShowHideFormula()
    ActiveWindow.DisplayFormulas = True
End Sub

Output: Before

Output: After code execution

Leave a Reply

Your email address will not be published.