Creating a Function Inventory for a Workbook

Written by Allen Wyatt (last updated October 12, 2021)
This tip applies to Excel 2007, 2010, 2013, and 2016


Tim has a workbook that is rather large, and he wonders if there is a way to do an "inventory" of which functions are used in the various formulas in the workbook, along with the cells in which those functions are used.

This task is a bit harder that it may at first seem. You can make some rules as to what constitutes an Excel function, but those rules can get a bit hinky. For instance, you might think that a function is defined by an uppercase word followed by an opening parenthesis. This hasn't been true for a while, however, as function names can now contain periods.

The easiest way that I've found to get a function inventory is to develop a macro that will step through each formula in each worksheet in the workbook and check that formula against every possible worksheet function. The following macro will do that just fine:

Sub FormulaInventory()
    Dim EFunc(500) As String
    Dim iEFCnt As Integer
    Dim sFile As String
    Dim sTemp As String
    Dim SourceBook As Workbook
    Dim TargetBook As Workbook
    Dim TargetSheet As Worksheet
    Dim w As Worksheet
    Dim c As Range
    Dim iRow As Integer
    Dim J As Integer
    Dim K As Integer
    Dim L As Integer

    ' Read functions from text file
    sFile = ActiveWorkbook.Path & "\ExcelFunctions.txt"
    iEFCnt = 0
    Open sFile For Input As #1
    While Not EOF(1)
        Line Input #1, sTemp
        sTemp = Trim(sTemp)
        If sTemp > "" Then
            iEFCnt = iEFCnt + 1
            EFunc(iEFCnt) = sTemp & "("
        End If
    Close #1

    ' Sort functions; longest to shortest
    For J = 1 To iEFCnt - 1
        L = J
        For K = J + 1 To iEFCnt
            If Len(EFunc(L)) < Len(EFunc(K)) Then L = K
        Next K
        If L <> J Then
            sTemp = EFunc(J)
            EFunc(J) = EFunc(L)
            EFunc(L) = sTemp
        End If
    Next J

    ' Create and setup new workbook
    Set SourceBook = ActiveWorkbook
    Set TargetBook = Workbooks.Add
    Set TargetSheet = TargetBook.Worksheets.Add
    TargetSheet.Name = "Inventory"
    TargetSheet.Cells(1, 1) = "Function Inventory for " & SourceBook.Name
    TargetSheet.Cells(3, 1) = "Function"
    TargetSheet.Cells(3, 2) = "Worksheet"
    TargetSheet.Cells(3, 3) = "Cell"
    TargetSheet.Range("A1").Font.Bold = True
    TargetSheet.Range("A3:C3").Font.Bold = True
    With TargetSheet.Range("A3:C3").Cells.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
    End With

    ' Perform actual inventory
    iRow = 4
    For Each w In SourceBook.Worksheets
        For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)
            sTemp = c.Formula
            For J = 1 To iEFCnt
                If InStr(sTemp, EFunc(J)) Then
                    TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)
                    TargetSheet.Cells(iRow, 2) = w.Name
                    TargetSheet.Cells(iRow, 3) = Replace(c.Address, "$", "")
                    iRow = iRow + 1
                    sTemp = Replace(sTemp, EFunc(J), "")
                End If
            Next J
        Next c
    Next w
End Sub

Careful examination of the macro indicates that it reads in a text file (ExcelFunctions.txt) that includes a listing of all of Excel's worksheet functions. This file can be downloaded by using this link, and it should be stored in the same folder as the workbook that you are analyzing. (This means that the workbook you are analyzing must have already been saved in a folder.)

When the worksheet function names are loaded from the text file, the macro appends an opening parenthesis to each name. These names are stored in an array, and then this array is sorted according to the length of the function name, with the longest function names at the beginning of the array. This is done because of some ambiguity in function names. For instance, when the function-name array is read in you'll have one function named LEFT( and another named T(. When looking at a formula, if the LEFT( name is found then the T( name will also be found. By looking at the longest function names first and then removing those names from the formula string, then you eliminate the possibility of "false positives."

It should be noted that this function will only work with those worksheet functions listed in the ExcelFunctions.txt file. You can modify the file, as desired, to adapt what is located. It currently includes all standard worksheet functions for Excel 2016, but you may want to modify it to include functions made available by your add-ins, or you may want to delete functions that you find too esoteric. (Hint: You could also simply add an apostrophe in front of a function name in the text file and it will then never be included in an inventory.) There are just below 500 function names in the file now; if you add too many more, you'll need to change the number elements declared in the macro for the EFunc array.

When the macro is completed, you'll find your inventory in the new workbook that the macro creates. You should note that the time it takes the macro to complete can vary from quite fast to amazingly slow. The larger your workbook—the more worksheets it contains and the more formulas on those worksheets—the longer it will take to finish its work. Remember it has to check every formula in the workbook against almost 500 worksheet function names.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (5136) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Unique Name Entry, Take Two

If you need to make sure that a column contains only unique text values, you can use data validation for the task. This ...

Discover More

Deleting All Comments

Have comments in your document? Want to get rid of them all? The easiest way to do so is going to depend on the ...

Discover More

Selecting Text in Linked Text Boxes

Text boxes are often used as design elements in a document layout. If you have linked text boxes, you may have noticed ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Converting Strings to Numbers

When working with data in a macro, there are two broad categories you can manipulate: numbers and text. Sometimes you ...

Discover More

Recovering Macros from Corrupted Workbooks

Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...

Discover More

Creating a String in a Macro

Need to put together a bunch of characters to create a text string? You can do it in your macros by using the String ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is three less than 9?

2018-01-02 14:06:20


Jacob, the macro makes the assumption that there are actually formulas somewhere within each worksheet in the workbook. If you have a worksheet that doesn't have any formulas in it (perhaps it has just data), then it will fail on the line you mention.


2018-01-02 11:18:45


This seems like a very useful macro! Is anyone else having trouble getting it to execute? Debugger shows that the code keeps failing for me at line 62 (the perform actual inventory stage): "For Each c In w.Cells.SpecialCells(xlCellTypeFormulas) "

2017-12-30 11:22:22

Brian Lair

Wow, great solution with lots of tips-within-the-tip! (e.g., reading in a text file, sorting an array, the longer-strings-first idea, etc.) Thanks!!

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.