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.
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
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
Application.ScreenUpdating = True
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:
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.