ThisCalculator

Everyday

Excel List All Formulas: Full Guide

Updated · Standard formula, shown below

Excel List All Formulas: Full Guide

Excel does not have a single built-in command that lists every formula, but you can use several methods to reveal all formulas in a workbook, including the Show Formulas feature, the Formula Auditing tools, and Go To Special. This guide explains each method, how to print or export a list, and how to get a list of all available functions from Microsoft’s official documentation.

Why You Might Need a List of All Formulas

When you inherit a spreadsheet from a colleague or revisit an old workbook, you often need to see exactly which cells contain formulas and what those formulas calculate. A complete list helps you:

  • Audit for errors – spot #REF! or #DIV/0! errors quickly.
  • Understand dependencies – see which cells feed into others.
  • Document your work – create a reference sheet for others.
  • Convert formulas to values – find all formulas before pasting values.

Excel offers several ways to surface formulas, each suited to a different task.

Method 1: Show Formulas in the Worksheet

The quickest way to see all formulas in a sheet is to use the Show Formulas button. This toggles the display from results to formulas for the entire worksheet.

  1. Go to the Formulas tab on the ribbon.
  2. In the Formula Auditing group, click Show Formulas.
  3. All cells with formulas now display the formula text instead of the calculated value.
  4. Click the button again to switch back to results.

Alternatively, press Ctrl + ` (the grave accent key, usually above Tab) to toggle the same view. This method shows formulas in place, but it does not create a separate list you can copy or print.

Method 2: Use Go To Special to Select All Formula Cells

To quickly select every cell that contains a formula, use Go To Special:

  1. Press F5 or Ctrl + G to open the Go To dialog.
  2. Click Special….
  3. Select Formulas and choose which types of results to include (numbers, text, logicals, errors).
  4. Click OK – all formula cells are now selected.

Once selected, you can format them (e.g., fill color), copy them, or view them in the formula bar. This method is useful for visually highlighting formulas, but it still does not produce a text list.

Method 3: Create a List of Formulas with Find & Replace

If you need a separate list of formulas with their cell references, you can use the Find & Replace feature to extract them:

  1. Press Ctrl + H to open Find and Replace.
  2. In the Find what box, type = (equals sign).
  3. In the Replace with box, type an apostrophe followed by equals sign: '=.
  4. Click Replace All.

This converts every formula into text (because the apostrophe makes Excel treat the cell as text). Now you can copy the range and paste it anywhere as a list. To revert, repeat the process replacing '= with =. This method is quick but alters your data, so save a backup first.

Method 4: Use VBA to Generate a Full List

For a comprehensive list of all formulas in a workbook, a simple VBA macro can loop through each worksheet and each used cell, writing the cell address and formula to a new sheet.

Sub ListAllFormulas()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim outputRow As Long
    
    ' Create a new sheet for output
    Set ws = Sheets.Add
    ws.Name = "Formula List"
    outputRow = 1
    
    ' Header
    ws.Cells(outputRow, 1) = "Sheet"
    ws.Cells(outputRow, 2) = "Cell"
    ws.Cells(outputRow, 3) = "Formula"
    outputRow = outputRow + 1
    
    ' Loop through all sheets
    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> "Formula List" Then
            Set rng = ws.UsedRange
            For Each cell In rng
                If cell.HasFormula Then
                    ws.Cells(outputRow, 1) = ws.Name
                    ws.Cells(outputRow, 2) = cell.Address
                    ws.Cells(outputRow, 3) = cell.Formula
                    outputRow = outputRow + 1
                End If
            Next cell
        End If
    Next ws
End Sub

To use this macro, press Alt + F11 to open the VBA editor, insert a new module, paste the code, and run it. The macro creates a new sheet named “Formula List� with three columns: Sheet, Cell, and Formula. This is the most efficient way to get a complete, exportable list.

Printing or Exporting the List

Once you have formulas visible (either via Show Formulas or the VBA-generated list), you can print or export them:

  • Print – Go to File > Print. In the settings, choose Print Active Sheets and set the print area to the range containing formulas. You may need to adjust page setup to fit columns.
  • Export to PDF – Use File > Save As and choose PDF. This works well for sharing a read-only view.
  • Copy to Word or Notepad – Select the cells with formulas, copy, and paste into a text editor. You lose formatting but get a plain-text list.

For a professional audit, the VBA method with a dedicated sheet is best because it gives you a clean table that you can sort, filter, or convert to CSV.

How to Get a List of All Excel Functions

If your question is about listing all available functions (like SUM, VLOOKUP, etc.), Excel does not have a built-in function to enumerate them. However, you can:

  • Press Shift + F3 to open the Insert Function dialog, which shows a categorized list of functions. You can browse through it, but it is not a text list.
  • Visit Microsoft’s official documentation for a complete list of functions by category. This is the most reliable source.
  • Use the Name Manager (Formulas tab > Name Manager) to list defined names, but this only shows named ranges, not functions.

For a quick reference, you can also type = in a cell and then press the fx button to open the Insert Function dialog, which has a search box.

Common Problems and Misconceptions

Many users expect a single “List Formulas� button, but Excel does not provide one. Instead, you have to combine the methods above. Here are common pitfalls:

  • Show Formulas only works for the active sheet – you must toggle it on each sheet individually, or use a VBA macro to do it for all sheets.
  • Find & Replace with apostrophe can corrupt data – always save a backup before converting formulas to text.
  • VBA macros require enabling macros – if your workbook is in .xlsx format, you need to save it as .xlsm to run macros.
  • Formulas in hidden sheets – the VBA macro above includes hidden sheets, but the Show Formulas method does not show hidden sheets unless you unhide them first.

Frequently Asked Questions

Does Excel have a built-in “List All Formulas� feature?

No, there is no single command that generates a list. The closest is the Show Formulas toggle, which displays formulas in place, but it does not create a separate list. For a true list, you need to use the VBA macro or the Find & Replace method.

How do I print all formulas in a workbook?

First, use the VBA macro to create a “Formula List� sheet. Then print that sheet. Alternatively, you can use Show Formulas on each sheet and print each one, but that is tedious for large workbooks.

Can I export formulas to a CSV file?

Yes. After generating a list with VBA, save the workbook as a CSV file. The CSV will contain only the active sheet, so make sure the “Formula List� sheet is active before saving.

Why does Show Formulas not work on some cells?

Show Formulas displays all formulas in the worksheet. If a cell contains a value that looks like a formula but starts with a space or an apostrophe, it is treated as text and will not be shown as a formula. Also, cells with formulas that are hidden by number formatting will still show the formula.

Is there a way to list formulas without VBA?

Yes, you can use the Find & Replace method with an apostrophe to convert formulas to text, then copy and paste. This is manual and can be error-prone, but it works for small sheets.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.