When multiple Excel files contain formulas with the same structure, manually changing the formula expressions in cells one by one is not only time-consuming, but also easy to correct errors. This article describes how to use office software" HeSoft Doc Batch Tool ", batch search and replace the formula contents in Excel cells, for example, replace" * 2 "in the formula with" * 3 ". The article will combine the actual operation interface to explain clearly the applicable scenarios, effects before and after processing, detailed steps and matters needing attention, so as to help you quickly complete batch modification of formulas in xlsx, xls and other table files, reduce duplication of labor and improve formula adjustment efficiency.
In daily form processing, many people will encounter such problems: in the same batch of Excel files, many cells use similar formulas, and temporarily adjust the formula expression uniformly, such as changing multiplication by 2 to multiplication by 3, or replacing a certain function parameter in batches. If you manually open xlsx and xls files one by one and modify them cell by cell, it will not only be a lot of repetitive work, but also easy to miss and correct mistakes.
This article is to solve, isBatch modify formula expressions in Excel cellsThis question. With office software HeSoft Doc Batch Tool The "Find and Replace Keywords in Excel" function in, can directly targetCells containing formulasBatch find replace, and specifyFormula Expressionto process, not just the result of the formula calculation. This kind of batch processing method is especially suitable for scenarios where a large number of Excel tables need to uniformly adjust formula rules.
Applicable Scenarios
This approach is useful if you have the following needs:
- Formula expressions in multiple Excel files need to be modified in batches;
- In the same batch of tables, the formula structure is consistent, only one paragraph of content needs to be replaced;
- The multiplier, divisor, parameter and string splicing rules in the formula need to be adjusted uniformly;
- Do not want to manually check and modify file-by-file, worksheet-by-worksheet;
- Need to deal with xlsx, xls and other common Excel table files.
For example, the formula shown in the screenshot was originally=CONCAT(D6*2,E6)The goal is to put them.* 2uniformly replaced* 3, so that the result column will be from.2000gBatch becomes3000g.
Effect Preview
Before treatment
As you can see from the sample table, the selected cell is shown in the formula bar=CONCAT(D6*2,E6). In other words, the current result column is obtained by multiplying the value of column D by 2 and splicing it with the unit of column E, so the results on the right are mostly displayed as follows2000g,400g,2pieceand other content.
After treatment
After the batch replacement is completed, the formula expression becomes=CONCAT(D6*3,E6). The corresponding result is automatically updated3000g,600g,3pieceWait. That is, the software modification isThe formula itselfinstead of just changing the currently seen result text.
Operation steps
Step 1: Enter Excel Batch Find Replace function
Open Office Software HeSoft Doc Batch Tool On the left, selectExcel Tools. Find in the list of features and enter"Find and replace keywords in Excel".
The purpose of this step is to enter the dedicated function page that supports batch search and replacement of Excel content. Since this article is dealing with formula expressions, you need to start from here instead of manually modifying them one by one in Excel.
Step 2: Import the Excel file that needs to be processed
After entering the function page, click in the upper right corner"Add File", can also be used"Import files from a folder"to import multiple Excel files at once. Four xlsx files have been imported in the screenshot. The file name, path, extension, creation time, and modification time are displayed in the list.
The purpose of this step is to add all the tables that need to modify the formula uniformly to the task at one time. For scenarios with a large number of files, it is recommended to use import by folder first, which is more suitable for office scenarios of batch processing files.
When the import is complete, click at the bottom"The Next Step".
Step 3: Set the processing range to cell text
In the Set Processing Options page, first confirmProcessing rangeSelectCell Text". This item has been checked in the screenshot.
The purpose of this is to allow the software to perform find and replace at the cell content level. Because formulas are essentially expressions of content in cells, cell text needs to be processed here.
Step 4: Limit processing to only cells containing formulas
In"Type of cell formula that needs to be processed", select"Process only cells containing formulas". This is one of the key settings for this operation.
After this setting, the software will not process ordinary text or ordinary numeric cells, but only search and replace cells with formulas, which is more accurate and safer.
Step 5: Select to be processed by formula expression
In"Cell Handling with Formula", selectFormula Expression"instead of "value after formula calculation".
This step is crucial. Because your goal is to put=CONCAT(D6*2,E6)In* 2Replace* 3, that is, to modify the formula source code itself. If "value after formula calculation" is selected, the software will process the display result, for example 2000g, instead of the formula expression.
The expected result is that the software will directly modify the formula content, and the result column will be refreshed automatically after Excel recalculates.
Step 6: Set find and replace content
In"Find", select"Find text with precision". then on the left"List of keywords to find"Input in* 2, on the right"List of keywords after replacement"Input in* 3.
This step represents: put all formula expressions in.* 2Batch Replace* 3.
As can be seen from the screenshots, the software supports the corresponding replacement lists on the left and right, so it is very suitable for handling batch adjustment requirements such as the following:
- Put* 2Changed* 3;
- Replacing a function parameter with a new parameter;
- Batch change old field names and old reference fragments to new writing methods;
- Adjust the string splicing content in the formula.
If your formula modification requirement belongs to "fixed fragment replacement", this function is very efficient.
Step 7: Continue the process and start processing
After completing the options settings, click at the bottom of the page"The Next Step", and then continue to set the save location according to the software process, and enter the start processing link.
Although the screenshot focuses on the first two steps and core replacement settings, it can be seen from the process at the top of the page that the following also includes"Set Save Location"and"Start processing". After the process is executed, the software will batch process the imported Excel files.
After the processing is completed, it is recommended to randomly open one of the result files to check the formula bar and confirm that the formula has been changed from=CONCAT(D6*2,E6)becomes=CONCAT(D6*3,E6)while the result column has been changed from2000gbecomes3000g.
Why this method is suitable for batch modification of Excel formulas
Compared with traditional manual modification, this batch processing method of office software has several obvious advantages:
- Process multiple files at once: There is no need to open xls and xlsx tables one by one;
- Replace for the formula itself: Avoid changing only the display value and truly complete the adjustment of the formula expression;
- Reduce duplication of work: Especially suitable for templated reports, periodic data tables, batch quotations;
- Reduce the error rate: uniform rule replacement is more stable than manually changing cells one by one;
- Suitable for standardized office processes: For teams that often need to change formulas in batches, the efficiency improvement is obvious.
Frequently Asked Questions or Precautions
1. Why must I choose "formula expression"
Because what you want to modify is the content of the formula, not the result calculated by the formula. For example, 2000g is only the result, what really needs to be replaced is the formula* 2. If the treatment is wrong, it may not achieve the desired effect.
2. Why is it recommended to check "Only process cells containing formulas"
This can avoid mistakenly changing ordinary text or numeric cells and improve the replacement accuracy. This restriction is necessary especially when there are titles, notes, and result columns in a workbook at the same time.
3. Do you want to confirm that the style is consistent before keyword replacement
To. When modifying Excel formulas in batches, it is best to sample and confirm that the target formulas are written in the same way. For example, the search in this example is* 2If some formulas are written* 2Or the structure is different, then this part may not be replaced by the same.
4. Is it recommended to back up files before processing
Recommendations. Although batch processing of office software can significantly reduce manual operations, it is more secure to keep the original documents before processing for important statements, financial statements and statistical tables, making it easier to check the differences before and after.
5. Which Excel formats are suitable for this method.
Judging from the import file in the screenshot, the software can handle common Excel files, suchxlsx. In actual office scenarios, it is usually also involved.xlsand other formats, suitable for unified integration into the batch processing process.
Summary
If you are looking for a way to be ableBatch modify formula expressions in Excel cells, instead of manually changing the cells one by one, then use HeSoft Doc Batch Tool will be more efficient. Through the "find and replace keywords in Excel" function, you can import multiple Excel files in batch first, then limit only the cells containing formulas to be processed, and accurately replace them according to the "formula expression" method, thus making similar* 2Such formula fragments are quickly and uniformly changed* 3.
For office workers who often deal with xlsx and xls tables, or need to adjust formula rules in batches, the value of this kind of office software is very direct:Batch processing of documents, reduce duplication of labor, improve efficiency. If you happen to have a batch of Excel files that need to be modified in a unified way, it is recommended to import a group of sample files for testing according to the steps in this article, and then process the official files in batches after confirming the effect.