When the same type of fields, category names, warehouse names, or status texts need to be uniformly adjusted across multiple Excel spreadsheets, opening xlsx files one by one to find and replace is not only time-consuming but also prone to omissions. This article explains how to use HeSoft Doc Batch Tool to batch search for keywords and replace them with new content in multiple Excel files, and highlight the modified cells for easy review of the processing results. It is suitable for batch file maintenance scenarios such as inventory sheets, product lists, ledgers, and statistical reports.
In daily office work, many Excel files do not exist independently but are saved separately by month, department, customer, project, or store. For example, in a batch of product inventory lists, the originally used category name is Stationery, which now needs to be uniformly changed to Office supplies; some warehouse locations were originally written as Warehouse C, and now need to be changed to Warehouse B. If there is only one file, you can simply find and replace directly in Excel; but if there are dozens or hundreds of xlsx spreadsheet files, opening, replacing, and saving them one by one takes a lot of time and is prone to missed changes, editing the wrong file, forgetting to save, and other issues.
This article aims to solve this typical repetitive task: batch find and replace keywords in many Excel spreadsheet files, while highlighting the replaced content for easy subsequent quick review. The following, combined with screenshots, will introduce how to use HeSoft Doc Batch Tool to complete this operation. It is a document batch processing software designed for office scenarios, with its core value being batch processing of files, reducing repetitive operations, and improving file organization and content processing efficiency.
Applicable Scenarios
Batch find and replace Excel keywords with highlighting is suitable for various spreadsheet maintenance scenarios, especially when it is necessary to unify field names, category names, region names, department names, and status descriptions.
- Batch update of inventory lists: Uniformly replace old category names in multiple inventory sheets with new ones, for example, replacing Stationery with Office supplies.
- Warehouse or store name adjustment: Uniformly change Warehouse C, which appears scattered in the spreadsheet, to Warehouse B, and highlight the modified positions.
- Standardization of department, project, and customer names: When organizational structures or project names change, updates need to be synchronized across multiple Excel and xlsx files.
- Cleaning ledgers and statistical reports: Batch correct old terms, incorrect writing, or inconsistent expressions in historical spreadsheets.
- Requirement for manual review after processing: After replacement, quickly locate modified positions through highlighting, reducing the cost of cell-by-cell checking.
Compared to manually opening each Excel file to execute "Find and Replace," batch tools are more suitable for office tasks involving numerous files, clear replacement rules, and a need for unified results.
Effect Preview: Before and After Processing
First, look at the sample spreadsheet before processing. The screenshot shows a product inventory table with headers including Product ID, Product Name, Category, Status, and Location. In the Category column, Stationery appears in multiple places, and in the Location column, Warehouse C appears in some positions. The screenshot uses red boxes to mark the content to be replaced, making it easy to understand the goal of this batch processing.
The effect after processing is as follows: the original Stationery has been replaced with Office supplies, and the original Warehouse C has been replaced with Warehouse B. Simultaneously, the replaced cells are set to a yellow highlight. This way, even if the spreadsheet has a lot of data, you can see at a glance which locations have changed, making it easier for reviewers to check if the replacements are correct.
From the before-and-after comparison, it can be seen that this processing is not simply modifying a single cell, but rather finding all keywords that meet the criteria in the spreadsheet and uniformly replacing them at multiple hit locations. For many Excel files, the operation logic is the same: first add the files to be processed to the task, then set the find and replace rules, and finally execute the batch processing.
Operation Steps
Step 1: Enter the Excel Tools and select "Find and Replace Keywords in Excel"
After opening HeSoft Doc Batch Tool , select "Excel Tools" from the function categories on the left. The main interface will display function cards related to Excel file processing. According to the screenshot, the first function is "Find and Replace Keywords in Excel," indicating that this function is used for batch finding and replacing keywords in Excel file content.
The purpose of this step is to enter the correct entry point for the batch processing task. For the needs of this tutorial, you need to click the "Find and Replace Keywords in Excel" function, not other tools like Excel to PDF, Excel to Word, or Add Password Protection for Excel. After entering this function, the software will guide the user through file selection, processing option settings, save location settings, and starting the process.
Step 2: Add the Excel files that need batch processing
After entering the function page, the top of the interface displays the current task name "Find and Replace Keywords in Excel." At this point, you are in Step 1 "Select records to be processed." The screenshot shows two entry points: "Add Files" and "Import Files from Folder." You can also see that multiple xlsx files have been imported into the file list area, which shows information such as serial number, name, path, extension, creation time, and modification time.
If you only need to process a few specified files, you can use "Add Files"; if many Excel spreadsheets are in the same folder, it is more suitable to use "Import Files from Folder," which can add all relevant xlsx files from that folder to the list at once. After adding, it is recommended to check the file list to confirm the file names, paths, and extensions are correct. The file extensions in the screenshot are xlsx, indicating that this batch task targets Excel workbook files.
The expected result of this step is: all Excel files that need batch find and replace appear in the task list. If a file does not need to be processed, you can use the delete icon on the right side of the list to remove it; if there is an import error, you can use the clear function on the interface to re-add. After confirming the files are correct, click "Next" at the bottom to enter the processing option settings.
Step 3: Set the search method and additional matching conditions
After entering "Set Processing Options," you will see the "Set Keyword Options" area. In the screenshot, the "Search Method" is selected as "Exact Text Search," with the option "Formula Fuzzy Text Search" also available. For this case, the goal is to replace the explicit text Stationery with Office supplies, and Warehouse C with Warehouse B, so selecting "Exact Text Search" is more intuitive.
In "Additional Options," the screenshot shows that "Ignore letter case" and "Match whole word only, not part of a word" are checked. These two options are useful for English keyword replacement. Ignoring case can avoid missed searches due to case differences; matching whole words reduces the risk of incorrect replacement, preventing a keyword that is just part of a longer word from being replaced. For Chinese content replacement, it is also recommended to carefully select matching rules based on the actual situation.
Step 4: Fill in the keywords to find and the replacement keywords
The screenshot shows the left area is the "List of Keywords to Find," and the right area is the "List of Replacement Keywords." In the example, the first row on the left is Stationery, and the first row on the right is Office supplies; the second row on the left is Warehouse C, and the second row on the right is Warehouse B. The software executes replacements corresponding by row, i.e., the find content in row 1 corresponds to the replace content in row 1, and row 2 find content corresponds to row 2 replace content.
This step is the core of the entire batch replacement task. Pay special attention to the following points when filling in: First, the number of rows and order on both sides must correspond to avoid replacing keyword A with content outside the rule for keyword B; second, English, numbers, and spaces must be accurate. The screenshot shows a space between Warehouse C and Warehouse B; if the space is inconsistent, it may affect the matching result; third, if a replacement keyword is left blank, the interface prompts "leaving blank means deletion," so do not leave it empty by mistake unless you indeed need to delete the found content.
For Chinese office documents, similar rules can be used to replace "华东一区" with "华东大区," "未完成" with "待处理," or old department names with new ones. As long as the content in the file is searchable text, multiple sets of replacement rules can be established in the same way.
Step 5: Enable "Highlight the replaced content"
In the "Other Settings" at the bottom of the screenshot, you can see the "Highlight the replaced content" toggle. This option is exactly the key requirement of this article's theme: not only to complete the batch find and replace, but also to mark the replaced results. In the example, after processing, the cells containing Office supplies and Warehouse B are displayed in yellow highlight, facilitating quick user identification of changed locations.
The benefits of enabling highlighting are very clear. After batch replacement is complete, without any marking, it is difficult for users to confirm which cells were modified, requiring another search or file comparison; whereas highlighting allows direct viewing of the processing results after opening Excel. This is very practical for tasks requiring submission for review, audit trails, or confirming the scope of changes. Especially when batch processing dozens of Excel files, highlighting can significantly reduce manual verification time.
Step 6: Set the save location and start processing
According to the interface workflow, after setting the keyword options, you need to continue clicking "Next" to enter "Set Save Location," and then enter "Start Processing." The workflow bar at the top of the screenshot has displayed these stages: Select records to be processed, Set processing options, Set save location, Start processing. It is recommended to choose an easily distinguishable output directory in the save location stage to avoid not being able to revert after overwriting the original files.
Before starting processing, you can recheck the file list and replacement rules: Are the files complete? Are the keywords written correctly? Does the replaced content correspond? Is the highlight toggle enabled? Execute processing after confirming no errors. After completion, open the output Excel file to see that the keywords have been replaced, and the replaced content has a highlighting effect.
Common Questions and Precautions
1. Why was some content not replaced?
Common reasons include inconsistent keyword writing, extra spaces, inconsistent letter casing, or overly strict matching rule settings. For instance, if there is a space in Warehouse C but the spreadsheet writes it as WarehouseC or Warehouse C, an exact match might not be possible. It is recommended to check the actual writing in the original spreadsheet first before filling in the keywords.
2. Will multiple sets of replacement rules interfere with each other?
Multiple rule sets are executed according to the list correspondence. To reduce the possibility of mutual interference, it is recommended to design the keywords and replacement content clearly to avoid a situation where one replacement result happens to match the next find rule. For complex tasks, you can test with a small number of files first, then batch process all files.
3. Can I replace without highlighting?
From the screenshot, "Highlight the replaced content" is a toggleable setting. If you do not need to mark the modification positions in Excel, you can turn this option off; if subsequent review is needed, it is recommended to turn it on.
4. Why is it recommended not to overwrite the original files directly?
Batch processing files has the characteristic of affecting multiple files at once. If the replacement rules are filled in incorrectly, overwriting the original files increases the cost of recovery. Therefore, when setting the save location, it is recommended to output to a new folder and archive or replace the original version after confirming the results are correct.
5. Are file types like xls, xlsx, xlsm all applicable?
The screenshot example imports xlsx files, and the function name points to keyword replacement in Excel file content. Actual usage should be based on the file formats supported by the software interface. For common Excel workbooks, it is recommended to import the files first and check if the extensions are recognized in the list.
Summary
Batch finding and replacing keywords in multiple Excel files and highlighting the replaced content is a very typical office automation requirement. Using HeSoft Doc Batch Tool , the work that originally required repeatedly opening spreadsheets, searching, replacing, saving, and reviewing can be organized into a clear batch processing workflow: select Excel Tools, enter "Find and Replace Keywords in Excel," add multiple files, set the find and replace lists, enable highlighting, set the save location, and start processing.
For large numbers of Excel files such as inventory lists, product catalogs, department ledgers, and project reports, this method can significantly reduce repetitive work, improve processing consistency, and enhance review efficiency through highlighted results. If you need to uniformly modify category, warehouse, status, or name fields in a batch of xlsx spreadsheets, it is recommended to first prepare the keyword mapping table, then follow the steps in this article to use the batch processing tool to complete the operation.