List All Formulas in Workbook

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. To help understand the file setup, use the following macros to list all formulas in workbook.

Excel has formula arrow, to show dependent cells, but that can get confusing, if there are lots of formula cells.

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.

_______________

27 thoughts on “List All Formulas in Workbook”

  1. 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.

    1. 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. 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. 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

    1. 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. 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?

  5. 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 ??

    1. =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

  6. 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.

  7. 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.

  8. 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

  9. 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

  10. 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

    1. 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.

  11. 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

  12. 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?

  13. Just a note – if you have a protected sheet, it will not build an F_ sheet. You just need to unprotect the sheet before you run the macro!

  14. Thanks for useful coding.
    However for Table, same formula is repeated across rows.
    Is there coding to list unique formulae only?

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.