When the formula logic reference in Excel table is wrong, or to replace the original calculation formula with a new formula according to the regulations, we need to replace the formula reference in the table in batch. In order to avoid the tedious duplication of labor, here is a skill to teach you, you can replace the calculation formula in Excel form in batch with one key to realize one-to-one accurate replacement. The operation steps are also very simple and can be learned in one minute!
Batch Replace Excel Table Formula Usage Scenario
1. Overall adjustment of calculation logic
the original calculation formula in the table was addition. Now, according to business requirements, data needs to be multiplied, divided, or averaged. A table can be modified manually. If dozens or even hundreds of Excel tables need to modify the formula logic, professional batch processing tools are needed.
2. Wrong formula needs to be corrected uniformly
if the formula in the table refers to the wrong operator, it needs to be corrected.
3, adapt to the operation logic of different scenes
for the same form template, different calculation methods need to be used in different scenarios. For example, in item A, the data in the table needs to be all added, while in item B, the data in the table needs to be all multiplied.
4. Modify the calculation formula according to the original data
if the original data in the Excel table changes, the corresponding calculation formula should be adjusted accordingly. For example, the previous data is "quantity gift quantity", calculate the total quantity; However, the new data table has changed to quantity and unit price, so the calculation formula should also be changed to "quantity x unit price".
Table formula batch replace with new formula effect preview
before treatment:
after treatment:
Batch replace Excel table calculation formula operation steps
1, open. HeSoft Doc Batch Tool , select Excel Tools → Find and Replace Keywords in Excel.
2. Add all Excel forms to be processed. Add three ways to choose one: add files, import files from folders, drag and drop directly.
3. Select Cell Text → Process Only Cells Containing Formulas → Formula Expression. Cell Data Type Select Text and Number. Then select Find text exactly, enter the formula you want to replace in the left list, and enter the replaced formula in the right list.
4. After the processing is completed, click the save location to view, and all the specified formulas in Excel tables have been replaced.
