Excel Course: IF Function, Copying Formulas

IF Function

Click on "Insert Function" and select the IF function.

insert if function - excel if function copying formulas

Our goal here is to have the function display "Yes" if the result is greater than ten, and "No" otherwise.

The logical test will tell us whether the function should display "Yes" or "No".

  • Logical test: D2>10 (this means that we are asking whether D2 is greater than 10)
  • If it is true, the function will display "Yes".
  • If it is false, the function will display "No".

Enter the required values and press OK.

window if function - excel if function copying formulas
If either of the values consists of text, it must be entered in quotation marks (example: "Yes").

A "Yes" will then be displayed in E2.

Now AutoFill down to cell E6.

fill if function - excel if function copying formulas

The results are correct (the formulas have been adapted: each time the row has been copied below, the cell row number in the formula has been incremented by 1).

result after fill - excel if function copying formulas

Now, if we want to change the limit value of 10 (which determines whether "Yes" or "No" should be displayed), we will have to edit each formula. It's not very practical.

So instead of entering the value 10 directly into the formula, we will refer to a cell that contains the number 10. Then if we want to change the limit value, all we will have to do is change the value in that cell.

limit - excel if function copying formulas

In order to do this, simply replace "10" with "A10":

use limit - excel if function copying formulas

AutoFill the formula.

fill formulas - excel if function copying formulas

You can clearly see that there is a problem because the reference to cell A10 has not been preserved in each of the formulas...

To fix this, we have to "fix" A10.

fill problem - excel if function copying formulas

One way to do this is to add a $ code before the row and column numbers.

So replace A10 with $A$10 (or with A$10 because in this case, we only need to "fix" the row number).

dollar - excel if function copying formulas

After AutoFilling, the results are now correct:

fill ok - excel if function copying formulas

Another option is to rename cell A10:

rename cell - excel if function copying formulas

And replace A10 with limit which in this case will be the name of the cell.

use cell name - excel if function copying formulas

After AutoFilling, the results are also correct in this case:

fill correct - excel if function copying formulas

You can now change the limit in all the formulas just by editing cell A10.

change limit - excel if function copying formulas
Download PDF files of this course