Create and edit Google Sheets IF formulas bit by bit

Text case sensitivity

Almost all IF Formula Builder operators are case-insensitive. They do not take text case into account. Words like east and East are treated as the same.

The only exceptions are 2 operators: is exactly and is exactly not. They consider the text case.

Formula recognition

Though the add-on recognizes existing IF formulas and breaks them down to pieces, we highly recommend using this feature only on formulas created by the tool.

You see, the same IF-THEN logic can be formulated in different ways with a different order of the arguments. But our add-on recognizes only those formula patterns that we put into the code and that are used to build IF statements. Since we can't foresee them all, this feature will be most useful when applied to the formulas build by the tool only.

Another thing to keep in mind is that the add-on recognizes operators based on the types of data in cells. If the formula references blank cells, the add-on will have difficulties understanding the formula.

Start the add-on

Go to the Extensions menu in Google Sheets, find IF Formula Builder in the list of your tools and click Start:

How to create IF THEN formulas in Google Sheets

Once the add-on opens, it will invite you to start threading your IF statements for Google Sheets:

1. Pick a cell where you want to see the resulting formula.

   By default, the add-on picks up your currently selected cell. To change it, do one of the following:

   • Type a required cell reference manually.
   • Click in this Source range field to place your cursor there and then select any other cell in the sheet. The add-on will pick that another cell.
   • Click the Select range icon within the field to specify a cell for the formula from there:
     
     Here you can also either enter the reference manually or pick a cell on the sheet.
2. This Preview formula field is like a formula bar in Google Sheets. It shows the outcome formula based on all the info you enter to the formula.
   Tip. You can copy the formula from this Preview any time and paste it someplace else.
3. Now you are to start setting up your IF formula. The first part is the criterion itself – If. It is that logical expression that will be tested in your sheet.
4. When you enter your records, the add-on autodetects their types (formats). Each format is represented by a special icon to the right of the record.

   To change it, click the Data type button and pick the format from a drop-down list:
   

5. Pick the comparison operator that suits your condition in the best way:
   

   All operators are grouped according to the data type: Common, Value (numerics), Text, Date or Time, Boolean.

   Note. The operator called is (is equal to) is different for Numeric values, Text, and Date or Time.

   To invert all operators, e.g. switch is to is not or turn is empty to is not empty, toggle the corresponding Invert button at the top of the list.

6. If there's an excess part of the formula, you can remove it by clicking the Delete icon.
7. To add more logical expressions and their possible outcomes, click the Plus icon instead.
8. By adding more fields to your logical expression, you can specify several criteria to test against:
   
   • AND will check if all conditions are true at the same time.
   • OR – if at least one of them is met.
9. THEN is whatever you need to return when your logical expression is true.
   Tip. Turn THEN into THEN IF to continue adding more conditions and values to return for them:
   
10. The value for ELSE will be returned if your logical expression is false.
    Tip. To nest even more IF expressions, select ELSE IF. More fields will appear for you to enter more conditions and corresponding values for when they are met or not:
    

How to quickly edit existing IF formulas

The IF Formula Builder can break down the existing Google Sheets IF formulas to pieces:

• isolate each logical expression
• separate expected results
• distinguish between values and operators

All these can be easily edited and pasted back to the formula.

Just select the cell with an IF formula in it and run the tool. All parts will be shown as if you created this formula: