Excel Batch substitution formula for the ACE

Source: Internet
Author: User

In the cells of Excel, we can use formulas.

If you want to bulk replace the formulas for Excel cells, you cannot do this by using the normal find and replace feature. You have to use the following. Trick.

Look at the following table, for example, the formula in column C is:

C1=a1+b1

C2=a2+b2

C3=a3+b3

C4=a4+b4

C5=a5+b5

Now we're going to replace these formulas with the following:

c1=sheet2! a1+sheet2! B1

c2=sheet2! a2+sheet2! B2

c3=sheet2! a3+sheet2! B3

c4=sheet2! a4+sheet2! B4

c5=sheet2! a5+sheet2! B5

The method is as follows, first, find the menu "tools" → "options", pop up the following image.

In the diagram above, switch to the View tab, find formula, and precede it with √. Click "OK" to confirm the exit.

Then, select Menu "edit" → "replace" and pop up the following image.

Enter a in the lookup content, in the replacement for the inside input sheet2! A, then, click "Replace All".

Now it's time to replace the other.

Computer Tutorials

Again, "edit" → "replace", in the figure above, find content input B, replace with input sheet2! B, so, all are replaced, this is the method of batch substitution formula.

Notice that after the replacement, in figure one, you have to remove the √ in front of the formula, otherwise your formula will not be counted.

Related Article

E-Commerce Solutions

Leverage the same tools powering the Alibaba Ecosystem

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.