List All Formulas in Workbook

If you’re working on a complicated Excel file, or taking over a file that someone else built, it can be difficult to understand how it all fits together. To help understand the file setup, use the following macros to list all formulas in workbook.

Excel has formula arrow, to show dependent cells, but that can get confusing, if there are lots of formula cells.

To get started, you can see where the formulas and constants are located, and colour code those cells.

View Formulas on the Worksheet

You can also view the formulas on a worksheet, by using the Ctrl + ` shortcut. And if you open another window in the workbook, you can view formulas and results at the same time.

Code to List Formulas

For more details on how the calculations work, you can use programming to create a list of all the formulas on each worksheet.

In the following sample code, a new sheet is created for each worksheet that contains formulas. The new sheet is named for the original sheet, with the prefix “F_”.

In the formula list sheet, there is an ID column, that you can use to restore the list to its original order, after you’ve sorted by another column.

There are also columns with the worksheet name, the formula’s cell, the formula and the formula in R1C1 format.

Copy the following code to a regular module in your workbook.

Sub ListAllFormulas()
'print the formulas in the active workbook
Dim lRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim c As Range
Dim rngF As Range
Dim strNew As String
Dim strSh As String
On Error Resume Next
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
strSh = "F_"
For Each ws In wb.Worksheets
  lRow = 2
  If Left(ws.Name, Len(strSh)) <> strSh Then
    Set rngF = Nothing
    On Error Resume Next
    Set rngF = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
    If Not rngF Is Nothing Then
      strNew = Left(strSh & ws.Name, 30)
      Worksheets(strNew).Delete
      Set wsNew = Worksheets.Add
      With wsNew
        .Name = strNew
        .Columns("A:E").NumberFormat = "@" 'text format
        .Range(.Cells(1, 1), .Cells(1, 5)).Value _
            = Array("ID", "Sheet", "Cell", "Formula", "Formula R1C1")
        For Each c In rngF
          .Range(.Cells(lRow, 1), .Cells(lRow, 5)).Value _
            = Array(lRow - 1, ws.Name, c.Address(0, 0), _
              c.Formula, c.FormulaR1C1)
          lRow = lRow + 1
        Next c
        .Rows(1).Font.Bold = True
        .Columns("A:E").EntireColumn.AutoFit
      End With 'wsNew
      Set wsNew = Nothing
    End If
  End If
Next ws
Application.DisplayAlerts = True
End Sub

Code to Remove Formula Sheets

In the List Formulas code, formula sheets are deleted, before creating a new formula sheet. However, if you want to delete the formula sheets without creating a new set, you can run the following code.

Sub ClearFormulaSheets()
'remove formula sheets created by
'ShowFormulas macro
Dim wb As Workbook
Dim ws As Worksheet
Dim strSh As String
On Error Resume Next
Application.DisplayAlerts = False
Set wb = ActiveWorkbook
strSh = "F_"
Set wb = ActiveWorkbook
  For Each ws In wb.Worksheets
    If Left(ws.Name, Len(strSh)) = strSh Then
      ws.Delete
    End If
  Next ws
Application.DisplayAlerts = True
End Sub

Download the Sample File

To download the sample file, please visit the Sample Files page on the Contextures website. In the UserForms, VBA, Add-Ins section, look for UF0019 – Formula Info List.

The file is zipped, and in Excel 2007 / 2010 format. Enable macros if you want to test the code.

_______________