There are two possible methods, first is Non-programming and second is programming (Macro).
Non-programming method:
- Click the Formula Tab.
- Find Formula Audit Group & Click Show
Formula Icon, available at top right.
Key Board Shortcut is Ctrl+`.
In Formula Audit Group you find Trace
Precedents, just click it to see the
relationship.
Programming method (VBA Macro):
Sub ExtractAllFormulas()
Dim sht As Worksheet
Dim shtName
Dim myRng As Range
Dim newRng As Range
Dim c As Range
ReTry:
shtName = Application.InputBox("Write name of the new sheet to list all formulas.", "New Sheet Name")
If shtName = False Then Exit Sub
On Error Resume Next
Set sht = Sheets(shtName)
If Not sht Is Nothing Then
MsgBox "This sheet already exists"
Err.Clear
Set sht = Nothing
GoTo ReTry
End If
Worksheets.Add.Move after:=Worksheets(Worksheets.Count)
Application.ScreenUpdating = False
With ActiveSheet
.Range("A1").Value = "Formula"
.Range("B1").Value = "Sheet Name"
.Range("C1").Value = "Cell Address"
.Name = shtName
End With
For Each sht In ActiveWorkbook.Worksheets
If sht.Name <> shtName Then
Set myRng = sht.UsedRange
On Error Resume Next
Set newRng = myRng.SpecialCells(xlCellTypeFormulas)
For Each c In newRng
Sheets(shtName).Range("A65536").End(xlUp).Offset(1, 0).Value = Mid(c.Formula, 2, (Len(c.Formula)))
Sheets(shtName).Range("B65536").End(xlUp).Offset(1, 0).Value = sht.Name
Sheets(shtName).Range("C65536").End(xlUp).Offset(1, 0).Value = Application.WorksheetFunction.Substitute(c.Address, "$", "")
Next c
End If
Next sht
Sheets(shtName).Activate
ActiveSheet.Columns("A:C").AutoFit
Application.ScreenUpdating = True
End Sub
How it works:
- Insert this VBA code as Module.
- As soon you Run it will display Inputbox.
- Write a New Sheet name & finish with Ok.
This code will list all the Formula in new sheet.