Back to product guides

Google Sheets Formulas

In this article, you will learn how to utilize the SWITCH formula in Google Sheets. The SWITCH function is beneficial when you want to show a corresponding value to another value (e.g., converting numerical grade to alphabetical grade).

- Type
**“=SWITCH”**or navigate to the**“Insert”**tab (or**“Functions”**icon) →**“Function”**→**“Logical”**→**“SWITCH”.** - Input
**“expression”, “case”,**and**“value”**by manual input or cell reference. - Enter
**“case”**and**“value”**until you incorporate all corresponding values. - Finally, include
**“default”**if needed. - Press the
**“Enter”**key.

The general syntax is as follows:

**Expression**: This is a value tested by this formula. You should input this value by a cell reference, applying this function to many values.

**Case1**: If a tested value matched this value, **“case1”**,** **the formula returns a value defined as “value1”. If a tested value is not the same as this value, the function keeps searching other cases (if specified) for the tested value.

**Value1**: This is a value shown in the cell when a tested value equals **“case1”.**

**Case2: **Same as **“case1”**.

**Value2: **Same as **“value1”**. This value should be paired with **“case2”**.

**Default (optional)**: This is optional. If this default value is set, it is returned by the formula when the formula can’t find any match between a tested value and each case. Otherwise, the function returns “#N/A”.

Note: You need to input pairs of “case” and “value” until you include all pairs. The values mentioned above can be text, number, date, etc.

Assume you are a group manager and use a numerical grade to evaluate your group members (“Score”). However, you need to switch the grading system from the numerical one to an alphabetical one (“Grade”) due to the change in your company’s policy. You defined the following relationships between scores and grades: “1”=”E”, “2”=”D”, “3”=”C”, “4”=”B”, and “5”=”A, and organize a table showing their correspondences. See the following examples.

**Example 1 - manual Input version**

This example shows what the formula looks like if you use manual input.

The arguments in the SWITCH function in cell D4 look as follows:

**Expression: **C4

**Case1: **“1”

**Value1**: “E”

**Case2: **“2”

**Value2: **“D”

**Case3: **“3”...

**Default (optional)**: Not defined in this formula

The formula in cell D4 tests the value in cell C4, which is “5”, and returns “A” because the tested value matches “case5”, which is “5”, and thus “Value5”, which is “A” is shown in the cell. However, as Amelia and Lucas have scores that are not defined in the formula, the formulas return “#N/A”.

**Example 2 - cell reference version**

The following example shows the same results, but all formulas are input by cell references, which is highly recommended in terms of accuracy and convenience.

The arguments in the SWITCH function in cell D16 look as follows:

**Expression: **C16

**Case1: $**K$16

**Value1**: **$**L$16

**Case2: $**K$17

**Value2: $**L$17

**Case3: $**K$18…

**Default (optional)**: Not defined in this formula

In this example, the table describing relationships between alphabets and numbers is leveraged by being referred to in the formula.

**Example 3 - cell reference version with a default value**

The last example shows what happens if the default value is included in the SWITCH formula. You determine that assuming a score is equal to or more than 1, you give an “A+” grade to scores that are not tied to specific alphabets, such as “7” and “8”, Amelia’s and Lucas’ scores, respectively.

The grades for Amelia and Lucas, shown as “#N/A” for the last two examples, now appear as “A+” correctly. This is how the **“default”** argument works. The **“default”** argument should be included in the formula as the last input, without being paired with any value. Otherwise, the formula recognizes them as a pair of “case” and “value”.

Check this article to learn how to switch two columns in Google Sheets.

Unfortunately, you can‘t swap two cells in Google Sheets, though you can do it in Excel. Consider switching two columns instead.

Check this article to learn how to transpose rows and columns by Spacial Paste or the TRANSPOSE function in Google Sheets.

Are you learning this formula to build a financial KPI dashboard or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Learn how to do this step-by-step in the video below 👇

Our team of real people are here to help you any time between 9am and 10pm GMT.

Email us at: founders@liveflow.io

Call us at +1 (415) 650-1711