When the same type of formula expression needs to be uniformly adjusted across multiple Excel and xlsx spreadsheets, opening files one by one and locating cells individually is not only time-consuming but also prone to omissions. This article uses the example of batch replacing *2 with *3 in formulas to introduce how to use HeSoft Doc Batch Tool to perform find and replace only on cells containing formulas across multiple Excel files, thereby quickly updating formula expressions while keeping the table structure unchanged.
In everyday office work, many Excel tables are not simple data lists, but business templates, quotation sheets, statistical reports, or product catalogs containing numerous formulas. For example, a column uses a formula to concatenate quantity and unit into remarks, with the original formula being =CONCAT(D6*2,E6). Now, due to adjusted business rules, the multiplier 2 needs to be changed to 3. If there is only one table, manual modification might be acceptable; but if dozens or even hundreds of xlsx or xls files all use the same template, opening each file to modify the formula expression would be very inefficient.
The problem this article aims to solve is clear: batch find and replace formula expressions in many Excel table files. In the example, we batch replace *2 with *3 in formulas across multiple Excel files, changing results that originally calculated as 2000g, 400g, 2piece to uniformly become 3000g, 600g, 3piece. The entire process uses the office software " HeSoft Doc Batch Tool ", whose core value is batch processing files, reducing repetitive work, and is suitable for handling repetitive tasks in a large number of office files like Excel, Word, PDF, and images.
Applicable Scenarios: What situations are suitable for batch replacing Excel formula expressions
Batch replacing Excel formula expressions is not simple text replacement; it is more suitable for scenarios with "many files, consistent rules, and clear modification points". For instance, multiple Excel reports all reference the same calculation logic, and now the coefficients, rates, fixed text, or function segments in the formulas need to be uniformly replaced; or in product catalogs, price lists, and template lists, a column's formulas all contain the same expression and need unified adjustment according to new standards.
Common scenarios include: bulk modifying multipliers or discount coefficients in formulas within xlsx files; replacing old function segments with new ones in multiple Excel templates; uniformly adjusting units, prefixes, or suffixes of concatenated text in formulas; only replacing formula expressions, not the displayed values after formula calculation; finding and replacing the same formula keyword across multiple workbooks.
If done manually, you typically need to open each Excel file, locate the corresponding worksheet, and then modify the formula in the formula bar. As the number of files increases, problems like missed changes, incorrect modifications, or saving the wrong version arise. The advantage of using a batch processing tool is that you can import the Excel files to be processed together, then limit the processing to only formula cells through condition settings, and specify the find and replace content, thereby completing the batch update in one go.
Effect Preview: The *2 in the formula before processing generates results like 2000g
In the Excel table before processing, the content of the Remark column F is generated by a formula. In the screenshot, cell F6 is selected, and the formula bar shows =CONCAT(D6*2,E6). This means multiplying the quantity in cell D6 by 2, and then concatenating it with the unit in cell E6. Therefore, when column D is 1000 and column E is g, the displayed result in column F is 2000g; when column D is 200 and column E is g, the result is 400g; when the unit is piece, it will also result in 2piece.
This screenshot illustrates that what we need to change is not the 2000g displayed on the cell surface, but the formula expression hidden in the formula bar. If you only perform a normal replacement on the cell's displayed content, you might not accurately modify the formula logic; if you only replace the value, it might also break the subsequent automatic calculation ability. Therefore, the focus of this tutorial is to select "Formula Expression" as the processing object and change *2 to *3 in the formula.
Effect Preview: The *3 in the formula after processing generates results like 3000g
After the processing is complete, when you open the Excel file and check the same location, the formula bar has changed from =CONCAT(D6*2,E6) to =CONCAT(D6*3,E6). Because the multiplier in the formula expression has changed, the calculation results in column F update accordingly: the original 2000g becomes 3000g, 400g becomes 600g, and 2piece becomes 3piece.
From the before-and-after comparison, it is clear that the table's row and column structure, worksheet content, and cell styles have not been re-formatted due to the batch replacement. The changes are concentrated in the key segments within the formula expression and the results displayed after the formula calculation. This is precisely the desired outcome when batch finding and replacing Excel formulas: accurately modifying formula logic while minimizing impact on other content.
Operation Steps: Using office software to batch find and replace Excel formulas
Following the operation sequence in the screenshots, the steps below explain how to complete the batch replacement in HeSoft Doc Batch Tool . The interface may vary slightly between versions, but the core process is generally selecting the tool, importing files, setting processing options, setting the save location, and starting the process.
Step One: Enter Excel tools and select "Find and Replace Keywords in Excel"
After opening HeSoft Doc Batch Tool , select "Excel Tools" in the left tool category. The right side will display batch processing functions related to Excel files, such as Excel conversion, encryption protection, remove password protection, etc. Here, you need to select the first option, "Find and Replace Keywords in Excel". This function is used to batch find and replace keywords in Excel file content. Combined with subsequent option settings, it can further limit the processing to formula expressions.
The purpose of selecting this entry is to tell the software that this task pertains to Excel file content replacement, not format conversion or image export. For users needing to batch process table files like xlsx, xls, etc., entering the correct Excel tool category first helps prevent accidentally using functions for other file types.
Step Two: Add the Excel files to be batch processed
After entering the function page, the first step is "Select records to be processed". The upper right part of the interface has "Add File" and "Import Files from Folder" buttons. In the screenshot, 4 xlsx files have already been imported, showing their file name, path, extension, creation time, and modification time individually. You can choose the appropriate import method based on the number of files: click "Add File" when there are few files, or use "Import Files from Folder" when files are centralized in the same directory.
After importing, it is recommended to check whether all files in the list are Excel files to be processed this time. If a file does not need processing, it can be deleted via the action column. The purpose of this is to confirm the processing scope before batch execution, avoiding modifying unrelated Excel tables together. Once the file list is confirmed as correct, click "Next" at the bottom to enter the processing options settings.
Step Three: Set to process only cell text and limit it to formula expressions
On the "Set processing options" page, several options require attention. First, under "Processing Scope", check "Cell Text". As seen in the screenshot, "Worksheet Sheet Name" and "Text on Shapes in Worksheet" are not selected this time, indicating the processing target is focused on cell content, not worksheet names or shape text.
Next, in "Cell formula type to process", select "Process only cells containing formulas". This option is crucial because it prevents normal text cells from being mistakenly replaced. For instance, some cells might also contain characters like "*2", but they are not formulas. If the goal this time is to modify Excel formula expressions, you should limit the process to only cells containing formulas.
Then, in "Processing method for cells containing formulas", select "Formula Expression". This step determines that the software replaces the formula itself, not the value calculated by the formula. In the example, if you chose the calculated value, you might be facing display results like 2000g, 400g; what we truly intend to change is =CONCAT(D6*2,E6) in the formula bar, so you should choose "Formula Expression".
Step Four: Set find content to *2 and replace content to *3
In the "Set keyword options" area on the same page, select "Exact Text Search". In the screenshot, the "List of keywords to find" is filled with *2, and the "List of keywords for replacement" is filled with *3. This indicates that the software will look for *2 in the formula expressions of eligible cells and replace it with *3.
Note here that the asterisk is a multiplication operator in Excel formulas. The *2 in this example is a formula fragment, not ordinary business text. By limiting the search object to "cells containing formulas" and selecting "Formula Expression", the replacement behavior can be made more aligned with the requirement. After completing the settings, click "Next", follow the wizard to continue setting the save location, and then proceed to the start processing step.
Step Five: Set save location and start processing
The progress bar at the top of the screenshot shows that the tool flow also includes "Set Save Location" and "Start Processing". In actual operation, it is recommended to save the processed files to a new folder first, making it easier to distinguish and compare with the original files. After setting the save location, execute the start processing. Once the processing is complete, open the output file and check the formula bar and display results for typical cells to confirm that *2 in the formulas has been replaced with *3.
If you need to keep the original files as a backup, it is not recommended to overwrite the original files directly. The efficiency of batch processing is high, but it also means that if the condition settings are incorrect, the scope of impact can be large. Therefore, outputting to a new directory and performing spot checks before formal use is a more prudent office workflow.
Common Questions and Considerations
1. Why choose "Formula Expression" instead of the formula's calculated value?
Because this example needs to modify the formula logic. The value calculated by the formula is only the display result, such as 2000g, 400g, and is not equivalent to the formula itself. If you only replace the display result, changes in subsequent data for columns D or E might not cause the result to automatically recalculate according to the new rule. Selecting "Formula Expression" allows you to directly modify the *2 in =CONCAT(D6*2,E6).
2. Will it replace *2 in all cells?
According to the settings in the screenshot, the software will operate within the "Cell Text" scope and only process "cells containing formulas". This helps reduce the impact on normal text content. However, if some formulas also contain *2 but are not within the scope of this business task, they might also be matched. Therefore, it is recommended to confirm the consistency of the file templates before processing and, if necessary, test with a small number of files first.
3. Can xlsx and xls files both be processed using this approach?
The files imported in the screenshot are xlsx files. For other Excel formats, such as xls, xlsm, etc., support depends on the software's current functionality and the actual situation of the files. At the time of writing this article, the screenshots clearly show a batch processing workflow for xlsx files, so it is suggested that users refer to the file types that can be imported and processed in the interface.
4. Do Excel files need to be closed before replacement?
When batch processing office files, it is recommended to close the Excel file currently being edited first to prevent file occupation issues that could lead to processing failure or save anomalies. Additionally, backing up the original files before processing is best practice, especially for tables involving critical data like formulas, quotations, and statistical standards.
Summary: Batch replacing formula expressions can significantly reduce repetitive work
When the formula rules across multiple Excel tables need unified adjustment, manual modification is both slow and error-prone. Using HeSoft Doc Batch Tool , you can utilize the "Find and Replace Keywords in Excel" function to import multiple xlsx files at once and precisely target the processing object through options like "only process cells containing formulas" and "Formula Expression".
The example in this article batch replaced *2 with *3 in formulas; the results before processing were 2000g, 400g, 2piece, and after processing automatically became 3000g, 600g, 3piece. For office staff who frequently maintain Excel templates, product lists, price sheets, and statistical reports, this type of batch processing method can noticeably reduce repetitive work and improve file processing efficiency. It is recommended to back up files before official processing, verify with a small sample first, and then execute the batch process on all Excel files.