Can Excel show a formula and its result simultaneously?
I'm not aware of any built-in functions for this. You can, however, create a user-defined VB function to accomplish what you want.
Press Alt+F11 to open the VBA editor, right-click in the Project Explorer and select Insert -> Module. Paste the following code:
Function GetFormula(cell)
GetFormula = cell.Formula
End Function
Now you can use =GetFormula(A1)
to show the formula of that cell.
Perhaps worth noting that as of Excel 2013, there's a native function for this: FORMULATEXT
.
From support dot office dot com article on FORMULATEXT
function:
Description
Returns a formula as a string.
Syntax
FORMULATEXT(reference)
The FORMULATEXT function syntax has the following arguments:
- Reference Required. A reference to a cell or range of cells.
Remarks
- The FORMULATEXT function returns what is displayed in the formula bar if you select the referenced cell.
Here’s a way to automate what you were doing before. It combines the best parts of LonelyKnight’s answer to his/her own question, Indrek’s answer, and Billis’s answer:
Sub Show_Formulas()
Dim rng As Range, cell As Range
row_offset = 10
col_offset = 0
Set rng = Range(Cells(1, 1), Cells(5, 7))
On Error GoTo ErrHandler
For Each cell In rng
cell.Offset(row_offset, col_offset) = "'" & cell.Formula
Next cell
Exit Sub
ErrHandler:
MsgBox "The cow jumped over the moon", , "Error!", Err.HelpFile, Err.HelpContext
End Sub
For every cell in the specified range (here hard-coded as A1:G5
), it copies the cell’s formula,
protected by an apostrophe, to a cell a fixed offset away.
(See How do I add VBA in MS Office?
for guidance on using VBA in Excel.) You just have to remember to run this macro before you print.
Or, add a PrintOut , , , True
statement just before Exit Sub
,
and this macro will print the sheet for you.
(You just have to remember to print using this macro.)
The fourth parameter to PrintOut
is Preview,
set to True
to have Microsoft Excel invoke print preview before printing the sheet
(thus giving you the option to cancel)
or False
(or omitted) to print the sheet immediately, unconditionally.
Or, if you’re really concerned about forgetting to run this,
you can use Private Sub Worksheet_Change
to update the displayable formulas
any time you make any change in the worksheet.