Written by Allen Wyatt (last updated November 22, 2025)
This tip applies to Excel 2007, 2010, 2013, 2016, 2024, and Excel in Microsoft 365
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(525) 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
Wend
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
If w.Cells.SpecialCells(xlCellTypeFormulas).Count > 0 Then
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
End If
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 365, 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 525 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, 2016, 2024, and Excel in Microsoft 365.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
Want to replace all commas in a formatted number with periods, and vice-versa? There are a couple of approaches you can ...
Discover MoreDoes your macro need to know how many windows Excel has open? You can determine it by using the Count property of the ...
Discover MoreWant to get some input from the users of your workbooks? You can do it by using the InputBox function in a macro.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2025-12-10 16:07:27
J. Woolley
Notice the various versions of FormulaInventory only consider formulas that reference a function in the list of Excel functions or a function that was added to the list; formulas like =A1+B1 or =2*C1 are ignored. Perhaps the macro should be named FunctionInventory.
Here's a simple way to rectify this issue in my recent FormulaInventory3 version; add the following statements immediately before the 'Next c' statement:
If sEval = c.Formula Then
sAddr = Replace(c.Address(External:=True), _
sBook, "", 1, 1)
Cells(nRow, 1) = "'" & sAddr
Cells(nRow, 1).Hyperlinks.Add Cells(nRow, 1), _
SourceBook.FullName, sAddr
Cells(nRow, 4) = "'" & sEval
nRow = nRow + 1
End If
2025-12-08 10:28:30
J. Woolley
@Coach Dave
I plan to add FormulaInventory to My Excel Toolbox after improvements.
See https://sites.google.com/view/MyExcelToolbox/
2025-12-07 11:48:03
Coach Dave
I have wanted a formula "finder" for some time. Without the skills to do it myself. It seems excel is constantly releasing new formulas and functions do things 'better'. Now I can go back and update existing workbooks with the newer Thank you Allen.
J Woolley - thank you so much for your edits! The original one provided by the site crashed when no formulas were present. Moving those sheets to the end at least was a work around but your solution is very slick,
Using a combination of transpose, sort, unique and filter I have been able to create a sheet of the worksheets and the formulas in each across to make finding easier for me. By copying it into my workbook it also acts as an index. Right clicking between <> only makes 20 sheets visible and I have 35.
If you have a blog or youtube channel I would like to follow. Your skills are evident.
2025-12-04 15:29:14
J. Woolley
Here's an update of my FormulaInventory2 macro that downloads function names with version data from this ExcelFunctions.csv file on Google Drive:
https://drive.google.com/file/d/1Tv0iexODXPGXBMM9iV8YPRfgOrt3lJhU
I believe the data is complete; it was compiled from information located here:
https://bettersolutions.com/excel/functions/updates.htm
I will try to keep the ExcelFunctions.csv file current when new worksheet functions are added to Excel.
This FormulaInventory3 macro creates a new unsaved workbook/worksheet with 4 columns: Sheet!Cell, Function, Version, and Formula. The first column is hyperlinked to its source. The version information might be useful when you share the source workbook.
Notice FormulaInventory3 utilizes Private Sub Quick_Sort.
Sub FormulaInventory3()
Dim FnList() As String, v As Variant
Dim sBook As String, sEval As String, sFunc As String, sAddr As String
Dim nLast As Integer, nDex() As Integer, nLen() As Integer
Dim nRow As Integer, nX As Integer, n As Integer
Dim SourceBook As Workbook, w As Worksheet
Dim FormulaCells As Range, c As Range
'Identify ExcelFunctions.csv file on Google Drive
Const sURL = "https://drive.google.com/uc?id=" _
& "1Tv0iexODXPGXBMM9iV8YPRfgOrt3lJhU"
'Get CR-LF delimited list of functions; max is 32767 characters
'Split(...) returns 1D base-0 array; each element contains _
comma separated function name and version like ABS,2003
FnList = Split(WorksheetFunction.WebService(sURL), vbCrLf)
nLast = UBound(FnList)
If Len(FnList(nLast)) = 0 Then nLast = nLast - 1
'Add any (not many) functions to FnList as comma separated pairs: _
name (case sensitive) and version; don't worry about duplicates
For Each v In Array("COPILOT,Beta")
nLast = nLast + 1
ReDim Preserve FnList(0 To nLast)
FnList(nLast) = v
Next v
'Index length of each function name in FnList
ReDim nDex(0 To nLast), nLen(0 To nLast)
For n = 0 To nLast
nDex(n) = n
nLen(n) = Len(Split(FnList(n), ",")(0))
Next n
'Arrange nDex so function names sort longest to shortest
Quick_Sort nDex, nLen, 0, nLast
Set SourceBook = ActiveWorkbook
'Create and setup new active workbook/worksheet
Workbooks.Add
ActiveSheet.UsedRange.Clear
ActiveSheet.Name = "FormulaInventory"
Range("A2") = "Sheet!Cell"
Range("B2") = "Function"
Range("C2") = "Version"
Range("D2") = "Formula"
With Range("A2:D2")
.Font.Bold = True
.Borders.LineStyle = xlContinuous
.Borders.Weight = xlThin
.AutoFilter
End With
Range("A3").Select
ActiveWindow.FreezePanes = True
'Inventory functions (case sensitive) for all formulas in SourceBook
sBook = "[" & SourceBook.Name & "]"
nRow = 3
For Each w In SourceBook.Worksheets
Set FormulaCells = Nothing
On Error Resume Next
Set FormulaCells = w.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not (FormulaCells Is Nothing) Then
For Each c In FormulaCells
sEval = c.Formula
For n = 0 To nLast
nX = nDex(n)
sFunc = Split(FnList(nX), ",")(0)
If InStr(1, sEval, sFunc & "(", vbBinaryCompare) Then
sAddr = Replace(c.Address(External:=True), _
sBook, "", 1, 1)
Cells(nRow, 1) = "'" & sAddr
Cells(nRow, 1).Hyperlinks.Add Cells(nRow, 1), _
SourceBook.FullName, sAddr
Cells(nRow, 2) = "'" & sFunc
Cells(nRow, 3) = "'" & Split(FnList(nX), ",")(1)
Cells(nRow, 4) = "'" & c.Formula
nRow = nRow + 1
sEval = Replace(sEval, sFunc & "(", "", 1, -1, _
vbBinaryCompare)
End If
Next n
Next c
End If
Next w
Columns("A:D").AutoFit
Range("A1") = SourceBook.FullName
Range("A1").Font.Bold = True
End Sub
Private Sub Quick_Sort(ByRef nDex() As Integer, nLen() As Integer, _
nL As Integer, nU As Integer)
Dim nPvt As Integer, nTmp As Integer, newL As Integer, newU As Integer
newL = nL: newU = nU: nPvt = nLen(nDex(nL + (nU - nL) \ 2))
Do Until newL > newU
Do While nLen(nDex(newL)) > nPvt: newL = newL + 1: Loop
Do While nPvt > nLen(nDex(newU)): newU = newU - 1: Loop
If newL <= newU Then
If nLen(nDex(newL)) <> nLen(nDex(newU)) Then
nTmp = nDex(newL): nDex(newL) = nDex(newU): nDex(newU) = nTmp
End If
newL = newL + 1: newU = newU - 1
End If
Loop
If nL < newU Then Quick_Sort nDex, nLen, nL, newU 'Recurse
If newL < nU Then Quick_Sort nDex, nLen, newL, nU 'Recurse
End Sub
2025-12-02 14:43:32
J. Woolley
PY (Excel 365) is another function missing from ExcelFunctions.txt.
COPILOT is a new function in Excel 365 Beta.
Allen already added TEXTSPLIT to ExcelFunctions.txt.
So the statement in Sub FormulaInventory2 can be changed to
For Each v In VBA.Array("COPILOT", "DROP", "PY", "STOCKHISTORY")
2025-11-30 13:28:44
J. Woolley
Here are two more functions missing from ExcelFunctions.txt:
DROP (Excel 2024)
STOCKHISTORY (Excel 365)
Re. my Sub FormulaInventory2, replace the following statement
For Each v In VBA.Array("TEXTSPLIT")
with this statement
For Each v In VBA.Array("DROP", "STOCKHISTORY", "TEXTSPLIT")
Also, 'AFunc() As Variant,' can be removed from the first Dim statement.
2025-11-27 14:01:32
J. Woolley
Here's an alternate version of the Tip's macro that uses worksheet function WEBSERVICE to download LF delimited function names from the latest list at https://excelribbon.tips.net/ExcelFunctions.txt. More functions can be added in a VBA.Array(...) statement. All function names are treated as case sensitive.
This version creates a new unsaved workbook/worksheet with 3 columns: Sheet!Cell, Function, and Formula. The first column is hyperlinked to its source.
Sub FormulaInventory2()
Dim EFunc() As String, AFunc() As Variant, v As Variant
Dim SourceBook As Workbook, w As Worksheet
Dim FormulaCells As Range, c As Range
Dim sTemp As String, sAddr As String
Dim iEFCnt As Integer, J As Integer, K As Integer, L As Integer
Const sFile = "https://excelribbon.tips.net/ExcelFunctions.txt"
'Get LF delimited list of functions; max is 32767 characters
EFunc = Split(WorksheetFunction.WebService(sFile), vbLf)
iEFCnt = UBound(EFunc)
If Len(EFunc(iEFCnt)) = 0 Then iEFCnt = iEFCnt - 1
'Add functions to EFunc; don't worry about possible duplicates
For Each v In VBA.Array("TEXTSPLIT")
iEFCnt = iEFCnt + 1
ReDim Preserve EFunc(iEFCnt)
EFunc(iEFCnt) = v
Next v
'Sort functions longest to shortest
For J = 0 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
Set SourceBook = ActiveWorkbook
'Create and setup new active workbook/worksheet
Workbooks.Add
With ActiveSheet
.Name = "FormulaInventory"
.Range("A2") = "Sheet!Cell"
.Range("B2") = "Function"
.Range("C2") = "Formula"
.Range("A1:C2").Font.Bold = True
.Range("A3").Select
ActiveWindow.FreezePanes = True
'Perform inventory
L = 3
For Each w In SourceBook.Worksheets
Set FormulaCells = Nothing
On Error Resume Next
Set FormulaCells = w.UsedRange.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not (FormulaCells Is Nothing) Then
For Each c In FormulaCells
sTemp = c.Formula
For J = 0 To iEFCnt
'Perform case sensitive comparison
If InStr(1, sTemp, EFunc(J) & "(", vbBinaryCompare) Then
sAddr = Replace(c.Address(External:=True), _
"[" & SourceBook.Name & "]", "", 1, 1)
.Cells(L, 1) = "'" & sAddr
.Cells(L, 1).Hyperlinks.Add .Cells(L, 1), _
SourceBook.FullName, sAddr
.Cells(L, 2) = "'" & EFunc(J)
.Cells(L, 3) = "'" & c.Formula
L = L + 1
sTemp = Replace(sTemp, EFunc(J) & "(", "", 1, -1, _
vbBinaryCompare)
End If
Next J
Next c
End If
Next w
.Columns("A:C").AutoFit
.Range("A1") = SourceBook.FullName
End With
End Sub
2025-11-26 16:11:03
Allen Wyatt
@James: The primary place from which I garnered the information is here:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
That doesn't tell the whole story, though, as there are some functions that are not listed there. These are deprecated, but still work in Excel for compatibility with past versions.
As to TEXTSPLIT, I'm not sure how I missed that one. I've now updated the file to include it. If you find any others, let me know and I'll get them added.
-Allen
2025-11-26 15:50:13
J. Woolley
@Allen
I'm curious how you derived the ExcelFunctions.txt list of functions. While testing the macro I noticed the Excel 2024 TEXTSPLIT function is missing. For example, the following formula
=TEXTSPLIT("Hello World"," ")
adds function T to the inventory instead of TEXTSPLIT. It's difficult to determine if any other functions are missing.
2025-11-23 11:15:45
J. Woolley
@Allen
Perhaps my question was not clearly stated. Do you intend to update https://excelribbon.tips.net/ExcelFunctions.txt whenever new worksheet functions are added to Excel?
As you suggested, here is another way to save ExcelFunctions.txt after downloading in a web browser:
1. Press Ctrl+A to copy all of the values from ExcelFunctions.txt
2. Press Ctrl+V to paste them into a blank Notepad window or tab
3. Press Ctrl+S (or Ctrl+Shift+S) to Save as type: Text documents (*.txt)
4. Specify that file in Sub FormulaInventory
2025-11-22 18:09:15
Allen
@James
I updated the list just this past week, so it is as up to date as I know how to make it right now.
As to saving the functions from a web browser page, you should just be able to copy it and save it into a text file in Notepad.
-Allen
2025-11-22 15:45:58
J. Woolley
SpecialCells(xlCellTypeFormulas) will produce an error if there are no formulas; therefore, replace the following statements in Sub FormulaInventory
' Perform actual inventory
iRow = 4
For Each w In SourceBook.Worksheets
If w.Cells.SpecialCells(xlCellTypeFormulas).Count > 0 Then
For Each c In w.Cells.SpecialCells(xlCellTypeFormulas)
with these statements
' Perform actual inventory
Dim FormulaCells As Range
iRow = 4
For Each w In SourceBook.Worksheets
Set FormulaCells = Nothing
On Error Resume Next
Set FormulaCells = w.Cells.SpecialCells(xlCellTypeFormulas)
On Error GoTo 0
If Not (FormulaCells Is Nothing) Then
For Each c In FormulaCells
Saving text like TRUE or FALSE in a cell results in a logical value instead of text; therefore, after this statement
TargetSheet.Cells(iRow, 1) = Left(EFunc(J), Len(EFunc(J)) - 1)
add the following statements
If EFunc(J) = "TRUE(" Then
TargetSheet.Cells(iRow, 1) = "'TRUE"
ElseIf EFunc(J) = "FALSE(" Then
TargetSheet.Cells(iRow, 1) = "'FALSE"
End If
2025-11-22 15:44:37
J. Woolley
When I use Chrome to save https://excelribbon.tips.net/ExcelFunctions.txt as a text file, each line is terminated by LF instead of CR or CR-LF; therefore, VBA's Line Input statement does not separate lines as expected. To remedy this:
1. Press Ctrl+A to copy all of the values from ExcelFunctions.txt
2. Press Ctrl+V to paste them into cell A1 of a blank worksheet
3. Press F12 and pick Save as type: Text (MS-DOS) (*.txt)
4. Specify that file in Sub FormulaInventory
2025-11-22 11:43:25
J. Woolley
@Allen
Do you intend to regularly update the list of Excel's worksheet functions?https://excelribbon.tips.net/ExcelFunctions.txt
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments