The question that I have been asked most often is “How are you making the formula appear on the screen like that?”
It’s not an Excel feature, but rather programming code I wrote years ago so that I could teach more effectively. I consider the tool that I use to be proprietary, but I am willing to share some programming code that will give you similar functionality. Or if you’d rather not use a macro, scroll down to learn about the FORMULATEXT function.
Macro-based approach
Click each image and follow the numbered steps. The programming code you need to copy and paste appears after the series of images:
Copy this programming code below to paste into the Formula Comment macro described during my presentation:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
'Declare rng variable as Range Dim rng As Range 'Instruct Excel to skip over errors in code On Error Resume Next 'Loop through each range within the cells selected For Each rng In Selection 'Capture the formula for the current cell strFormula = rng.Formula 'If cell is not blank then... If strFormula <> "" Then 'Erase existing cell note (Office 365 ) or comment (Excel 2019 and earlier) rng.ClearComments 'Add note (or comment) to cell rng.AddComment 'Set the text of the note (or comment) to be the formula rng.Comment.Text strFormula 'Set the width of the note (or comment) rng.Comment.Shape.Width = WorksheetFunction.Max(40, WorksheetFunction.Min(Len(strFormula) * 10, 500)) 'set height of the note or comment rng.Comment.Shape.Height = WorksheetFunction.RoundUp(Len(strFormula) / 70, 0) * 15 'Change the font size of the note (or comment) text to be 12 rng.Comment.Shape.TextFrame.Characters.Font.Size = 12 'Bold the text of the note (or comment) text to bold rng.Comment.Shape.TextFrame.Characters.Font.Bold = True 'Set the note (or comment) to be visible rng.Comment.Visible = True End If 'Loop through to next cell in selection Next 'Turn off error-handling so that additional errors stop the code from running On Error GoTo 0 |
Now, let’s say that you’d rather not use a macro? In Excel 2013 and later you can display formulas by using worksheet functions instead:
If you’ve found this page helpful, please consider buying me a cup of coffee on Ko-Fi!
