The FORMULATEXT operate is commonly dismissed as a result of it merely transforms a components into textual content. Nonetheless, that uncooked textual content is strictly what makes it so useful. So, cease pondering of it as a one-trick pony—it is the right device for auditing, enter management, and logic validation.

The FORMULATEXT operate has one argument: 

=FORMULATEXT(ref)

the place ref is a reference to a cell or, in trendy variations of Excel, a spread of cells.

If the ref argument is to a different workbook, that workbook have to be open. In any other case, the components will return the #N/A error. You may additionally see this error if the referenced cell does not comprise a components.

Listed here are 3 ways you need to use it in your spreadsheets at the moment.

Debugging formulation

The only use of the FORMULATEXT operate is simple components auditing.

Suppose you have been despatched a spreadsheet riddled with #DIV/0!, #VALUE!, #REF!, #SPILL!, and different errors. Choosing every cell one after the other to overview the components within the components bar is time-consuming. What’s extra, recognizing patterns within the errors is just about unimaginable.

An Excel spreadsheet containing various errors, including SPILL, DIV0, REF, and VALUE.

As a substitute, you need to use FORMULATEXT with IF and ISERROR in a single components to solely show the formulation of the cells the place an issue is detected.

To do that, choose the top-left cell the place you need the components audit to start, and enter the next components (changing each circumstances of A2:D19 with the vary you wish to audit in your spreadsheet): 

=IF(ISERROR(A2:D19),FORMULATEXT(A2:D19),"")

The results of this single components spills throughout the complete audit space, returning solely the formulation of cells containing errors.

An Excel worksheet that uses IF, ISERROR, and FORMULATEXT to replicate the formulas of cells where errors occur.

This automatic spilling effect is on the market in Excel 2021 or later, Excel for Microsoft 365, Excel for the net, and the Excel pill and cellular apps. In older variations, enter the components as a legacy array components by choosing all of the cells within the audit space, typing the components within the first chosen cell, and urgent Ctrl+Shift+Enter to commit the components to every cell.

This side-by-side view means that you can overview the formulation immediately and diagnose the root causes of errors. As quickly as you repair them, the formulation disappear from the audit space. What’s extra, it permits you to simply spot if the identical defective logic is utilized to a number of cells.

A hand holding a magnifying glass over the Excel logo, with spreadsheets and a green alarm clock in the background.


These 5 little-known Excel features save me hours every week

My coworkers all the time marvel how I end my Excel work so quick.

Documenting key formulation

You need to use the FORMULATEXT to create a library of all the important thing formulation in your workbook. This has three predominant advantages:

  • Improved understanding: It offers a central location the place anybody can go to grasp a workbook’s core logic.
  • Streamlined handovers: If another person is delegated to take care of the workbook, the components library serves as useful documentation of how every little thing works.
  • Method security internet: You possibly can periodically copy all the important thing formulation into one other worksheet (paste values) or doc for safekeeping. Date-stamp the pasted formulation to create a historical past of how a components has developed over time.

Making a components secret is easy, involving simply two columns on a devoted worksheet: a components label in column A and the components textual content in column B.

An Excel worksheet where a key formula library will be stored--column A is Formula Label, and column B is Formula Text.

Now, undergo every worksheet, and every time you hit a cell containing an vital components—equivalent to the ultimate gross revenue calculation in your Gross sales worksheet—kind a components label in column A, and in column B, kind: 

=FORMULATEXT(

Then, choose the important thing cell, shut the parentheses, and press Enter.

An Excel worksheet containing a key formula library, with a gross profit formula copied using the FORMULATEXT function.

Lastly, repeat the method for all the opposite key formulation in your workbook.

Format your key components library as an Excel table. That manner, every time you kind a components label on a brand new row in column A, the desk will broaden to seize the most recent addition, housing your index inside a single, nameable object.

Checking components consistency

For those who handle a big dataset, you may know that components consistency down a column is considered one of your largest high quality management complications.

For instance, if somebody by accident overwrites a components with a hard-coded quantity or one other components, as a result of the cell seems to have the right info, the error goes undetected. On this spreadsheet, regardless that cells J8, J11, and J14 look respectable at first look, they do not comprise the identical underlying mechanics as the opposite cells in column J.

An Excel table, with cells J8, J11, and J14 in the Profit column highlighted.

When used alongside conditional formatting, the FORMULATEXT operate helps you take care of this downside.

First, be sure the components within the first cell of the column is right, as this shall be used as an absolute anchor cell towards which you’ll confirm the consistency of the remainder of the column. Subsequent, choose the column you wish to examine (excluding the anchor) by choosing the second cell and urgent Ctrl+Shift+Down Arrow. Then, within the Dwelling tab, click on Conditional Formatting > New Rule.

All the cells in column J of an Excel spreadsheet, except the first cell, are selected, and New Rule in the Conditional Formatting drop-down menu is highlighted.

Now, click on “Use A Method To Decide Which Cells To Format,” and enter the next components (assuming you wish to examine column J): 

=IF(NOT(ISFORMULA(J3)),TRUE,IF(NOT(ISFORMULA($J$2)),FALSE,FORMULATEXT(J3)FORMULATEXT($J$2)))

the place:

  • IF(NOT(ISFORMULA(J3)),TRUE checks if the present cell is a hard-coded worth. Whether it is (TRUE), all different checks are bypassed, and the cell returns TRUE instantly.
  • IF(NOT(ISFORMULA($J$2)),FALSE checks if the anchor cell is a hard-coded worth. Whether it is, the method is stopped (FALSE).
  • FORMULATEXT(J3)>FORMULATEXT($J$2)) checks whether or not the present cell comprises a components that differs from the anchor cell (J2). In that case, it returns TRUE.
The New Formatting Rule dialog box in Excel, with Use A Formula selected, and a formula typed into the formula field.
An office desk with a book featuring the Excel logo on the cover, a function icon next to it and a keyboard.


The Beginner’s Guide to Boolean Logic in Microsoft Excel

Enhance your Boolean boon.

As a result of the reference to cell J3 is relative, the conditional formatting rule adjusts for every row. Alternatively, the anchor cell is absolute (therefore the greenback indicators), so it constantly serves as the purpose of comparability.

Subsequent, click on “Format,” and select a yellow cell fill to make the inconsistencies stand out.

A yellow cell fill is selected as the conditional formatting in an Excel spreadsheet.

Lastly, click on “OK” twice to shut each dialog containers and see the outcome.

Inconsistent cells in an Excel table column are highlighted yellow via conditional formatting.

If the anchor cell is a hard-coded worth, no cells shall be highlighted, so that you run the chance of decoding the column as error-free moderately than the examine having been aborted. For this reason it is essential you double-check the anchor cell for accuracy.

As quickly as you right the components in a highlighted cell, it’s going to lose the formatting—and, earlier than you already know it, you may find yourself with an error-free, constant spreadsheet.

Excel provides some ways to audit your spreadsheet. One in every of my favorites is the Go To Special tool, which helps you to rapidly choose and edit all cells of a particular kind—equivalent to all cells containing formulation, constants, or conditional formatting. Nonetheless, this built-in device solely tells you the place the formulation are. By treating the components as a textual content string, the FORMULATEXT operate transforms from a easy viewer into a robust high quality management device.

OS

Home windows, macOS, iPhone, iPad, Android

Free trial

1 month

Microsoft 365 consists of entry to Workplace apps like Phrase, Excel, and PowerPoint on as much as 5 gadgets, 1 TB of OneDrive storage, and extra.



Source link