It’s sometimes in reality beneficial to show your formulation for your work sheets. Doing so offers an insight into the logic of the work sheet, and is also a super way to test your formulas to make sure they look accurate.

Excel already has a constructed in option for this in case you want to toggle among a everyday view of your paintings sheet as well as display your formulas. Here is how:-

Formula Tab
Formula Auditing
Show Formulas
You also can use a set of shortcut Financial Software Tutorials keys to speedy toggle between those two perspectives through hitting CTRL+’.

So, with this method you can handiest view both the values or the formulation at the only time. Let’s get a piece grasping now and turn up the extent and appearance to have each the values AND the formulation at the identical work sheet.

I have determined this useful while documenting a spreadsheet solution for a third birthday celebration, or writing a person manual to accompany a spreadsheet. It offers a terrific visual approach of explaining your formulas alongside the effects, i.E the values of your formulation.

It takes a bit of Visual Basic For Applications (VBA), but now not lots, just a bit, so let us deliver it a cross.

So right here is the code we want to perform this project.

Function showformula (rng As Range)

If rng. HasArray = True Then

showformula = “(” & rng.Formula & “)”

Else

showformula = rng.Formula

End If

End Function

Next issue we want to do is install the code. This is straightforward enough to do-

Open up the Visual Basic (VB) editor in Excel with the aid of hitting the ALT+F11 keys
Click on This Workbook within the within the Project VBA window
Insert A New Module into the Project
Double click to open the module
Enter exactly the code above into newly created Module in Step 3
Hit ALT+F11 to store and near out of the VB editor
We now want to get the use of the code. All you need to do is enter formulation

=ShowFormula(Range) within the cellular you need to show your formulation in. See an instance below

Order Ref Quantity Price Total Show Formula

123 2 1.Ninety nine 3.Ninety eight =ShowFormula(D2)

234 three 2.50 7.50 =ShowFormula(D3)

In the Show Formula Column (assuming Order Ref Column is Column A)the formula =ShowFormula(D2) might show the calculation of the formulation in Total column which might be =B2*C2.

Give this a cross, a few easy VBA can allow you to demonstrate both your values and formulation at the same worksheet which lets in you to each reveal the common sense of your Excel solution and test your formulas workings.