Normally, in Excel, we can quickly display formulas in the cells, but, Excel doesn’t provide a feature for listing all formulas in a worksheet. Listing all formulas of a worksheet can help us to keep trace of the cell values with its real formulas. Here, I can talk about creating a VBA code to solve this problem.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following VBA code will help you to list the formulas of a selection or a worksheet, please do as follows:
1. Hold down the ALT + F11 keys, and it opens the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: List all formulas of a worksheet
Sub ListFormulas() 'Update 20141027 Dim Rng As Range Dim WorkRng As Range Dim xSheet As Worksheet Dim xRow As Integer xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8) Set WorkRng = WorkRng.SpecialCells(xlFormulas, 23) If WorkRng Is Nothing Then Exit Sub Application.ScreenUpdating = False Set xSheet = Application.ActiveWorkbook.Worksheets.Add xSheet.Range("A1:C1") = Array("Address", "Formula", "Value") xSheet.Range("A1:C1").Font.Bold = True xRow = 2 For Each Rng In WorkRng xSheet.Cells(xRow, 1) = Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) xSheet.Cells(xRow, 2) = " " & Rng.Formula xSheet.Cells(xRow, 3) = Rng.Value xRow = xRow + 1 Next xSheet.Columns("A:C").AutoFit Application.ScreenUpdating = True End Sub
3. Then press F5 key to run this code, and a prompt box will pop out to remind you to select a range or the whole worksheet that you want to list its formula cells, see screenshot:
4. And then click OK, formulas in your selection have been listed at once in a new worksheet in the front of your active worksheet which contains the information of cell address, formula and value as following screenshot shown: