How to read Excel formulas in the spreadsheet and transform them into a series of text strings.
More complex spreadsheets can be really jam-packed with formulas. Understanding how data is managed and processed may therefore not be immediate.
We present a simple procedure that allows you to extract all Excel formulas and display them as text without having to select each cell and check the contents of the Formula field under the menu bar.
By typing = TEXT.FORMULA () in an empty cell of the spreadsheet then indicating the reference box (containing a formula) in round brackets, you can read it in comfort.
By setting the formula as follows, you can make Excel show the formula text only when it is actually present in the indicated cell:
= IF (IS FORMULA (A2); TEXT.FORMULA (A2); “”)
The correct cell reference must obviously be replaced in place of A2 .
In fact, it may happen that a cell of the spreadsheet does not contain any formula, is empty or hosts a simple text string. Using the presented formula, the cell you are in will remain empty and will not show any error messages.
By dragging the formula just added by acting on the lower right corner it will be possible to automatically read the contents of the Excel formulas present in the cells immediately below.
The same approach can be used with traditional versions of Office, with cloud-based ones (Microsoft 365) as well as with Excel Online .