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 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.