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
Search for Tips
Browse Tips by Category
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