List All Formulas in Workbook

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.

formulalist03 

To get started, you can see where the formulas and constants are located, and colour code those cells.

Copy of formatformulas09 

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.

FormulaView03

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.

formulalist02 

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.

_______________

You may also like...

23 Responses

  1. Terry Lavelle says:

    Hi,
    This could be handy. I’ve done a similar thing manually by copying the sheet and doing a find and replace – find “=”, replace with ” =”. Then if you want to restore the formula for any reason you can remove the space and it’s done. The first time I tried it I replaced with “‘=”, but you can’t search for a ‘ (as far as I know), so space was the answer. BTW, the reason I wanted to do this was so I could print the formulas with grid lines showing, or remove rows and columns and preserve the references. Not something that comes up all the time.
    But your code is nice. Thank you.

    • Thanks Terry, and your solution of replacing the equal signs is a good workaround too. That solution also works when you want to copy formulas to a different location, without adjusting the references.

  2. Adil Modak says:

    i.e:
    17 18 19 20 21 22
    Yes Yes
    Yes No
    if 19 and 21 row contains Yes, then which formula can return Column Name(i.e 19,21) in 22.

    Please help, highly appreciated.

  3. Gary says:

    I don’t know it’s possible, but it would be amazing if you could do this in reverse!!!

    I have a budget excel file I use to keep score on my finances. The 1st worksheet “Monthly Budget” is a summary of all my different expenses, so it has cells that refer to other worksheets like “Groceries”, “Gas” & “Entertainment” taking the sum of each page, but I don’t want to loose track of previous months, so each month I start new columns in each category. So from the main “Monthly Budget” sheet I have to go in and modify each cell value to reflect the current month.

    I’ve been trying to find some function that says something like (if the current date month is = 10 then =Groceries!B31 elseif current date month – 11 then =Groceries!E31, etc. (or case 10 do X, case 11 do Y

    But if there was a way to reverse what you’ve done, that would be just as good ;-)

    Thanks 4 sharing,

    Gary

    • Bob says:

      I know you posted this some time ago, but in case you and/or others are looking for a solution wouldn’t the following simple type of formula work for you? It isn’t super elegant but if it works… :-)

      =IF(month(January),Groceries!B31,IF(month(February),Groceries!B32, etc))

  4. rajeev rathour says:

    Is there any formula to convert numeric data in text format.

  5. mudasiriqbal says:

    please give me excel formulas………..

  6. shawn says:

    hello..may i asking anyone who know how is the formulas to prevent the double entry.thanks a lot for help…

  7. Bob says:

    Thanks for the “List Formulas” code, it worked well on a couple of small test workbooks.

    I am currently running it on my project workbook, phew is it supposed to take so long, it has been running for over 30 minutes, with no clue how much longer it will be?

  8. Kate says:

    Hi, need some help please. I have a monthly budget sheet. If there are 25 days in my month, and the total i need by day 25 is X, what formula can i use for it to automatically work out what i need each day to achieve this. I dont mean X / 25 = Y x 25 = X, I mean on day one I need X to reach X, but on day to i’ll need X – what I did yesterday divided by the remaining number of days. Help ??

    • zain says:

      =NETWORKDAYS(A1,A2,B1)
      Where sample is.
      If A1=1-Mar-15
      A2=31-Mar-15
      B1=23-Mar-15 (Holiday)

      The result will be 21 Working Days you can combine more than one formula for your calculations to get the desired results.
      in this formula it will not count Saturday and Sunday and the specific holiday other than sat and sun as mentioned in formula regarding 23-Mar-15.
      +92 3317506364

  9. AK says:

    In Excel I find a formula which points to two separate workbooks. For example in my Model I might have a link saying =”C:\ABC.xls” + “C:\DEF.xls”.
    My problem is if there’s more than one link I have no way of opening those except by manually going into Windows Explorer and then opening them. So I’d like a way of making this much simpler.

    My vision would be if I run the macro on a certain cell I get a pop-up giving me a list of all the files referenced in that cell, and it then open whichever I choose.

  10. David says:

    I thought wow, this is excellent, but as one other person wrote, after it does a few worksheets it pretty well grinds to a stop. Almost need to do each sheet one at a time if the workbook is large.

  11. Industrious says:

    Hi there

    i am working on a result computation and i have got to a point where there is no road again, can somebody help me out

    it goes thus;
    each grade has point. like
    A—–4.0
    AB—-3.75
    B—–3.0
    BC—-2.75
    C—–2.25
    CD—-2.0
    D——1.75
    F——1.25

    And a students is offering the following courses with course unit

    course. units
    English. 3
    Maths. 4
    Chemistry. 2
    total units. 9
    etc

    the student got
    English A
    Maths. BC
    Chemistry CD

    in calculating
    You needs to multiply point of the grade scores with the unit of the courses and add all the result together and divide it by the total units of the course

    pls how do i achieve these by putting all the Grades aligned with the point into drop down
    and the moment i select all the grade the student score in each subjects it calculates automatically

  12. Lonoko Junior Paul says:

    I have a large file which consists of item description with details of the vendor or staff name included in the description. There is no staff analysis I can use so that I can use the Vlookup formula to get the vendor name alone without the rest of the item description. For example: April 2015 Salary>Paulo Ngasitia Lonoko>Finance and Grants Manager, Jonglei and Awerial offices. I would have also used the Right or left formula but it happens that some of the item descriptions are shorter than ohers. Thank you in advance for your help.

    Regards and waiting

  13. محمد says:

    Thank you for this is code
    but I need all functions in all work book in one sheet

  14. Douglas Eckert says:

    Debra: I believe there is a tool in Excel 2013 which allows users to list all of the formulas used in a workbook on a new worksheet – if I could only remember what it was.

    So, do you know if this is true? If not, I would like to use your solution above, but I am new to entering code into Excel. What is a regular module?

    Doug in York PA

    • Doug, there is a new function — FORMULATEXT — that lets you show the formula from a specific cell.
      You can also paste a list of names and their formulas on a worksheet (Formulas tab, Use in Formula, Paste Names, Paste List)
      And, if you select a pivot table cell, you can paste a list of any formulas use in it (calculated fields and items)
      I don’t know of any built-in tool to paste worksheet formulas though.

  15. ShirleyBufkin says:

    I need to see a list of Excel Formulas. I am working on a spreadsheet and I am stuck. I worked with Excel for over 30 years but have been retired since 2005 and I don’t remember the formula for my spreadsheet.

    The Problem: I have 6 columns but only three will be in the formula.
    column F is an amount that will be in each line of the spreadsheet.

    F2 has a forwarding balance of $7572.70. I need to pick up the 7572.70 and add column (4)d3(403.77 and
    subtract column 5 E3 0 for a total in column 5.as

  16. Greg says:

    When I try this, if there are any cells that reference another worksheet, the row is blank, with no ID number, sheet, etc. but all the other formulae are listed correctly. Does anyone know what I’m doing wrong?

  1. February 19, 2013

    […] few months ago, I shared my code for listing all the formulas in an Excel workbook. The code creates a new worksheet, with details on each formula’s worksheet […]

  2. July 2, 2015

    […] order to achieve this, I modified a brilliant script written by Debra Dalgleish which looks through a workbook, extracts all of the formulae and places them into a series of new […]

Leave a Reply to Greg Cancel reply

Your email address will not be published. Required fields are marked *