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.
To get started, you can see where the formulas and constants are located, and colour code those cells.
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.
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.
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.
_______________
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.
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.
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
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))
Is there any formula to convert numeric data in text format.
please give me excel formulas………..
hello..may i asking anyone who know how is the formulas to prevent the double entry.thanks a lot for help…
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?
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 ??
=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
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.
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.
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
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
Thank you for this is code
but I need all functions in all work book in one sheet
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.
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
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?
@Greg, the sample file has VLOOKUP formulas that reference another sheet. Does the macro work correctly for you in that file?
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!
Good point, thanks Beth!
Thanks for useful coding.
However for Table, same formula is repeated across rows.
Is there coding to list unique formulae only?