Create and edit Google Sheets IF formulas bit by bit

Use IF Formula Builder to create your own and edit existing IF formulas for Google Sheets without having to worry about the proper syntax and extra characters or functions.

Before you start

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.

How to use IF Formula Builder

Start the add-on

Go to the Add-ons menu in Google Sheets, find IF Formula Builder in the list of your tools and click Start:
Run the IF Formula Builder on Google Sheets.

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:
Build Google Sheets IF formulas using the add-on.

  1. Decide where to place 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:
      Select range pop-up.
      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:
    A list of data types (formats) available.

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

    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:
    Switch between AND-OR logic.

    • 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:
    Switch between THEN-THEN IF logic.
  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:
    Switch to ELSE IF to nest more IF expressions for Google Sheets.

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:
Decode existing Google Sheets IF formulas.

Note. 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.

Ask a question (posted publicly)

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.