Creating A List Of Formulas
Most users have discovered that Excel has an option that lets you display formulas directly in their cells: Choose Tools Options, click the View tab, and select the Formulas checkbox. However, Excel doesn't provide a way to generate a concise list of all formulas in a worksheet. The VBA macro below inserts a new worksheet, then creates a list of all formulas and their current values.
NOTE: My Power Utility Pak add-in includes a more sophisticated version of this subroutine, plus several other auditing tools.
To use this subroutine:
- Copy the code below to a VBA module. You can also store it in your Personal Macro Workbook, or create an add-in.
- Activate the worksheet that contains the formulas you want to list.
- Execute the ListFormulas subroutine. The subroutine will insert a new worksheet that contains a list of the formulas and their values.
The ListFormulas Subroutine
Sub ListFormulas() Dim FormulaCells As Range, Cell As Range Dim FormulaSheet As Worksheet Dim Row As Integer ' 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").AutoFit Application.StatusBar = False End Sub
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks