OmniDocHow to batch modify and delete Excel formulas in batches
When there are a large number of formulas that need to be modified uniformly, how to use search and replace and auxiliary columns to complete batch modifications, delete suffixes, and apply IFERROR. Explain clearly the situations where each method is suitable, the specific steps, and how to check the results after completion.
Key points of this article
- When only changing a fixed text in the formula (such as removing the suffix, replacing the range reference), use Find and Replace and set the search scope to "Formula".
- To put IFERROR around each formula, you can first use FORMULATEXT to extract the formula text, then spell it into a new formula, and finally paste it back into the original column.
- Make a backup before making batch changes. After making changes, use "Show Formula" and error location check, and compare a few lines with the original results.
- IFERROR will hide all errors. Please confirm which errors need to be retained before applying.
The formulas of many tables are pulled down column by column, and it is only when they need to be modified that they are discovered: either each cell has an extra suffix, or all formulas must be covered with IFERROR, or the reference range needs to be expanded as a whole. Manually changing them one by one is slow and easy to miss. This article talks about the idea of Excel formula batch modification: how to change a certain part of the existing formula at one time instead of filling in a new formula.
People who are suitable for reading this article usually have dozens to thousands of lines of formulas in their hands and want to do three types of things: replace text or references in formulas in batches, delete fixed suffixes or coefficients in batches, and add error handling to formulas in batches. Below is a judgment table first, and then the specific steps and verification methods.
How to modify formulas in batches? One sentence conclusion: Use replacement to fix the text, and use auxiliary columns to set the shell.
First use search and replace to process changes that can be located by a fixed text; when you need to wrap or reorganize each formula as a whole, use auxiliary columns to generate new formula text, and then paste it back to the original place.
| your situation | Recommended practices | reason |
|---|---|---|
| The formulas all have the same suffix at the end, such as "*1.13" | Find and replace, select "Formula" for the search range | Only the specified text is changed, and content other than the calculation results is not affected. It is fast. |
| The reference area should be expanded as a whole, such as B2:B100 to B2:B200 | Find and replace, write the entire area | The replacement string must be written completely to avoid accidentally changing other references. |
| Every formula must be covered with IFERROR | Auxiliary column + FORMULATEXT | Replacement cannot directly handle the bracket structure, and the splicing is more reliable. |
| There are different structures mixed in the formula, and only some parts need to be modified. | Add filtering to auxiliary columns, or filter out target rows first | New formulas can be generated only for the required rows to reduce accidental injuries. |
| The changes involve a large amount of text description, such as title text | Test on a blank space before replacing | The text in the formula may be a string within quotation marks, and you need to confirm the replacement range. |
Before you start: Confirm these points first
Back up the original file first. Once batch changes are saved, it is difficult to restore them one by one. Make a copy of the worksheet, or save it as a new file, and make sure the original formula can be restored before starting.
Make sure the text to be changed is the same in all formulas. Replacement only recognizes content that is literally the same. For example, some formulas end with "1.13" and some with "1.130". They must be processed separately, otherwise they will be missed. You can first use the "Search" function (select "Formula" for the search range) to search and see how many hits there are.
Confirm Excel version. The FORMULATEXT function requires Excel 2013 and above. When the version is older, the replacement method can be used, and the auxiliary column cannot be used to generate formula text.
Determine which formulas should not be processed uniformly. Some formulas already have IFERROR, or intentionally retain error values to remind you of missing data. This type of formula must be excluded, otherwise it will be wrapped twice, or the error message will be swallowed.
Confirm whether the calculation results need to remain unchanged. Deleting the suffix will change the calculation result, and applying IFERROR only affects the errored cell. Before making modifications, you need to think clearly whether the purpose of batch modification is to change the results.
Steps to batch apply IFERROR to an entire column of formulas in Excel
Assume that the formula is in column C and the data starts from row 2. Now we need to apply IFERROR to each formula and display "-" when there is an error.
- Copy the original table, or confirm that it has been backed up.
- Enter
=FORMULATEXT(C2)in the blank column D2 and press Enter to get the text of the C2 formula with an equal sign in front. - Enter
="=IFERROR("&MID(D2,2,999)&","""-"")"in E2. MID removes the equal sign at the beginning, and IFERROR wraps it. The second parameter is the content displayed when an error occurs. Here it is "-", which can be changed to empty text or other text as needed. - Fill D2 and E2 down together to the last row of data.
- Select the result in column E, copy it and "Paste Special" as the value in column F to fix the text in column E.
- Select column F and use find and replace to replace "=" with "=". Excel will re-identify text that begins with an equal sign as a formula. This is a common practice for converting formula text. After replacement, check whether column F becomes the calculated result.
- Copy column F and paste it in its original position in column C. Because they are formulas on the same line, the references will automatically correspond.
- After confirmation, delete the auxiliary content in columns D, E, and F.
Check each item after completion
Don’t rush to close the file after making batch changes. Check them one by one according to the table below.
| Inspection items | How to see it | What should you do if there's a problem? |
|---|---|---|
| Have all the formulas been modified? | Press Ctrl+' (backquotes) to switch the display formula and browse several locations screen by screen | If you miss any changes, return to the replacement step and use a more complete search sequence to make corrections |
| Did you wrap an extra layer?] | Check if two layers of IFERROR appear at the beginning of the formula | Indicates duplicate processing; after undoing, exclude lines with existing IFERRORs |
| Whether the calculation results are reasonable | Compare with the same cell in the backup file and spot check 10 to 20 rows | If the result is biased, check whether the replacement string has accidentally damaged other references |
| Error value distribution | Use "Location Conditions" to select error values and see where the remaining errors lie | Identify which errors need to be kept, and switch to more precise judgments if necessary |
| Whether the reference points to the correct area | Open a few formulas and check the referenced range of highlights | After correcting the area, replace it uniformly again |
If the table contains text translated from OmniDoc or requires processing multiple documents simultaneously, note during verification: verify machine translation or OCR content item by item, especially headers and numbers, and do not rely directly on results.
📊 Core Comparison
| Method | Suitable changes | Advantages | Limitations |
|---|---|---|---|
| Find and Replace (Find Scope Select Formula) | Delete suffixes, replace fixed text, and expand the area | The steps are short, completed in just a few seconds | You can only modify literal text, but cannot handle parenthesis structures |
| Auxiliary column FORMULATEXT | Apply IFERROR, wrap function, and recombination formula | It can generate new formulas according to the rules | There are many steps; you need to paste the values into values and then return to the formula |
| Manually modify each item one by one | Formulas are few or structures vary | Direct and less likely to make mistakes | If there are too many lines, it becomes time-consuming and easy to overlook |
The key to replacement is that the replacement string must be specific enough. For example, changing "*1.13" to null will only hit formulas containing that string; If you directly replace "1," it will accidentally damage all parts containing the number 1.
❓ Frequently Asked Questions (FAQ)
1. How do you batch delete the suffix at the end of a formula?
Replace with 'Search', fill in the full suffix for the search content, for example, "*1.13", replace it with blank, and set the lookup range to "Formula". First, test it once in a small area, confirm the results are correct, then expand to the entire table.
2. Does IFERROR hide the real error?
Yes. IFERROR treats all errors equally, including reference errors and division by zero. It is recommended to first use "Positioning Conditions" to check existing error types, applying only to cases caused by empty values or missing data, and keeping the original formula in other cases.
3. Why did the results change after batch modification of formulas?
Most often, the replacement string is too broad, or the area referenced by the formula after replacement changes. Compare the backup files with each change before and after each change, find the lines with deviations, and then check the formulas for those lines.
4. How do you batch modify text in formulas, such as table names or titles?
Table names, region names, and text in quotes in formulas can be replaced or modified using lookup; just select the formula within the lookup range. When modifying text inside quotation marks, be aware that it will directly affect the result; if necessary, modify the corresponding data simultaneously.
📝 Summary
The key to batch modifying Excel formulas is dividing changes into two categories: those that can be fixed in a fixed text section and replace them with lookup; For the entire package or reorganization, use FORMULATEXT to generate new formulas and then paste them back. Regardless of the method, back up before modification, and after modification, verify using the display formula, error location, and sampling comparison.
