When the same formula snippet exists in multiple Excel or xlsx spreadsheet files, opening and searching/replacing them one by one is time-consuming and prone to omissions. This article uses the batch replacement of "*2" in formula expressions with "*3" as an example to introduce how to use HeSoft Doc Batch Tool to uniformly search for and replace formula content across multiple Excel files. The article will combine before-and-after effect images and software operation screenshots to explain applicable scenarios, key settings, operation steps, and precautions, helping users complete batch processing safely and efficiently.
In daily office work, many Excel tables do not exist independently, but are saved as multiple xlsx files by month, department, project, and region. Whenever business rules change, the formulas in the table may also need to be adjusted simultaneously. For example, originally a column's formula was =CONCAT(D6*2,E6), but now the rule has changed, requiring the multiplier to be changed from 2 to 3, meaning it should be uniformly changed to =CONCAT(D6*3,E6). If there is only one workbook, manual find and replace might be acceptable; but if there are dozens or hundreds of Excel files, opening each one individually to modify the formula is not only time-consuming but also prone to omissions.
The problem this article aims to solve is: How to batch find and replace formula expressions in many Excel table files. The office software used here is HeSoft Doc Batch Tool , which is positioned as a batch processing tool for office files. Its core value is to centralize repetitive file processing actions, reducing the time spent on manual opening, copying, pasting, and saving. Below, with screenshots, we will demonstrate how to batch replace *2 with *3 in formulas within multiple Excel files, causing the formula calculation results to synchronize changes.
Applicable Scenarios: Which Excel formulas are suitable for batch find and replace
Batch replacing Excel formula expressions is suitable for scenarios where "the same formula fragment exists in multiple files and needs unified adjustment." For example:
- Price formulas, quantity formulas, and unit conversion formulas in multiple xlsx and xls tables need unified adjustment;
- A project template was copied many times, and later it was discovered that coefficients, multipliers, or reference text in the formulas need updating;
- Departmental reports, product lists, and inventory tables contain the same formula expressions that need batch modification;
- Only the formula itself needs to be modified, rather than arbitrarily replacing the values already calculated in the cells;
- You don't want to open Excel files one by one for manual find and replace, hoping to import files once for centralized processing.
The requirement in this example is typical: the formula contains an expression like D6*2, which ultimately displays results like "2000g," "400g," "2piece," etc. After the rule adjustment, the formula fragment *2 needs to be replaced with *3, thus changing the results to "3000g," "600g," "3piece." If done manually, the workload for this type of processing grows linearly with the number of files; using batch processing software, multiple Excel files can be added to a task together, and replacement rules set once for automatic processing.
Effect Preview: Formula is *2 before processing, result displayed as 2000g
First, look at the Excel effect before processing. In the screenshot, the selected cell is F6, and the formula bar displays the formula =CONCAT(D6*2,E6). That is, the content in column F "Remark" is not plain manually typed text, but is obtained by multiplying the quantity in column D by 2 and concatenating it with the unit in column E. Because many rows in column D are 1000 and column E's unit is g, the column F result displays as "2000g." If column D is 200, the result displays as "400g"; if the unit is piece, it also displays as "2piece."
This image illustrates a key point: What we want to change is not the simple display result, but the multiplier within the formula expression. If replacement is only performed on the cell display text, issues like incomplete replacement or inaccurate replacement positions may arise. Therefore, during batch processing, the target must be limited to cells containing formulas, and "Formula Expression" must be selected for processing.
Effect Preview: Formula changed to *3 after processing, result automatically becomes 3000g
After the processing is complete, open the Excel file again to check. In the screenshot, with cell F6 selected again, the formula bar has changed to =CONCAT(D6*3,E6), and the result in column F "Remark" has correspondingly changed to "3000g." Rows that originally displayed "400g" have become "600g," and rows that originally displayed "2piece" have become "3piece." This indicates that the batch replacement took effect on the formula expression, and Excel will recalculate the display results based on the new formula.
From the before-and-after comparison, it can be seen that batch find and replace for formula expressions is suitable for handling unified rule changes. As long as the target fragment in the formula is consistent, a single setup can synchronously replace the same formula fragment across multiple Excel files, avoiding manual modification file by file.
Steps: Using HeSoft Doc Batch Tool to batch replace Excel formulas
Step 1: Enter Excel Tools, select "Find and Replace Keywords in Excel"
After opening HeSoft Doc Batch Tool , you can see multiple office file processing categories on the left, including Word Tools, Excel Tools, PowerPoint Tools, PDF Tools, etc. Since the files to be processed this time are Excel table files, you need to first enter the Excel Tools category on the left.
In the Excel Tools list, select the first item "Find and Replace Keywords in Excel". From the interface description, it can be seen that this function is used to "Batch find and replace keywords in Excel file content." Although the name mentions keywords, combined with subsequent settings, it can not only process regular cell text but also target cells containing formulas to process the formula expression, making it very suitable for this example's requirement of batch modifying formula fragments.
The purpose of this step is to enter the correct batch processing function module. The expected result is to enter a step-by-step wizard page, where you can subsequently add the Excel files to be processed and set up the find and replace rules.
Step 2: Add the Excel files that need batch processing
After entering the "Find and Replace Keywords in Excel" function, the top of the interface displays the processing flow: Select records to process, Set processing options, Set save location, Start processing. The first step is to import the Excel files to be processed.
As can be seen in the screenshot, there are "Add File" and "Import Files from Folder" buttons in the upper right of the page. If you only need to process a few specific files, click "Add File"; if there are many Excel files in the same folder, using "Import Files from Folder" is more suitable. The screenshot shows that 4 xlsx files have been imported, displaying their file name, path, extension, creation time, and modification time respectively.
The purpose of this step is to add the Excel files requiring unified formula expression replacement to the task list. The expected result is to see all files to be processed in the file list, with the correct summary record count at the bottom. After importing, you can check if the file paths are correct to avoid adding files that shouldn't be modified to the task. If you find a file that shouldn't be processed, you can remove it using the delete operation on the right side of the list.
Step 3: Set the processing scope, limit to Cell Text
After clicking "Next" at the bottom, you enter the "Set Processing Options" page. The settings here are critical, as they determine where the software will search and replace, and whether it replaces the formula calculation result or the formula expression.
In "Set Excel Options," first look at "Processing Scope." The screenshot shows "Cell Text" is checked, while options like "Worksheet Sheet Name" and "Text on Shapes in Worksheet" are not checked. For this example, the formula is located in a cell, so selecting "Cell Text" is reasonable. If you only need to modify the formula and don't need to change the worksheet name or shape text, do not check other scopes to avoid expanding the processing range.
The purpose of this step is to tell the software to only search for the target fragment within the content of Excel cells. The expected result is a more precise processing scope, avoiding impacting the same text content in worksheet names or other objects.
Step 4: Process only cells containing formulas, and select "Formula Expression"
Continuing to look at the same settings page, in "Cell Formula Types to Process," the screenshot shows "Process only cells containing formulas" is selected. This step is very important because this time we want to replace *2 in the formula, not characters in plain text cells. If no restriction is chosen, the software might search in more cell content; if only cells not containing formulas are processed, this formula modification cannot be completed.
In "Processing method for cells containing formulas," the screenshot shows "Formula Expression" is selected, rather than "Formula Calculated Value." This means the software will find and replace text fragments within the formula itself. For example, find *2 in =CONCAT(D6*2,E6) and replace with *3. If you choose to process the formula's calculated value, the processing target becomes the display result like "2000g," not the multiplier in the formula, which is inconsistent with this example's requirement.
The purpose of this step is to precisely limit the batch replacement to the "formula expression of cells containing formulas." The expected result is that the formula itself is modified, and after processing, Excel will recalculate the displayed value based on the new formula.
Step 5: Set search mode and replacement content
In "Set Keyword Options," the screenshot shows "Exact text search" is selected. The "List of Keywords to Find" below is filled with *2, and the "List of Replacement Keywords" is filled with *3. This means the software will replace *2 with *3 in the qualified formula expressions.
This example uses a very short formula fragment, so special attention must be paid to the matching scope during actual operation. If other formulas in your Excel file also contain *2 and you do not want them modified, consider writing the find content more completely, such as searching for D6*2 or another more distinguishable formula fragment. The demonstration in the screenshot uniformly changes all qualifying *2 to *3, suitable for batch scenarios with consistent formula structures and clear rules.
The purpose of this step is to establish the correspondence between "Find Content" and "Replacement Content." The expected result is that in each processed Excel file, the qualifying formula fragments will be replaced according to the rules.
Step 6: Continue following the wizard to set the save location and start processing
After setting the processing options, click "Next" at the bottom of the page. From the flow at the top of the interface, you can see that there are two more steps: "Set Save Location" and "Start Processing." For the convenience of result verification, it is recommended to save the processed files to a separate location, or choose a suitable save directory according to the actual workflow. This way, even if backtracking is needed, you can quickly distinguish between original and processed files.
After completing the save location setting, follow the interface guidance to enter the start processing step. When processing is finished, open the output Excel file and check if the formula bar has changed from =CONCAT(D6*2,E6) to =CONCAT(D6*3,E6), and also check if the cell display result has changed from "2000g" to "3000g." If processing multiple files, you can spot-check the same positions in different files to confirm the batch replacement results are consistent.
FAQ & Considerations
1. Why choose "Formula Expression" instead of the formula's calculated value?
Because what we need to modify in this example is the formula itself. The formula's calculated value is just the result displayed by Excel, such as "2000g". If the business rule change is to change the multiplier from 2 to 3, you should modify the *2 in the formula expression, not directly replace the display result. After selecting "Formula Expression," Excel will automatically calculate the new display value based on the new formula.
2. Will writing "*2" as the find content affect other formulas?
Yes, as long as other qualifying formula expressions also contain *2, they might be replaced. Therefore, before batch processing, confirm whether the replacement rule applies to all imported files. If you only want to replace a certain type of formula, you can make the find fragment more specific to reduce the possibility of incorrect matches.
3. Can multiple xlsx files be processed at once?
Yes. As can be seen from the screenshot, multiple xlsx files have already been added to the task list. For Excel table files like xls and xlsx, the value of batch processing lies in importing once and executing uniformly across multiple files, avoiding the need to repeatedly open each file.
4. Should I back up before processing?
Backup is recommended. Batch formula replacement is an operation that affects calculation logic, so it's best to keep the original files before processing or output to a separate directory during the save location step. This way, even if you find the rule settings are inappropriate, you can quickly revert.
Summary: Using batch processing tools to reduce repetitive labor in Excel formula modification
When formula expressions in many Excel files need unified adjustment, manual modification is not only slow but also prone to omissions. Using the "Find and Replace Keywords in Excel" function of HeSoft Doc Batch Tool , you can import multiple table files at once, set the processing scope to cell text, limit it to processing only cells containing formulas, select processing for the formula expression, and then batch replace *2 with *3. After the processing is complete, the formulas and results will be updated synchronously.
If you are maintaining a large number of Excel, xlsx reports, or template files, it is recommended to prioritize using batch processing methods to complete unified formula modifications. This not only saves time on individual file operations but also improves processing consistency and reduces the risk of omissions caused by manual modification.