Shiklo Tools
PDF40
Image20
Calculators & Converters25
Text & Writing13
Developer26
Video14
Audio14
Document14
Data & Spreadsheet12
Web & SEO9
OCR & Scanning5
Archive & File12
Security & Privacy23
Health & Medical29
QR & Barcode5

Extract Formulas from Excel

List every formula in an Excel or OpenDocument workbook with its cell, value and functions, and export the list.

Open an Excel workbook (.xlsx or .xlsm) or an OpenDocument spreadsheet (.ods) and get a complete list of its formulas: the sheet, the cell, the formula itself and the value it last calculated. A summary shows how many formulas there are, which functions are used and how often, how many formulas currently show an error such as #REF! or #DIV/0!, and how many depend on other sheets or on other workbooks.

Filter the list by text, sheet or errors, then download it as a CSV or Excel file for documentation, reviews and audits, or to compare two versions of a model. Formulas filled down a column are listed for every cell with their own references. The workbook is read in your browser and never uploaded, so it is safe for financial models and confidential data.

How to use Extract Formulas from Excel

  1. Choose or drop an .xlsx, .xlsm or .ods file.
  2. Read the summary: formulas, functions used, errors and links to other sheets.
  3. Filter by text, by sheet, or show only formulas with errors.
  4. Download the list as CSV or Excel.

Features

  • Every formula with sheet, cell and last calculated value
  • Functions used, with how often each appears
  • Formulas showing errors such as #REF! and #DIV/0!
  • Cross-sheet and external workbook references
  • Filter by text, sheet or errors; export to CSV or Excel
  • Excel .xlsx, .xlsm and OpenDocument .ods; nothing is uploaded

What you can use the list for

Auditing a spreadsheet model means checking its logic, and the logic is in the formulas, scattered over thousands of cells and hidden behind values. A flat list makes them reviewable: sort it, search it for a function such as VLOOKUP or INDIRECT, find formulas that point to another workbook before you send the file on, or keep it as documentation of how a report is calculated. Comparing the lists of two versions shows exactly which calculations changed.

Errors and references

Each formula's value is the result Excel or LibreOffice saved when the file was last calculated, so the tool can show which formulas currently produce an error without recalculating anything. The most common error values are listed below. Formulas that refer to other sheets are counted separately because they break when sheets are renamed, deleted or split into separate files; references to other workbooks (shown by Excel as [1]Sheet1!A1) break when that file moves.

ErrorUsual cause
#REF!a referenced cell, row or sheet was deleted
#DIV/0!division by zero or by an empty cell
#N/Aa lookup such as VLOOKUP found no match
#NAME?a misspelled function or an undefined name
#VALUE!text used where a number is expected
#NUM!an impossible number, such as the square root of -1

Supported files

Excel workbooks in the current format (.xlsx, and .xlsm with macros, whose formulas are read while the macros are ignored) and OpenDocument spreadsheets (.ods) from LibreOffice and other office suites. OpenDocument formulas are shown in the familiar Excel style: [.A1:.B2] becomes A1:B2 and semicolons between arguments become commas. Old binary .xls files from Excel 97 to 2003 and password-protected workbooks cannot be read in a browser; open them in Excel or LibreOffice and save them as .xlsx first. Workbooks up to 100 MB can be opened.

Shared and array formulas

When you fill a formula down a column, Excel stores it once and marks the other cells as sharing it. The tool expands these, so every cell appears with its own references (=B2*C2, =B3*C3 …), just as you see them in Excel. Array formulas, entered with Ctrl+Shift+Enter in older Excel, are listed once for the cell that holds them. Hover over a formula to see whether it is shared or an array formula.

Frequently asked questions

How do I list all formulas in an Excel file?

Drop the .xlsx file on this page. Every formula is listed with its sheet, cell and value, and you can export the list.

Is my workbook uploaded?

No. It is read in your browser and never leaves your device.

Can it read .xls files?

No, the old binary .xls format can't be read in a browser. Save the file as .xlsx in Excel or LibreOffice first.

Does it recalculate the formulas?

No. It shows the values saved in the file, which are the results of the last calculation in Excel or LibreOffice.

Can I find formulas that show #REF! errors?

Yes. Turn on Only errors to list every formula whose value is an error such as #REF!, #DIV/0! or #N/A.

Does it work with LibreOffice files?

Yes, .ods spreadsheets are supported and their formulas are shown in Excel style.

Are formulas in hidden sheets included?

Yes. Formulas in hidden and very hidden sheets are listed too.

Related tools