D
Donna
I have a speadsheet with many cells all containing a separate formula. How do
I print out the formula of each cell and not the result?
I print out the formula of each cell and not the result?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
E
Eduardo
Hi,
Press CTRL + ~ + Enter
~ is at the left of number 1
that will display all your formulas for each cell, print, to return to your
results press again as per above
Press CTRL + ~ + Enter
~ is at the left of number 1
that will display all your formulas for each cell, print, to return to your
results press again as per above
A
alanglloyd
Hi,
Press �CTRL + ~ + Enter
~ is at the left of number 1
that will display all your formulas for each cell, print, to return to your
results press again as per above
I think you meant . . .
Ctrl + <key left of 1>
.. . . & this toggles the formula display.
The Enter is not needed & some keyboards (ie mine) have a Reverse
Single Quote on the key. The tilde being at the right-hand end of the
third row, not the left-hand end of the first row.
Alan Lloyd
G
Gord Dibben
If you have many formulas using CTRL + ~ can get a little messy.
You can send all formulas to a new worksheet for easy printing using VBA
John's macro will create a new sheet then copy the formulas, addresses and
values to that sheet.
Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim WS As Worksheet
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If
' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With
' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell
' Adjust column widths
FormulaSheet.Columns("A:C").Cells.WrapText = True ''AutoFit
Application.StatusBar = False
End Sub
Gord Dibben MS Excel MVP
You can send all formulas to a new worksheet for easy printing using VBA
John's macro will create a new sheet then copy the formulas, addresses and
values to that sheet.
Sub ListFormulas()
'from John Walkenbach
Dim FormulaCells As Range, Cell As Range
Dim FormulaSheet As Worksheet
Dim Row As Integer
Dim WS As Worksheet
' Create a Range object for all formula cells
On Error Resume Next
Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)
' Exit if no formulas are found
If FormulaCells Is Nothing Then
MsgBox "No Formulas."
Exit Sub
End If
' Add a new worksheet
Application.ScreenUpdating = False
Set FormulaSheet = ActiveWorkbook.Worksheets.Add
FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
' Set up the column headings
With FormulaSheet
Range("A1") = "Address"
Range("B1") = "Formula"
Range("C1") = "Value"
Range("A1:C1").Font.Bold = True
End With
' Process each formula
Row = 2
For Each Cell In FormulaCells
Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
With FormulaSheet
Cells(Row, 1) = Cell.Address _
(RowAbsolute:=False, ColumnAbsolute:=False)
Cells(Row, 2) = " " & Cell.Formula
Cells(Row, 3) = Cell.Value
Row = Row + 1
End With
Next Cell
' Adjust column widths
FormulaSheet.Columns("A:C").Cells.WrapText = True ''AutoFit
Application.StatusBar = False
End Sub
Gord Dibben MS Excel MVP